|
wwagner -> Updating Records in Access Database (10/6/2004 16:19:34)
|
I am updating records in an Access database. The users will see all the records and then choose which one to update. They will then be taken to an edit page where they can make their changes. When they submit their changes it calls an update page. My problem is that some of the records update fine, others will return a HTTP 500 Internal Server Error. Their is over 500 records in the table. Record #1 will update fine as will record #472. But record #2 will not and neither will record #11
Below is my update code:
<%
Vid = Request.form("ID")
response.Write(Request.form("ID"))
response.Write(Vid)
'Convert ID to Long int
lngRecordNo = CLng(Request.form("ID"))
Response.Write(lngRecordNo)
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Inetpub\wwwroot\austin_officials\www\data\osis2.mdb;" & _
"Persist Security Info=False"
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM High_Schools WHERE ID =" & lngRecordNo
'Set the cursor type we are using so we can navigate through the recordset
rsUpdateEntry.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsUpdateEntry.LockType = 3
rsUpdateEntry.open SQL, dbConn
If Not rsUpdateEntry.EOF then
rsUpdateEntry.MoveFirst
'Update the records in the record set
rsUpdateEntry.Fields("School") = Request.Form("school")
rsUpdateEntry.Fields("Address") = Request.Form("address")
rsUpdateEntry.Fields("City") = Request.Form("city")
rsUpdateEntry.Fields("St") = Request.Form("state")
rsUpdateEntry.Fields("zip") = Request.Form("zip")
rsUpdateEntry.Fields("Phone") = Request.Form("phone")
rsUpdateEntry.Fields("Fax") = Request.Form("fax")
rsUpdateEntry.Fields("Coach") = Request.Form("coach")
rsUpdateEntry.Fields("Miles") = Request.Form("miles")
rsUpdateEntry.Fields("Flat Fee") = Request.Form("fee")
'Write the updated recordset to the database
rsUpdateEntry.Update
Response.Write("The following school " & rsUpdateEntry.Fields("School") & " has been updated!")
End if
'Reset server objects
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
Set dbConn = Nothing
'Return to the update select page in case another record needs deleting
'Response.Redirect "record.asp"
%>
Any Help will be greatly appreciated
|
|
|
|
