Problem incrementing integer filed in MS SQL / ASP

Results 1 to 2 of 2

Thread: Problem incrementing integer filed in MS SQL / ASP

  1. #1
    Join Date
    Dec 1969

    Default Problem incrementing integer filed in MS SQL / ASP

    Hi all,<BR><BR>I&#039;ve got some code that i&#039;ve written that doesn&#039;t work. What I want to happen, is for the code to check the user record exists, then when it determines that, I want it to increment the "accessed" field, which is an integer + 1. This is to keep track of how many times the user has accessed their record. Problem is I get an error. <BR><BR><BR>Heres my code:<BR><BR>&#060;%<BR>Function RetrieveAndIncrementCount()<BR><BR> &#039; Local variables<BR> Dim rsCounter, iCount<BR> &#039; Open our recordset<BR> Set rsCounter = Server.CreateObject("ADODB.Recordset")<BR> &#039; SQL Server version:<BR> Set rsCounter = objConn.Execute ("SELECT * FROM users where userid = &#039;" & userid & "&#039; and pwd = &#039;" & pwd &"&#039;;")<BR> &#039; If we&#039;ve got a record then we read the current value<BR> &#039; If we don&#039;t then we create one, set the filename, and start at 0<BR> If rsCounter.EOF = true then<BR> response.redirect ("login.asp?error=1") <BR> Else<BR> iCount = rsCounter("accessed")<BR> &#039; Increment the count and update the DB<BR> rsCounter.Fields("accessed").Value = iCount + 1<BR> rsCounter.Update<BR> End If<BR> &#039; Close our connection<BR> rsCounter.Close<BR> Set rsCounter = Nothing<BR>End Function<BR>%&#062;<BR>&#060;%= RetrieveAndIncrementCount() %&#062;<BR><BR>Here is the error message: <BR><BR>ADODB.Field error &#039;800a0cb3&#039; <BR><BR>Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype. <BR><BR>/video/index.asp, line 89 <BR><BR>Can anyone shed any light on why this isn&#039;t working?<BR><BR>Thanks in advance<BR><BR>James<BR>

  2. #2
    Join Date
    Dec 1969

    Default Error message tells you...

    When you open a recordset via Connection.Execute, you *ALWAYS* get a FORWARD ONLY cursor *and* a READ ONLY lock type. Period.<BR><BR>But why not do it by simply using an UPDATE query, anyway??? <BR> UPDATE users SET accessed=accessed + 1 WHERE userid = ###<BR><BR>Or, since this is SQL Server, using a Stored Proc to accomplish both at once?<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts