Link to home
Create AccountLog in
Avatar of oksum73
oksum73

asked on

Add 1 to a numeric field in MS SQL Server 2005

I have this working but it adds 2 and not 1 why??
The linkClicks field is numeric(18, 0)

<!--#include file="../sqlcon/db.asp" -->
<%
      session.CodePage=65001
      Response.CacheControl="no-cache"
      Response.AddHeader "Pragma","no-cache"
      Response.Expires=0

      newID=13
      
      strSQL="Update links set linkClicks = linkClicks + 1 Where linkID =13"
      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open strSQL, objCON, 1, 3


      objCON.Execute strSQL      
      objCON.close
      
      objCON.close
      Set objCON = Nothing      
%>
Avatar of Thejaka
Thejaka
Flag of Sri Lanka image

Your SQL command gets called twice.
First, when you call Recordset.Open()
Next, when you call Recordset.Execute()

Remove the statement "objCON.Execute strSQL" (and also your extra "objCON.close" statement) and it should work as you expect.
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You're right about the recordset being needless, acperkins. Actually, I was simply pointing out why oksum73's code wasn't producing the desired result. :)
I agree with you.  It is just that it might have confused the author when you mentioned the Recordset's non-existant Execute method, so I was trying to set the record straight as to the correct way to do it.
Oh, sorry! I only now noticed I'd put it down as "Recordset.Execute()". My bad.