Sports DB
am 16.10.2004 00:48:13 von John Smith
am new to dynamic web design and am having some trouble designing a db/web
form for a hockey team. I am hoping to have a form that will update the
records for each player on the team.
I have created a table in my DB with the following column headers:
playerid (primary, autonumber)
playername
goals
assists
points
pims
shutouts
whenever I try to update the table from the form I created in dreamweaver,
it overwrites the other entries. Can anyone point me in the right direction?
I have been searching around for most of the day and cant find the answer.
One other thing I was hoping to find out. Is it possible to have the form
update the record so that the numbers are cumulative? For example Tom Smith
has 4 goals in his next game he scores 2 more, I enter 2 into the form and
the database changes his goals record to 6. Is this a possibility?
Re: Sports DB
am 16.10.2004 04:05:31 von McKirahan
"Whodat" wrote in message
news:qsYbd.3967$q02.635984@read2.cgocable.net...
> am new to dynamic web design and am having some trouble designing a db/web
> form for a hockey team. I am hoping to have a form that will update the
> records for each player on the team.
>
> I have created a table in my DB with the following column headers:
>
> playerid (primary, autonumber)
> playername
> goals
> assists
> points
> pims
> shutouts
>
> whenever I try to update the table from the form I created in dreamweaver,
> it overwrites the other entries. Can anyone point me in the right
direction?
> I have been searching around for most of the day and cant find the answer.
>
> One other thing I was hoping to find out. Is it possible to have the form
> update the record so that the numbers are cumulative? For example Tom
Smith
> has 4 goals in his next game he scores 2 more, I enter 2 into the form and
> the database changes his goals record to 6. Is this a possibility?
>
"whenever I try to update the table from the form I created in dreamweaver,
it overwrites the other entries." is like saying "Doctor, something hurts,
can you cure me?".
We can't fix what we can't see -- show us your code!
Re: Sports DB
am 16.10.2004 04:53:09 von John Smith
Hi sorry for the lack of info, not sure if you would want to see all of
this - didnt want to spam.
Since I first posted I have changed the way my DB is setup. It is now like
this:
Table BDIV_teamstats contains:
Gameday
Opponent
Goalsfor
Goalsagainst
Win
Loss
Tie
Shutout
GameID (primary)
Table BPlayers Contains:
PlayerID (primary)
Playername
Playernumber
Table BPlayerstats contains:
StaID (primary)
staPlayerID (related to Bplayer; PlayerID)
StaGameid (related to BDIV_teamstats; Gameid
Stagoals
Staassists
Stapoints
StaPims
I have created a relationship between these tables as I indicated above.
When there is no data in the pages, everythign is fine. As soon as I enter
data I get an error:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)"
Is this because of Primary keys possibly being the same number? I have been
told about Foreign Keys? - would doing that help? I really dont know how to
create them.
Using windows Xp Sp2 and access 2003, DWMX4. I am not sure where I need to
change my approach. I am not sure if the error is in my DB design or the
form design. Anyway I appreciate any help I can get.
This is the input form code:
THanks for reading
"McKirahan" wrote in message
news:Ll%bd.398126$Fg5.66456@attbi_s53...
> "Whodat" wrote in message
> news:qsYbd.3967$q02.635984@read2.cgocable.net...
>> am new to dynamic web design and am having some trouble designing a
>> db/web
>> form for a hockey team. I am hoping to have a form that will update the
>> records for each player on the team.
>>
>> I have created a table in my DB with the following column headers:
>>
>> playerid (primary, autonumber)
>> playername
>> goals
>> assists
>> points
>> pims
>> shutouts
>>
>> whenever I try to update the table from the form I created in
>> dreamweaver,
>> it overwrites the other entries. Can anyone point me in the right
> direction?
>> I have been searching around for most of the day and cant find the
>> answer.
>>
>> One other thing I was hoping to find out. Is it possible to have the form
>> update the record so that the numbers are cumulative? For example Tom
> Smith
>> has 4 goals in his next game he scores 2 more, I enter 2 into the form
>> and
>> the database changes his goals record to 6. Is this a possibility?
>>
>
> "whenever I try to update the table from the form I created in
> dreamweaver,
> it overwrites the other entries." is like saying "Doctor, something hurts,
> can you cure me?".
>
> We can't fix what we can't see -- show us your code!
>
>
Re: Sports DB
am 16.10.2004 06:05:05 von McKirahan
"Whodat" wrote in message
news:520cd.3982$q02.642377@read2.cgocable.net...
> Hi sorry for the lack of info, not sure if you would want to see all of
> this - didnt want to spam.
>
> Since I first posted I have changed the way my DB is setup. It is now like
> this:
>
> Table BDIV_teamstats contains:
>
> Gameday
> Opponent
> Goalsfor
> Goalsagainst
> Win
> Loss
> Tie
> Shutout
> GameID (primary)
>
> Table BPlayers Contains:
>
> PlayerID (primary)
> Playername
> Playernumber
>
> Table BPlayerstats contains:
> StaID (primary)
> staPlayerID (related to Bplayer; PlayerID)
> StaGameid (related to BDIV_teamstats; Gameid
> Stagoals
> Staassists
> Stapoints
> StaPims
>
> I have created a relationship between these tables as I indicated above.
> When there is no data in the pages, everythign is fine. As soon as I enter
> data I get an error:
>
> "This expression is typed incorrectly, or it is too complex to be
evaluated.
> For example, a numeric expression may contain too many complicated
elements.
> Try simplifying the expression by assigning parts of the expression to
> variables. (Error 3071)"
>
> Is this because of Primary keys possibly being the same number? I have
been
> told about Foreign Keys? - would doing that help? I really dont know how
to
> create them.
>
> Using windows Xp Sp2 and access 2003, DWMX4. I am not sure where I need to
> change my approach. I am not sure if the error is in my DB design or the
> form design. Anyway I appreciate any help I can get.
>
> This is the input form code:
>
>
>
> THanks for reading
[snip]
1) Please do not top post.
2) Where's your SQL statement?
3) Here's a single ASP page that does what you originally asked.
Basically, it display all Players and allows for each to be updated or a new
Player to be added. However, it is up to you to compute and enter the new t
otal number of "goals" etc. One thing missing is client-side data
validation in the JavaScript function.
Perhaps this will give you some ideas; let me know. Watch for word.wrap.
<% @Language="VBScript" %>
<% Option Explicit
'----------------------------------------------------------- ----------------
'
' "players.asp"
'
' This ASP (Active Server Pages) program does the following:
' 1) Displays the "players" table and allows add and update.
'
' playerid (primary, autonumber)
' playername
' goals
' assists
' points
' pims
' shutouts
'
' Test URL: http://localhost/players.asp
'
' Changes:
------------------------------------------------------------ -------------
' 15-Oct-2004.0 Created.
'
'----------------------------------------------------------- ----------------
'
'*
'* Declare Constants
'*
Const cASP = "players.asp"
Const cMDB = "players.mdb"
Const cDSN = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
'*
Const adCmdTable = &H0002
Const adLockOptimistic = 3
Const adOpenKeySet = 1
'*
'* Declare Variables
'*
Dim strPID
strPID = Request.Form("playerid")
Dim strSQL
Dim arrSTR()
ReDim arrSTR(100)
Dim intSTR
intSTR = 0
Dim strSTR
'*
'* Declare Objects
'*
Dim objADO
Set objADO = Server.CreateObject("ADODB.Connection")
objADO.Open cDSN & Server.MapPath(cMDB)
Dim objRST
Set objRST = Server.CreateObject("ADODB.Recordset")
'*
'* SQL based on "playerid"
'*
If strPID = "0" Then
'*
'* ADDNEW
'*
objRST.Open "players", objADO, adOpenKeySet, adLockOptimistic,
adCmdTable
objRST.AddNew
objRST("playername") = Request.Form("playername")
objRST("goals") = CInt(Request.Form("goals"))
objRST("assists") = CInt(Request.Form("assists"))
objRST("points") = CInt(Request.Form("points"))
objRST("pims") = CInt(Request.Form("pims"))
objRST("shutouts") = CInt(Request.Form("shutouts"))
objRST.Update
objRST.Close
ElseIf strPID <> "" Then
'*
'* UPDATE
'*
strSQL = "UPDATE players SET"
strSQL = strSQL & " playername = '" & Request.Form("playername") &
"'"
strSQL = strSQL & ", goals = " & CInt(Request.Form("goals"))
strSQL = strSQL & ", assists = " & CInt(Request.Form("assists"))
strSQL = strSQL & ", points = " & CInt(Request.Form("points"))
strSQL = strSQL & ", pims = " & CInt(Request.Form("pims"))
strSQL = strSQL & ", shutouts = " & CInt(Request.Form("shutouts"))
strSQL = strSQL & " WHERE playerid = " &
CInt(Request.Form("playerid"))
objADO.Execute(strSQL)
End If
'*
'* SELECT
'*
strSQL = "SELECT * FROM players"
strSQL = strSQL & " ORDER BY playername"
objRST.Open strSQL, objADO
Append ""
'*
'* Destroy Objects
'*
Set objRST = Nothing
objADO.Close
Set objADO = Nothing
Sub Append(strSTR)
'** ------------------------------------------------------------ ------------
---'
'* Append()
'*
'* Appends strings to array entries redimensioning as needed; (see
"Concat()").
'** ------------------------------------------------------------ ------------
---'
strSTR = strSTR & ""
If intSTR > UBound(arrSTR) Then
ReDim Preserve arrSTR(UBound(arrSTR) + 100)
End If
arrSTR(intSTR) = strSTR & vbCrLf
intSTR = intSTR + 1
End Sub
Function Concat()
'** ------------------------------------------------------------ ------------
---'
'* Concat()
'*
'* Concatenates array entries into a single string; (see "Append()").
'** ------------------------------------------------------------ ------------
---'
Redim Preserve arrSTR(intSTR)
Concat = Replace(Join(arrSTR, ""),"`",Chr(34))
Erase arrSTR
ReDim arrSTR(100)
intSTR = 0
End Function
%>
<%=cASP%>
Re: Sports DB
am 16.10.2004 06:53:38 von John Smith
"McKirahan" wrote in message
news:R51cd.247250$MQ5.65084@attbi_s52...
> "Whodat" wrote in message
> news:520cd.3982$q02.642377@read2.cgocable.net...
>> Hi sorry for the lack of info, not sure if you would want to see all of
>> this - didnt want to spam.
>>
>> Since I first posted I have changed the way my DB is setup. It is now
>> like
>> this:
>>
>> Table BDIV_teamstats contains:
>>
>> Gameday
>> Opponent
>> Goalsfor
>> Goalsagainst
>> Win
>> Loss
>> Tie
>> Shutout
>> GameID (primary)
>>
>> Table BPlayers Contains:
>>
>> PlayerID (primary)
>> Playername
>> Playernumber
>>
>> Table BPlayerstats contains:
>> StaID (primary)
>> staPlayerID (related to Bplayer; PlayerID)
>> StaGameid (related to BDIV_teamstats; Gameid
>> Stagoals
>> Staassists
>> Stapoints
>> StaPims
>>
>> I have created a relationship between these tables as I indicated above.
>> When there is no data in the pages, everythign is fine. As soon as I
>> enter
>> data I get an error:
>>
>> "This expression is typed incorrectly, or it is too complex to be
> evaluated.
>> For example, a numeric expression may contain too many complicated
> elements.
>> Try simplifying the expression by assigning parts of the expression to
>> variables. (Error 3071)"
>>
>> Is this because of Primary keys possibly being the same number? I have
> been
>> told about Foreign Keys? - would doing that help? I really dont know how
> to
>> create them.
>>
>> Using windows Xp Sp2 and access 2003, DWMX4. I am not sure where I need
>> to
>> change my approach. I am not sure if the error is in my DB design or the
>> form design. Anyway I appreciate any help I can get.
>>
>> This is the input form code:
>>
>>
>>
>> THanks for reading
>
> [snip]
>
> 1) Please do not top post.
>
> 2) Where's your SQL statement?
>
> 3) Here's a single ASP page that does what you originally asked.
> Basically, it display all Players and allows for each to be updated or a
> new
> Player to be added. However, it is up to you to compute and enter the new
> t
> otal number of "goals" etc. One thing missing is client-side data
> validation in the JavaScript function.
>
> Perhaps this will give you some ideas; let me know. Watch for word.wrap.
>
>
> <% @Language="VBScript" %>
> <% Option Explicit
>
> '----------------------------------------------------------- ----------------
> '
> ' "players.asp"
> '
> ' This ASP (Active Server Pages) program does the following:
> ' 1) Displays the "players" table and allows add and update.
> '
> ' playerid (primary, autonumber)
> ' playername
> ' goals
> ' assists
> ' points
> ' pims
> ' shutouts
> '
> ' Test URL: http://localhost/players.asp
> '
> ' Changes:
>
>
> ------------------------------------------------------------ -------------
> ' 15-Oct-2004.0 Created.
> '
>
> '----------------------------------------------------------- ----------------
> '
> '*
> '* Declare Constants
> '*
> Const cASP = "players.asp"
> Const cMDB = "players.mdb"
> Const cDSN = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
> '*
> Const adCmdTable = &H0002
> Const adLockOptimistic = 3
> Const adOpenKeySet = 1
> '*
> '* Declare Variables
> '*
> Dim strPID
> strPID = Request.Form("playerid")
> Dim strSQL
> Dim arrSTR()
> ReDim arrSTR(100)
> Dim intSTR
> intSTR = 0
> Dim strSTR
> '*
> '* Declare Objects
> '*
> Dim objADO
> Set objADO = Server.CreateObject("ADODB.Connection")
> objADO.Open cDSN & Server.MapPath(cMDB)
> Dim objRST
> Set objRST = Server.CreateObject("ADODB.Recordset")
> '*
> '* SQL based on "playerid"
> '*
> If strPID = "0" Then
> '*
> '* ADDNEW
> '*
> objRST.Open "players", objADO, adOpenKeySet, adLockOptimistic,
> adCmdTable
> objRST.AddNew
> objRST("playername") = Request.Form("playername")
> objRST("goals") = CInt(Request.Form("goals"))
> objRST("assists") = CInt(Request.Form("assists"))
> objRST("points") = CInt(Request.Form("points"))
> objRST("pims") = CInt(Request.Form("pims"))
> objRST("shutouts") = CInt(Request.Form("shutouts"))
> objRST.Update
> objRST.Close
> ElseIf strPID <> "" Then
> '*
> '* UPDATE
> '*
> strSQL = "UPDATE players SET"
> strSQL = strSQL & " playername = '" & Request.Form("playername") &
> "'"
> strSQL = strSQL & ", goals = " & CInt(Request.Form("goals"))
> strSQL = strSQL & ", assists = " & CInt(Request.Form("assists"))
> strSQL = strSQL & ", points = " & CInt(Request.Form("points"))
> strSQL = strSQL & ", pims = " & CInt(Request.Form("pims"))
> strSQL = strSQL & ", shutouts = " & CInt(Request.Form("shutouts"))
> strSQL = strSQL & " WHERE playerid = " &
> CInt(Request.Form("playerid"))
> objADO.Execute(strSQL)
> End If
> '*
> '* SELECT
> '*
> strSQL = "SELECT * FROM players"
> strSQL = strSQL & " ORDER BY playername"
> objRST.Open strSQL, objADO
> Append ""
> '*
> '* Destroy Objects
> '*
> Set objRST = Nothing
> objADO.Close
> Set objADO = Nothing
>
> Sub Append(strSTR)
> '** ------------------------------------------------------------ ------------
> ---'
> '* Append()
> '*
> '* Appends strings to array entries redimensioning as needed; (see
> "Concat()").
> '** ------------------------------------------------------------ ------------
> ---'
> strSTR = strSTR & ""
> If intSTR > UBound(arrSTR) Then
> ReDim Preserve arrSTR(UBound(arrSTR) + 100)
> End If
> arrSTR(intSTR) = strSTR & vbCrLf
> intSTR = intSTR + 1
> End Sub
>
> Function Concat()
> '** ------------------------------------------------------------ ------------
> ---'
> '* Concat()
> '*
> '* Concatenates array entries into a single string; (see "Append()").
> '** ------------------------------------------------------------ ------------
> ---'
> Redim Preserve arrSTR(intSTR)
> Concat = Replace(Join(arrSTR, ""),"`",Chr(34))
> Erase arrSTR
> ReDim arrSTR(100)
> intSTR = 0
> End Function
> %>
>
>
> <%=cASP%>
>
>
>
>
>
>
>
>
>
Thanks very much for that. I have tried it out and it should give me a good
base to go on thank you very much.
I am away for 2 weeks starting on sunday so I will probably not be playing
with it untill I return.
Thanks again.