Create Function

[Jeff]

Is there a way, using asp scripts, to create a table in a DB, that creates
the table based on the name of the last table name?
What I want to do, is if the last table name is 5, automatically name the
new one being created 6. And go in asc order? I am writing a tourney site,
and want to create a new table for each tourney.
I am using access 2002 as the DB. And the table number is the actual tourney
ID. Hope this makes sense. Let me know if more info is needed.
The tourney info is entered into one tablecalled admin with all the info,
and I would like that autonumber ID to be the name of the new table being
created. Is this possible?
Jeff

[Aaron [SQL Server MVP]]

> Is there a way, using asp scripts, to create a table in a DB, that creates

> the table based on the name of the last table name?

How do you define "last table name"?

> What I want to do, is if the last table name is 5, automatically name the
> new one being created 6. And go in asc order? I am writing a tourney site,
> and want to create a new table for each tourney.

Your design sounds flawed. Why not have a single tourney table, with a
column for tourney_id, where you store values like 5, 6, etc.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Jeff]

Ok. Each tourney table will have about 20 columns. I am writing this to be
automated, so a host can create and administer a tourney without any help.
If I do what you are saying, the script would have to be altered each
tourney.

When I say last table name, I mean this. And I see i got my wording a bit
confused.
The table names will be the same as the ID in the admin table. So a host
creates a tourney, in the admin table, it is given an autonumber ID. So if
that host just created tourney 27 lets say, that would be the last record
entered into the admin table. I want to then create a table called 27 for
the actual tourney records to be entered into. I know how to create the
fields in the table, but I don't know how to create a table name based on a
field from another table.
I do hope this clears things a bit.
Jeff


"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OHy428NaEHA.3664@TK2MSFTNGP12.phx.gbl...

> > Is there a way, using asp scripts, to create a table in a DB, that

creates

> > the table based on the name of the last table name?

>
> How do you define "last table name"?
>

> > What I want to do, is if the last table name is 5, automatically name

the

> > new one being created 6. And go in asc order? I am writing a tourney

site,

> > and want to create a new table for each tourney.

>
> Your design sounds flawed. Why not have a single tourney table, with a
> column for tourney_id, where you store values like 5, 6, etc.
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>

[Aaron [SQL Server MVP]]

> Ok. Each tourney table will have about 20 columns. I am writing this to be

> automated, so a host can create and administer a tourney without any help.
> If I do what you are saying, the script would have to be altered each
> tourney.

I don't think you've really thought this through, using a single table will
be much more automated than creating a new table each time.

> I want to then create a table called 27 for
> the actual tourney records to be entered into.

I know that's what you WANT to do. But I'm telling it's not what you SHOULD
do.

> I know how to create the
> fields in the table, but I don't know how to create a table name based on

a

> field from another table.

Have a look at the CREATE TABLE statement (it's in Access online help).

If you decide you want to design your application right, let us know, I will
be more than willing to show you how to use a single table for this without
"altering the script for each tourney"...

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Jeff]

Ok. I am willing to listen. here is what I need as far as column names in
the table being created:
(username, email, password, Rd1, Rd2, Rd3, Rd4, Rd5, Rd6, Rd7, check) that
is the area that players will sign up at. The rd1, rd2 ect.. are where the
scores will go. How can I make this the way that you are suggesting?
Jeff

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OrMMBSOaEHA.556@tk2msftngp13.phx.gbl...

> > Ok. Each tourney table will have about 20 columns. I am writing this to

be

> > automated, so a host can create and administer a tourney without any

help.

> > If I do what you are saying, the script would have to be altered each
> > tourney.

>
> I don't think you've really thought this through, using a single table

will

> be much more automated than creating a new table each time.
>

> > I want to then create a table called 27 for
> > the actual tourney records to be entered into.

>
> I know that's what you WANT to do. But I'm telling it's not what you

SHOULD

> do.
>

> > I know how to create the
> > fields in the table, but I don't know how to create a table name based

on

> a

> > field from another table.

>
> Have a look at the CREATE TABLE statement (it's in Access online help).
>
> If you decide you want to design your application right, let us know, I

will

> be more than willing to show you how to use a single table for this

without

> "altering the script for each tourney"...
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>

[Aaron [SQL Server MVP]]

CREATE TABLE Admins
(
AdminID INT AUTOINCREMENT,
username VARCHAR(32),
password VARCHAR(32),
email VARCHAR(128)
)

CREATE TABLE Tournaments
(
TournamentID INT AUTOINCREMENT,
tDescription VARCHAR(32),
AdminID INT,
tDate DATETIME
)

CREATE TABLE TScores
(
TournamentID INT,
RoundNumber INT,
Score ???
)

Some points here:
(1) storing the users in a separate table allows you to store their "big"
information only once, and allows them to manage multiple tournaments with
the same username/password.
(2) added a description to the tournament, because an end user should not
rely on identifying an element by its internal, arbitrarily-assigned id.
(3) keeping the scores in a separate table allows you to have tournaments
with a different number of rounds and to order by round instead of column
order.
(4) I have no idea what the "check" column is for.
(5) password is not the best name for the column, since it is reserved (see
[url]http://www.aspfaq.com/2080[/url]).
(6) keys and index optimization are not done here. Just a barebones starter
kit.

Now, when a user logs in, you know their AdminID because you can match their
username and password:

sql = "SELECT AdminID FROM Admins WHERE Username = '" & _
username & "' AND [Password] = '" & password & "'"
set rs = conn.execute(sql)
if not rs.eof then
session("AdminID") = rs(0)
sql = "SELECT TournamentID, tDescription FROM Tournaments" & _
" WHERE AdminID = " & session("AdminID")
set rs = conn.execute(sql)
if not rs.eof then
response.write "<form method=post action=admin-edit.asp>"
response.write "Choose an existing tourney: <SELECT name=tourney>"
do while not rs.eof
response.write "<OPTION value=" & rs(0) & ">" & rs(1)
rs.movenext
loop
response.write "</SELECT><input type=submit></form>"
response.write "<p><form method=post action=admin-add.asp>"
response.write "Or add a new one: "
response.write "<input type=text name=tDescription>"
response.write "<input type=text name=tDate>" ' force YYYY-MM-DD
response.write "<input type=submit></form>"
end if
else
response.redirect("login.asp")
end if

Now, admin-edit.asp:

tID = Request.Form("tourney")
sql = "SELECT TournamentID, tDescription, tDate " & _
"FROM Tournaments WHERE AdminID = " & _
"Session("AdminID") & " AND TournamentID=" & tID
set rs = conn.execute(sql)
if not rs.eof then
response.write rs(0) & " - " & rs(1) & " - " & rs(2)
sql = "SELECT RoundNumber, Score FROM tScores " & _
"WHERE TournamentID = " & tID & " ORDER BY RoundNumber"
set rs = conn.execute(sql)
do while not rs.eof
' here you could write out the scores into textboxes, etc.
rs.movenext
loop
end if

Admin-add.asp:

tDescription = Request.Form("tDescription")
tDate = Request.Form("tDate")
sql = "INSERT INTO Tournaments(tDescription, AdminID, tDate) " & _
" VALUES('" & tDescription & "', " & Session("AdminID") & ", " & _
"'" & tDate & "'"
conn.execute(sql)

Bob can probably show you how to do this stuff with saved queries. Access
isn't really my bag and I don't want to lead you astray. There is also no
validation, etc, and I'll leave conn creation / connection strings up to
you. Again, just a barebones starter kit. It wasn't clear to me what
players / teams etc. could sign up, so I didn't include that portion. But
it would be easy to adapt...

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Jeff" <gig_bam_takemeout_@verizon.net> wrote in message
news:O$XcNZOaEHA.1644@tk2msftngp13.phx.gbl...

> Ok. I am willing to listen. here is what I need as far as column names in
> the table being created:
> (username, email, password, Rd1, Rd2, Rd3, Rd4, Rd5, Rd6, Rd7, check)

that

> is the area that players will sign up at. The rd1, rd2 ect.. are where

the

> scores will go. How can I make this the way that you are suggesting?
> Jeff

[Jeff]

And actually, I would still like to know if what I asked could be done. As
well as creating a DB the same way, using a variable from from a form. So
like when a host signs up for hosting, it will create them a DB and their
tables and admin stuff would be there. I know this might not be proper to do
as well, but I would like to know how to do it.
Jeff


"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OrMMBSOaEHA.556@tk2msftngp13.phx.gbl...

> > Ok. Each tourney table will have about 20 columns. I am writing this to

be

> > automated, so a host can create and administer a tourney without any

help.

> > If I do what you are saying, the script would have to be altered each
> > tourney.

>
> I don't think you've really thought this through, using a single table

will

> be much more automated than creating a new table each time.
>

> > I want to then create a table called 27 for
> > the actual tourney records to be entered into.

>
> I know that's what you WANT to do. But I'm telling it's not what you

SHOULD

> do.
>

> > I know how to create the
> > fields in the table, but I don't know how to create a table name based

on

> a

> > field from another table.

>
> Have a look at the CREATE TABLE statement (it's in Access online help).
>
> If you decide you want to design your application right, let us know, I

will

> be more than willing to show you how to use a single table for this

without

> "altering the script for each tourney"...
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>

[Jeff]

Understand that I am doing 2 different things here. First, there will be a
select number of Admins or hosts. Then there is the players for the tourney,
their info, and their scores.
I guess explaining when the final result will be might be best here.

someone signs up to be a host. they give their info, and i either grant or
deny them access. after doing this, if they are approved, they can create
and admin tourneys. once they create one, a link will be shown for that
tourney on the tourney main page and on the calendar. when a user clicks
that link, they are taken to that tourney page. which is actually the same
page, only hyperlink variables will be used so the info they will see is
based on their tourney. here they can sign up, withdrawal, whatever. when
the tourney starts.. the standings will also be able to be viewed here. The
"check" you are refering to, is a player check in, saying that they are
present and ready to play. that's all it does.
so basically, each tourney admin will have their own unique set of tourneys.
that is why i wanted each admin to have their own DB and tables.
This is sortof the way it is done on the myleague and cases ladder tourney
systems. I wanted to write this in asp instead of php, as I am not a fan of
that. i have everything else working the way i want it to run, with the
exception of the actual creating of stuff.
I hope this better explains what I am trying to do here.
Jeff

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23n9MMoOaEHA.3708@TK2MSFTNGP10.phx.gbl...

> CREATE TABLE Admins
> (
> AdminID INT AUTOINCREMENT,
> username VARCHAR(32),
> password VARCHAR(32),
> email VARCHAR(128)
> )
>
> CREATE TABLE Tournaments
> (
> TournamentID INT AUTOINCREMENT,
> tDescription VARCHAR(32),
> AdminID INT,
> tDate DATETIME
> )
>
> CREATE TABLE TScores
> (
> TournamentID INT,
> RoundNumber INT,
> Score ???
> )
>
> Some points here:
> (1) storing the users in a separate table allows you to store their "big"
> information only once, and allows them to manage multiple tournaments with
> the same username/password.
> (2) added a description to the tournament, because an end user should not
> rely on identifying an element by its internal, arbitrarily-assigned id.
> (3) keeping the scores in a separate table allows you to have tournaments
> with a different number of rounds and to order by round instead of column
> order.
> (4) I have no idea what the "check" column is for.
> (5) password is not the best name for the column, since it is reserved

(see

> [url]http://www.aspfaq.com/2080[/url]).
> (6) keys and index optimization are not done here. Just a barebones

starter

> kit.
>
> Now, when a user logs in, you know their AdminID because you can match

their

> username and password:
>
> sql = "SELECT AdminID FROM Admins WHERE Username = '" & _
> username & "' AND [Password] = '" & password & "'"
> set rs = conn.execute(sql)
> if not rs.eof then
> session("AdminID") = rs(0)
> sql = "SELECT TournamentID, tDescription FROM Tournaments" & _
> " WHERE AdminID = " & session("AdminID")
> set rs = conn.execute(sql)
> if not rs.eof then
> response.write "<form method=post action=admin-edit.asp>"
> response.write "Choose an existing tourney: <SELECT name=tourney>"
> do while not rs.eof
> response.write "<OPTION value=" & rs(0) & ">" & rs(1)
> rs.movenext
> loop
> response.write "</SELECT><input type=submit></form>"
> response.write "<p><form method=post action=admin-add.asp>"
> response.write "Or add a new one: "
> response.write "<input type=text name=tDescription>"
> response.write "<input type=text name=tDate>" ' force YYYY-MM-DD
> response.write "<input type=submit></form>"
> end if
> else
> response.redirect("login.asp")
> end if
>
> Now, admin-edit.asp:
>
> tID = Request.Form("tourney")
> sql = "SELECT TournamentID, tDescription, tDate " & _
> "FROM Tournaments WHERE AdminID = " & _
> "Session("AdminID") & " AND TournamentID=" & tID
> set rs = conn.execute(sql)
> if not rs.eof then
> response.write rs(0) & " - " & rs(1) & " - " & rs(2)
> sql = "SELECT RoundNumber, Score FROM tScores " & _
> "WHERE TournamentID = " & tID & " ORDER BY RoundNumber"
> set rs = conn.execute(sql)
> do while not rs.eof
> ' here you could write out the scores into textboxes, etc.
> rs.movenext
> loop
> end if
>
> Admin-add.asp:
>
> tDescription = Request.Form("tDescription")
> tDate = Request.Form("tDate")
> sql = "INSERT INTO Tournaments(tDescription, AdminID, tDate) " & _
> " VALUES('" & tDescription & "', " & Session("AdminID") & ", " & _
> "'" & tDate & "'"
> conn.execute(sql)
>
> Bob can probably show you how to do this stuff with saved queries. Access
> isn't really my bag and I don't want to lead you astray. There is also no
> validation, etc, and I'll leave conn creation / connection strings up to
> you. Again, just a barebones starter kit. It wasn't clear to me what
> players / teams etc. could sign up, so I didn't include that portion. But
> it would be easy to adapt...
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>
>
>
> "Jeff" <gig_bam_takemeout_@verizon.net> wrote in message
> news:O$XcNZOaEHA.1644@tk2msftngp13.phx.gbl...

> > Ok. I am willing to listen. here is what I need as far as column names

in

> > the table being created:
> > (username, email, password, Rd1, Rd2, Rd3, Rd4, Rd5, Rd6, Rd7, check)

> that

> > is the area that players will sign up at. The rd1, rd2 ect.. are where

> the

> > scores will go. How can I make this the way that you are suggesting?
> > Jeff

>
>

[Aaron [SQL Server MVP]]

> Understand that I am doing 2 different things here. First, there will be a

> select number of Admins or hosts. Then there is the players for the

tourney,

> their info, and their scores.

Like I said, I don't get paid enough to design your entire application for
you. I only wanted to show you the right approach. If that isn't enough,
sorry.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Aaron [SQL Server MVP]]

Yes, of course, you can design it the wrong way if you like (people do it
every day).

Here is how to create the same old table design with a new name, over and
over again, based on the last tourney created:


sql = "SELECT MAX(TourneyID) + 1 FROM Admin"
set rs = conn.execute(sql)
NextTourneyID = rs(0)
conn.execute "CREATE TABLE Tourney" & NextTourneyID(... columns ...)
conn.execute "INSERT INTO Admin(TourneyID) VALUES(" & NextTourneyID & ")"


Here is how to create a database for each new tourney:


sql = "SELECT MAX(TourneryID) + 1 FROM Admin"
set rs = conn.execute(sql)
NextTourneyID = rs(0)
newDB = "c:\inetpub\wwwroot\databases\Tourney" & NextTourneyID & ".mdb"
newDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & newDB
Set cat = CreateObject("ADOX.Catalog")
cat.Create newDB
Set conn = CreateObject("ADODB.Connection")
conn.Open newDB
conn.execute("CREATE TABLE Tourney ... ")


However, as I will say again and again, this is a horrible design and will
become impossible to manage in no time. But by all means, please proceed
with this route. Maybe you'll hire me to fix it when it goes completely
south.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Jeff" <gig_bam_takemeout_@verizon.net> wrote in message
news:#$evloOaEHA.1268@TK2MSFTNGP11.phx.gbl...

> And actually, I would still like to know if what I asked could be done. As
> well as creating a DB the same way, using a variable from from a form. So
> like when a host signs up for hosting, it will create them a DB and their
> tables and admin stuff would be there. I know this might not be proper to

do

> as well, but I would like to know how to do it.

[Jeff]

Please understand, your help is most appreciated. This site is in the baby
stages, and I wanted to develope it in a way that I have it in my head. I
know that things will change, and it will end up the simpliest way, which is
what you have already helped me with. I wanted to show myself that the idea
in my head works, before I make changes to it. It is a pride thing I guess
(or so i have been told).


"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:utJAr7OaEHA.3112@tk2msftngp13.phx.gbl...

> Yes, of course, you can design it the wrong way if you like (people do it
> every day).
>
> Here is how to create the same old table design with a new name, over and
> over again, based on the last tourney created:
>
>
> sql = "SELECT MAX(TourneyID) + 1 FROM Admin"
> set rs = conn.execute(sql)
> NextTourneyID = rs(0)
> conn.execute "CREATE TABLE Tourney" & NextTourneyID(... columns ...)
> conn.execute "INSERT INTO Admin(TourneyID) VALUES(" & NextTourneyID & ")"
>
>
> Here is how to create a database for each new tourney:
>
>
> sql = "SELECT MAX(TourneryID) + 1 FROM Admin"
> set rs = conn.execute(sql)
> NextTourneyID = rs(0)
> newDB = "c:\inetpub\wwwroot\databases\Tourney" & NextTourneyID & ".mdb"
> newDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & newDB
> Set cat = CreateObject("ADOX.Catalog")
> cat.Create newDB
> Set conn = CreateObject("ADODB.Connection")
> conn.Open newDB
> conn.execute("CREATE TABLE Tourney ... ")
>
>
> However, as I will say again and again, this is a horrible design and will
> become impossible to manage in no time. But by all means, please proceed
> with this route. Maybe you'll hire me to fix it when it goes completely
> south.
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>
>
>
> "Jeff" <gig_bam_takemeout_@verizon.net> wrote in message
> news:#$evloOaEHA.1268@TK2MSFTNGP11.phx.gbl...

> > And actually, I would still like to know if what I asked could be done.

As

> > well as creating a DB the same way, using a variable from from a form.

So

> > like when a host signs up for hosting, it will create them a DB and

their

> > tables and admin stuff would be there. I know this might not be proper

to

> do

> > as well, but I would like to know how to do it.

>
>

[Jeff Cochran]

On Tue, 13 Jul 2004 11:11:53 -0400, "Jeff"
<gig_bam_takemeout_@verizon.net> wrote:

>Understand that I am doing 2 different things here. First, there will be a
>select number of Admins or hosts. Then there is the players for the tourney,
>their info, and their scores.
>I guess explaining when the final result will be might be best here.
>
>someone signs up to be a host. they give their info, and i either grant or
>deny them access. after doing this, if they are approved, they can create
>and admin tourneys. once they create one, a link will be shown for that
>tourney on the tourney main page and on the calendar. when a user clicks
>that link, they are taken to that tourney page. which is actually the same
>page, only hyperlink variables will be used so the info they will see is
>based on their tourney. here they can sign up, withdrawal, whatever. when
>the tourney starts.. the standings will also be able to be viewed here. The
>"check" you are refering to, is a player check in, saying that they are
>present and ready to play. that's all it does.
>so basically, each tourney admin will have their own unique set of tourneys.
>that is why i wanted each admin to have their own DB and tables.
>This is sortof the way it is done on the myleague and cases ladder tourney
>systems. I wanted to write this in asp instead of php, as I am not a fan of
>that. i have everything else working the way i want it to run, with the
>exception of the actual creating of stuff.
>I hope this better explains what I am trying to do here.

Aarons database layout is a better option for a number of reasons, but
it's just a database layout, you'd need to code the application.
Which means you need to make a choice: Good database design but
recoding, or poor database design and use what you have. If you're
desperate to use what you have then your best option would be when you
create the tourney and number, you create the matching table, named
for that number. It's an inefficient and fault-prone database design,
but doing it right *will* mean doing some recoding.

Jeff

>Jeff
>
>"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
>news:%23n9MMoOaEHA.3708@TK2MSFTNGP10.phx.gbl...

>> CREATE TABLE Admins
>> (
>> AdminID INT AUTOINCREMENT,
>> username VARCHAR(32),
>> password VARCHAR(32),
>> email VARCHAR(128)
>> )
>>
>> CREATE TABLE Tournaments
>> (
>> TournamentID INT AUTOINCREMENT,
>> tDescription VARCHAR(32),
>> AdminID INT,
>> tDate DATETIME
>> )
>>
>> CREATE TABLE TScores
>> (
>> TournamentID INT,
>> RoundNumber INT,
>> Score ???
>> )
>>
>> Some points here:
>> (1) storing the users in a separate table allows you to store their "big"
>> information only once, and allows them to manage multiple tournaments with
>> the same username/password.
>> (2) added a description to the tournament, because an end user should not
>> rely on identifying an element by its internal, arbitrarily-assigned id.
>> (3) keeping the scores in a separate table allows you to have tournaments
>> with a different number of rounds and to order by round instead of column
>> order.
>> (4) I have no idea what the "check" column is for.
>> (5) password is not the best name for the column, since it is reserved

>(see

>> [url]http://www.aspfaq.com/2080[/url]).
>> (6) keys and index optimization are not done here. Just a barebones

>starter

>> kit.
>>
>> Now, when a user logs in, you know their AdminID because you can match

>their

>> username and password:
>>
>> sql = "SELECT AdminID FROM Admins WHERE Username = '" & _
>> username & "' AND [Password] = '" & password & "'"
>> set rs = conn.execute(sql)
>> if not rs.eof then
>> session("AdminID") = rs(0)
>> sql = "SELECT TournamentID, tDescription FROM Tournaments" & _
>> " WHERE AdminID = " & session("AdminID")
>> set rs = conn.execute(sql)
>> if not rs.eof then
>> response.write "<form method=post action=admin-edit.asp>"
>> response.write "Choose an existing tourney: <SELECT name=tourney>"
>> do while not rs.eof
>> response.write "<OPTION value=" & rs(0) & ">" & rs(1)
>> rs.movenext
>> loop
>> response.write "</SELECT><input type=submit></form>"
>> response.write "<p><form method=post action=admin-add.asp>"
>> response.write "Or add a new one: "
>> response.write "<input type=text name=tDescription>"
>> response.write "<input type=text name=tDate>" ' force YYYY-MM-DD
>> response.write "<input type=submit></form>"
>> end if
>> else
>> response.redirect("login.asp")
>> end if
>>
>> Now, admin-edit.asp:
>>
>> tID = Request.Form("tourney")
>> sql = "SELECT TournamentID, tDescription, tDate " & _
>> "FROM Tournaments WHERE AdminID = " & _
>> "Session("AdminID") & " AND TournamentID=" & tID
>> set rs = conn.execute(sql)
>> if not rs.eof then
>> response.write rs(0) & " - " & rs(1) & " - " & rs(2)
>> sql = "SELECT RoundNumber, Score FROM tScores " & _
>> "WHERE TournamentID = " & tID & " ORDER BY RoundNumber"
>> set rs = conn.execute(sql)
>> do while not rs.eof
>> ' here you could write out the scores into textboxes, etc.
>> rs.movenext
>> loop
>> end if
>>
>> Admin-add.asp:
>>
>> tDescription = Request.Form("tDescription")
>> tDate = Request.Form("tDate")
>> sql = "INSERT INTO Tournaments(tDescription, AdminID, tDate) " & _
>> " VALUES('" & tDescription & "', " & Session("AdminID") & ", " & _
>> "'" & tDate & "'"
>> conn.execute(sql)
>>
>> Bob can probably show you how to do this stuff with saved queries. Access
>> isn't really my bag and I don't want to lead you astray. There is also no
>> validation, etc, and I'll leave conn creation / connection strings up to
>> you. Again, just a barebones starter kit. It wasn't clear to me what
>> players / teams etc. could sign up, so I didn't include that portion. But
>> it would be easy to adapt...
>>
>> --
>> [url]http://www.aspfaq.com/[/url]
>> (Reverse address to reply.)
>>
>>
>>
>>
>> "Jeff" <gig_bam_takemeout_@verizon.net> wrote in message
>> news:O$XcNZOaEHA.1644@tk2msftngp13.phx.gbl...

>> > Ok. I am willing to listen. here is what I need as far as column names

>in

>> > the table being created:
>> > (username, email, password, Rd1, Rd2, Rd3, Rd4, Rd5, Rd6, Rd7, check)

>> that

>> > is the area that players will sign up at. The rd1, rd2 ect.. are where

>> the

>> > scores will go. How can I make this the way that you are suggesting?
>> > Jeff

>>
>>

>

[Jeff Cochran]

On Tue, 13 Jul 2004 10:11:19 -0400, "Jeff"
<gig_bam_takemeout_@verizon.net> wrote:

>Ok. Each tourney table will have about 20 columns. I am writing this to be
>automated, so a host can create and administer a tourney without any help.
>If I do what you are saying, the script would have to be altered each
>tourney.
>
>When I say last table name, I mean this. And I see i got my wording a bit
>confused.
>The table names will be the same as the ID in the admin table. So a host
>creates a tourney, in the admin table, it is given an autonumber ID. So if
>that host just created tourney 27 lets say, that would be the last record
>entered into the admin table. I want to then create a table called 27 for
>the actual tourney records to be entered into. I know how to create the
>fields in the table, but I don't know how to create a table name based on a
>field from another table.

Query the Admin table for the tourney ID, the use the CREATE TABLE
command to create the table based on that ID for a name. You could
select the highest number ID, though that may be inaccurate, or select
based on the parameters an admin has to enter for a tourney (Name,
date, etc.).

Jeff

>I do hope this clears things a bit.
>Jeff
>
>
>"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
>news:OHy428NaEHA.3664@TK2MSFTNGP12.phx.gbl...

>> > Is there a way, using asp scripts, to create a table in a DB, that

>creates

>> > the table based on the name of the last table name?

>>
>> How do you define "last table name"?
>>

>> > What I want to do, is if the last table name is 5, automatically name

>the

>> > new one being created 6. And go in asc order? I am writing a tourney

>site,

>> > and want to create a new table for each tourney.

>>
>> Your design sounds flawed. Why not have a single tourney table, with a
>> column for tourney_id, where you store values like 5, 6, etc.
>>
>> --
>> [url]http://www.aspfaq.com/[/url]
>> (Reverse address to reply.)
>>
>>

>

[Jeff Cochran]

On Tue, 13 Jul 2004 11:41:51 -0400, "Jeff"
<gig_bam_takemeout_@verizon.net> wrote:

>Please understand, your help is most appreciated. This site is in the baby
>stages, and I wanted to develope it in a way that I have it in my head. I
>know that things will change, and it will end up the simpliest way, which is
>what you have already helped me with. I wanted to show myself that the idea
>in my head works, before I make changes to it. It is a pride thing I guess
>(or so i have been told).

Usually is, and we all suffer from it. The tough part is when we let
the pride hang on too long and we're in over our heads. Then it take
forever to get back on dry land.

Take the time, as soon as you can, to design the database structure
correctly, then do the coding.

Jeff

>"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
>news:utJAr7OaEHA.3112@tk2msftngp13.phx.gbl...

>> Yes, of course, you can design it the wrong way if you like (people do it
>> every day).
>>
>> Here is how to create the same old table design with a new name, over and
>> over again, based on the last tourney created:
>>
>>
>> sql = "SELECT MAX(TourneyID) + 1 FROM Admin"
>> set rs = conn.execute(sql)
>> NextTourneyID = rs(0)
>> conn.execute "CREATE TABLE Tourney" & NextTourneyID(... columns ...)
>> conn.execute "INSERT INTO Admin(TourneyID) VALUES(" & NextTourneyID & ")"
>>
>>
>> Here is how to create a database for each new tourney:
>>
>>
>> sql = "SELECT MAX(TourneryID) + 1 FROM Admin"
>> set rs = conn.execute(sql)
>> NextTourneyID = rs(0)
>> newDB = "c:\inetpub\wwwroot\databases\Tourney" & NextTourneyID & ".mdb"
>> newDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & newDB
>> Set cat = CreateObject("ADOX.Catalog")
>> cat.Create newDB
>> Set conn = CreateObject("ADODB.Connection")
>> conn.Open newDB
>> conn.execute("CREATE TABLE Tourney ... ")
>>
>>
>> However, as I will say again and again, this is a horrible design and will
>> become impossible to manage in no time. But by all means, please proceed
>> with this route. Maybe you'll hire me to fix it when it goes completely
>> south.
>>
>> --
>> [url]http://www.aspfaq.com/[/url]
>> (Reverse address to reply.)
>>
>>
>>
>>
>> "Jeff" <gig_bam_takemeout_@verizon.net> wrote in message
>> news:#$evloOaEHA.1268@TK2MSFTNGP11.phx.gbl...

>> > And actually, I would still like to know if what I asked could be done.

>As

>> > well as creating a DB the same way, using a variable from from a form.

>So

>> > like when a host signs up for hosting, it will create them a DB and

>their

>> > tables and admin stuff would be there. I know this might not be proper

>to

>> do

>> > as well, but I would like to know how to do it.

>>
>>

>

[Jeff]

The info i just gave wasn't for the admin table, but tather for the main
player signup table. This is where the names of those whi sign up go to. The
admin sign up simply has username, password, and email for that table.
I do see why this would be easier to do.
Only other thing is, in the scripts that I wrote to actually set up the
brackets, it takes the usernames from the players table, and makes them in
ramdom order, then places them in another table. this other table is where
the info goes for the who won and who lost. and the brackets are based on
this.




"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23n9MMoOaEHA.3708@TK2MSFTNGP10.phx.gbl...

> CREATE TABLE Admins
> (
> AdminID INT AUTOINCREMENT,
> username VARCHAR(32),
> password VARCHAR(32),
> email VARCHAR(128)
> )
>
> CREATE TABLE Tournaments
> (
> TournamentID INT AUTOINCREMENT,
> tDescription VARCHAR(32),
> AdminID INT,
> tDate DATETIME
> )
>
> CREATE TABLE TScores
> (
> TournamentID INT,
> RoundNumber INT,
> Score ???
> )
>
> Some points here:
> (1) storing the users in a separate table allows you to store their "big"
> information only once, and allows them to manage multiple tournaments with
> the same username/password.
> (2) added a description to the tournament, because an end user should not
> rely on identifying an element by its internal, arbitrarily-assigned id.
> (3) keeping the scores in a separate table allows you to have tournaments
> with a different number of rounds and to order by round instead of column
> order.
> (4) I have no idea what the "check" column is for.
> (5) password is not the best name for the column, since it is reserved

(see

> [url]http://www.aspfaq.com/2080[/url]).
> (6) keys and index optimization are not done here. Just a barebones

starter

> kit.
>
> Now, when a user logs in, you know their AdminID because you can match

their

> username and password:
>
> sql = "SELECT AdminID FROM Admins WHERE Username = '" & _
> username & "' AND [Password] = '" & password & "'"
> set rs = conn.execute(sql)
> if not rs.eof then
> session("AdminID") = rs(0)
> sql = "SELECT TournamentID, tDescription FROM Tournaments" & _
> " WHERE AdminID = " & session("AdminID")
> set rs = conn.execute(sql)
> if not rs.eof then
> response.write "<form method=post action=admin-edit.asp>"
> response.write "Choose an existing tourney: <SELECT name=tourney>"
> do while not rs.eof
> response.write "<OPTION value=" & rs(0) & ">" & rs(1)
> rs.movenext
> loop
> response.write "</SELECT><input type=submit></form>"
> response.write "<p><form method=post action=admin-add.asp>"
> response.write "Or add a new one: "
> response.write "<input type=text name=tDescription>"
> response.write "<input type=text name=tDate>" ' force YYYY-MM-DD
> response.write "<input type=submit></form>"
> end if
> else
> response.redirect("login.asp")
> end if
>
> Now, admin-edit.asp:
>
> tID = Request.Form("tourney")
> sql = "SELECT TournamentID, tDescription, tDate " & _
> "FROM Tournaments WHERE AdminID = " & _
> "Session("AdminID") & " AND TournamentID=" & tID
> set rs = conn.execute(sql)
> if not rs.eof then
> response.write rs(0) & " - " & rs(1) & " - " & rs(2)
> sql = "SELECT RoundNumber, Score FROM tScores " & _
> "WHERE TournamentID = " & tID & " ORDER BY RoundNumber"
> set rs = conn.execute(sql)
> do while not rs.eof
> ' here you could write out the scores into textboxes, etc.
> rs.movenext
> loop
> end if
>
> Admin-add.asp:
>
> tDescription = Request.Form("tDescription")
> tDate = Request.Form("tDate")
> sql = "INSERT INTO Tournaments(tDescription, AdminID, tDate) " & _
> " VALUES('" & tDescription & "', " & Session("AdminID") & ", " & _
> "'" & tDate & "'"
> conn.execute(sql)
>
> Bob can probably show you how to do this stuff with saved queries. Access
> isn't really my bag and I don't want to lead you astray. There is also no
> validation, etc, and I'll leave conn creation / connection strings up to
> you. Again, just a barebones starter kit. It wasn't clear to me what
> players / teams etc. could sign up, so I didn't include that portion. But
> it would be easy to adapt...
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>
>
>
> "Jeff" <gig_bam_takemeout_@verizon.net> wrote in message
> news:O$XcNZOaEHA.1644@tk2msftngp13.phx.gbl...

> > Ok. I am willing to listen. here is what I need as far as column names

in

> > the table being created:
> > (username, email, password, Rd1, Rd2, Rd3, Rd4, Rd5, Rd6, Rd7, check)

> that

> > is the area that players will sign up at. The rd1, rd2 ect.. are where

> the

> > scores will go. How can I make this the way that you are suggesting?
> > Jeff

>
>