Hi everbody. I have an asp script that supposed to edit a player table records. When ever i try to edit a record value i get this strange error. I be happy if some one help me fix this error and be able to update the players records.Thanks
data type for players table:
playerno ==> number
name==>text
initials==>text
bird_date==>date/time
************==>text
joined==>number
street==>text
houseno==>text
postcode==>text
town==>text
phoneno==>text
leagueno==>text
---------------------------------------------------------------------
Technical Information (for support personnel)
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''8, POSTCODE = '6392LK'.
/asp/asp/tenissdb_edit.asp, line 236
and the line 236 points to this line :
cnnDBEdit.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords
------------------------------------------------------------------
[cod]
edit code:
Case "edit"
' First of a 2 part process... build a form with the
' values from the db.
iRecordId = Request.QueryString("playerno")
If IsNumeric(iRecordId) Then
iRecordId = CLng(iRecordId)
Else
iRecordId = 0
End If
strSQL = "SELECT * FROM players WHERE playerno=" & iRecordId & ";"
Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
rstDBEdit.Open strSQL, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText
If Not rstDBEdit.EOF Then
%>
<p>
Note: Watch your input... the text field is small and no error
handling is done to check for valid integers or dates. If
an error gets thrown when you submit simply hit back and fix
the offending entry before resubmitting.
</p>
<form action="<%= SCRIPT_NAME %>?action=editsave" method="post">
<input type="hidden" name="playerno" value="<%= rstDBEdit.Fields("playerno").Value %>"/><br />
<input type="text" name="NAME" value="<%= Server.HTMLEncode(rstDBEdit.Fields("NAME").Value) %>" /><br />
<input type="text" name="INITIALS" value="<%= Server.HTMLEncode(rstDBEdit.Fields("INITIALS").Val ue) %>" /><br />
<input type="text" name="BIRTH_DATE" value="<%= Server.HTMLEncode(rstDBEdit.Fields("BIRTH_DATE").V alue) %>" /><br />
<input type="text" name="************" value="<%= Server.HTMLEncode(rstDBEdit.Fields("************").Value) %>" /><br />
<input type="text" name="JOINED" value="<%= Server.HTMLEncode(rstDBEdit.Fields("JOINED").Value ) %>" /><br />
<input type="text" name="STREET" value="<%= Server.HTMLEncode(rstDBEdit.Fields("STREET").Value ) %>" /><br />
<input type="text" name="HOUSENO" value="<%= Server.HTMLEncode(rstDBEdit.Fields("HOUSENO").Valu e) %>" /><br />
<input type="text" name="POSTCODE" value="<%= Server.HTMLEncode(rstDBEdit.Fields("POSTCODE").Val ue) %>" /><br />
<input type="text" name="TOWN" value="<%= Server.HTMLEncode(rstDBEdit.Fields("TOWN").Value) %>" /><br />
<input type="text" name="PHONENO" value="<%= Server.HTMLEncode(rstDBEdit.Fields("PHONENO").Valu e) %>" /><br />
<input type="text" name="LEAGUENO" value="<%= Server.HTMLEncode(rstDBEdit.Fields("LEAGUENO").Val ue) %>" /><br />
<input type="submit" name="Update Database">
</form>
<%
Else
Response.Write "Record not found!"
End If
rstDBEdit.Close
Set rstDBEdit = Nothing
Response.Write(BACK_TO_LIST_TEXT)
-------------------------------------------------------------
[/code]
[cod]
editsave code:
Case "editsave"
' Part 2 of 2: Here's where we save the values that the
' user entered back to the DB. Again... no error
' handling or input checking so ' characters and invalid
' values will throw error messages.
iRecordId = Request.Form("PLAYERNO")
iRecordId = Replace(iRecordId, "'", "''")
' Date delimiter on this should be changed to # for Access
strSQL = "UPDATE PLAYERS SET " _
& "NAME = '" & CStr(Replace(Request.Form("NAME"), "'", "''")) & "', " _
& "INITIALS = " & CStr(Replace(Request.Form("INITIALS"), "'", "''")) & ", " _
& "BIRTH_DATE = '" & CStr(Replace(Request.Form("BIRTH_DATE"), "'", "''")) & "', " _
& "************ = '" & CStr(Replace(Request.Form("************"), "'", "''")) & "', " _
& "JOINED = '" & CStr(Replace(Request.Form("JOINED"), "'", "''")) & "', " _
& "STREET = '" & CStr(Replace(Request.Form("STREET"), "'", "''")) & "', " _
& "HOUSENO = '" & CStr(Replace(Request.Form("HOUSENO"), "'", "''")) & ", " _
& "POSTCODE = '" & CStr(Replace(Request.Form("POSTCODE"), "'", "''")) & ", " _
& "TOWN = '" & CStr(Replace(Request.Form("TOWN"), "'", "''")) & "', " _
& "PHONENO = '" & CStr(Replace(Request.Form("PHONENO"), "'", "''")) & ", " _
& "LEAGUENO = '" & CDate(Replace(Request.Form("LEAGUENO"), "'", "''")) & ", " _
& "WHERE (PLAYERNO = " & iRecordId & ")"
' If something does throw an error, checking this is
' actually a valid command often helps debug.
Response.Write strSQL
Set cnnDBEdit = Server.CreateObject("ADODB.Connection")
cnnDBEdit.Open CONN_STRING
===> cnnDBEdit.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords
cnnDBEdit.Close
Set cnnDBEdit = Nothing
Response.Write("<p>Record Id #" & iRecordId & " updated!</p>")
Response.Write(BACK_TO_LIST_TEXT)
[/code]
error trying to update record in asp
