Jul 04 2001

Updating and adding records

Posted by admin under ADO database

When updating records you basically have two choices. You can build a SQL statement like sSQL = "update xxx set yyy=" & Request.Form("yyy") & " where id=" & Request.QueryString("id") oConn.Execute sSQL Or you can open a recordset and call oRS.Update. I prefer the second one, since it gives a couple of advantages in date handling for example. In the AdMentor code I used the first way for inserting records into a traceclicks log table which had a datetime timestamp column. I built the statement like: 

sSQL2 = "insert into traceclicks( bannerid, page, dt, ip, undertext ) values (" &nBanner & ","
sSQL2 = sSQL2 & "'" & Request.ServerVariables("HTTP_REFERER") & "','" & now() & "','"
sSQL2 = sSQL2 & Request.ServerVariables( "REMOTE_ADDR" ) & "', " & fUnderText & ")"
oConn.Execute ( sSQL2 )


Some users ( from Germany actually ) complained that they got an error in this function. I rewrote it like: 

Set oRSTC = Server.CreateObject("ADODB.Recordset")
Set oRSTC.ActiveConnection = oConn
oRSTC.Open "select * from traceclicks where bannerid = -1 ", ,adOpenKeyset,adLockOptimistic
oRSTC.AddNew()
oRSTC("bannerid")=nBanner
oRSTC("page")=Request.ServerVariables("HTTP_REFERER")
oRSTC("dt") = now()
oRSTC("ip")=Request.ServerVariables( "REMOTE_ADDR" )
oRSTC("undertext")=fUndertext
oRSTC.Update
oRSTC.Close


And it worked without problems. It was the dt part of the statement that didn't work. I never actually got clear on exactly why it didn't work ( since I know there were other installations in Germany that worked ) but since I got it working I know I will always use that method. The problem with this method ( when adding a new record ) is that you need to open a recordset. As you can see I use a bannerid that will never be used - this way I make sure no unnessecery transfering of unused records is done.

Now for the update code template: 

<%
Set oRSTC = Server.CreateObject("ADODB.Recordset")
Set oRSTC.ActiveConnection = oConn
oRSTC.Open "select * from xxx  where id = " & Request.QueryString("id"), ,adOpenKeyset,adLockOptimistic
oRSTC("yyy")=Request.Form("yyy")
oRSTC.Update
oRSTC.Close
%>


You will need to send an id in the QueryString.