read and update problem

<i><b>Originally posted by : Peter Osney (webmaster@osneymedia.co.uk)</b></i><br />Hi <br /><br />Im trying to read a record that stores a counter, increment the counter by 1 the update the record. <br /><br />I'm doing something wrong, and get an error. What is wrong with it? code below... Regards. <br /><br /><br /><% <br /><br />'Read old hit rate <br /><br />set rs=Server.CreateObject("adodb.Recordset") <br /><br />sqlstmt= "SELECT * from tblcounter where counterid = 1" <br /><br />rs.open sqlstmt, osneydsn <br /><br />counterid = rs("counterid") <br />hitcount = rs("hitcount") <br /><br />'add 1 to hit count <br />hitcount=hitcount+1 <br /><br />rs.close <br />' clears record set <br /><br />set rs2 = server.createobject("adodb.connection") <br />rs2.open osneydsn <br />'update tblcounter <br />SQLstmt = "UPDATE tblcounter" <br />SQLstmt = SQLstmt & " SET hitcount='" & hitcount & "'" <br />'SQLstmt = SQLstmt & " hitdate='" & date() & "'" <br />SQLstmt = SQLstmt & " Where counterid = " & counterid <br />' creates sql statement to insert article information into database <br /><br />Set RS2 = rs2.execute(SQLstmt) <br /><br />rs2.close <br />' clears record set <br /><br />%>

<i><b>Originally posted by : Tim Curtin (tcurtin@nhhartford.com)</b></i><br />It looks like you are using Access( 'tbl' naming convention).<br />You forgot rs.UPDATE<br />You need a Keyset or dynamic recordset do an AddNew, Update then read the Key field back.<br />You have to do:<br /> <br /> rs.Open sqlstmt, 2,2 'keyset or dynamic rs. not sure of constants. look up in MSDN<br /> rs("hitcount") = rs("hitcount") + 1<br /> rs.Update 'HERE<br /> response.write rs("HitCount")<br /><br /><br />------------<br />Peter Osney at 4/9/2001 6:46:28 AM<br /><br />Hi <br /><br />Im trying to read a record that stores a counter, increment the counter by 1 the update the record. <br /><br />I'm doing something wrong, and get an error. What is wrong with it? code below... Regards. <br /><br /><br /><% <br /><br />'Read old hit rate <br /><br />set rs=Server.CreateObject("adodb.Recordset") <br /><br />sqlstmt= "SELECT * from tblcounter where counterid = 1" <br /><br />rs.open sqlstmt, osneydsn <br /><br />counterid = rs("counterid") <br />hitcount = rs("hitcount") <br /><br />'add 1 to hit count <br />hitcount=hitcount+1 <br /><br />rs.close <br />' clears record set <br /><br />set rs2 = server.createobject("adodb.connection") <br />rs2.open osneydsn <br />'update tblcounter <br />SQLstmt = "UPDATE tblcounter" <br />SQLstmt = SQLstmt & " SET hitcount='" & hitcount & "'" <br />'SQLstmt = SQLstmt & " hitdate='" & date() & "'" <br />SQLstmt = SQLstmt & " Where counterid = " & counterid <br />' creates sql statement to insert article information into database <br /><br />Set RS2 = rs2.execute(SQLstmt) <br /><br />rs2.close <br />' clears record set <br /><br />%>