The following code is from ASP101.com and shows how to properly update a record using the ADO object. Instructions are in the code comments.
Code:
<%' Defining some constants to make my life easier! (Same as Sample 1 & 2)
' Begin Constant Definition
' DB Configuration constants
' Fake const so we can use the MapPath to make it relative.
' After this, strictly used as if it were a Const.
Dim DB_CONNECTIONSTRING
' ODBC
'DB_CONNECTIONSTRING = "DRIVER={Microsoft Access Driver (*.mdb)};" _
' & "DBQ=" & Server.Mappath("./db_scratch.mdb") & ";"
' OLE DB
DB_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.Mappath("db_dsn.mdb") & ";"
' We don't use these, but we could if we neeeded to.
'Const DB_USERNAME = "username"
'Const DB_PASSWORD = "password"
'Now we override the above settings to use our SQL server.
'Delete the following line to use the sample Access DB.
DB_CONNECTIONSTRING = Application("SQLConnString")
' ADODB Constants
' You can find these in the adovbs.inc file
' Do a search for it and it should turn up somewhere on the server
' If you can't find it you can download our copy from here:
' http://www.asp101.com/samples/download/adovbs.inc
' It may not be the most recent copy so use it at your own risk.
%>
<!-- #INCLUDE FILE="adovbs.inc" -->
<%
' End Constant Definition
%>
<%
Dim I ' Standard looping var
Dim iRecordToUpdate ' Id of deleted record
Dim strSQL ' String variable for building our query
'We're going to keep this as simple as we can.
' 1. Create a Recordset object
' 2. Connect the Recordset to the table
' 3. Find the record to update
' 4. Update the record
' 5. Update the table
' 6. Close the Recordset
'Step 1:
Dim objRecordset
Set objRecordset = Server.CreateObject("ADODB.Recordset")
'Step 2:
' Get the Id of the record to update
iRecordToUpdate = Request.QueryString("id")
' If the record ID passed in isn't a number, we set it to
' one so we don't cause SQL query errors. I use 0 since I
' know there's no record in the DB with an id of 0
If IsNumeric(iRecordToUpdate) Then
iRecordToUpdate = CLng(iRecordToUpdate)
Else
iRecordToUpdate = 0
End If
' I'm prebuilding our SQL query so it's easier to print
' out in case we need to debug later. I'm using a query
' that will return just the record we want to update.
strSQL = "SELECT * FROM scratch WHERE id=" & iRecordToUpdate & ";"
' The syntax for the open command is
' recordset.Open Source, ActiveConnection, CursorType, LockType, Options
objRecordset.Open strSQL, DB_CONNECTIONSTRING, adOpenKeyset, adLockPessimistic, adCmdText
'Step 3:
' The recordset should only have the one record so:
If Not objRecordset.EOF Then
objRecordset.MoveFirst
'Step 4:
'Only update if we've got a record, o/w we never run this
' String / Text Data Type
objRecordset.Fields("text_field") = CStr(WeekdayName(WeekDay(Date())))
' Integer Data Type
objRecordset.Fields("integer_field") = CInt(Day(Now()))
' Date / Time Data Type
objRecordset.Fields("date_time_field") = Now()
'Step 5:
'Only update if we've got a record, o/w we never run this
objRecordset.Update
Response.Write "Record id " & iRecordToUpdate & " updated!"
End If
'Step 6:
' Finally we close the recordset and release the memory used by the
' object variable by setting it to Nothing (a VBScript keyword)
objRecordset.Close
Set objRecordset = Nothing
Bookmarks