Incrementing a Long Integer in an Access table

[madfiddler]

I am executing the following SQL on an Access table from
ASP

UPDATE MYTABLE SET MYCOUNT=(MYCOUNT + 1)


Unfortunately this seems to add 2! If I add 2 in the code,
the actual result is +4! If I do -1, then the result is -2.

If I change MYCOUNT to an Integer from a Long Integer it
works fine.

If a create an update query in Access, whatever data type
MYCOUNT is, it works.

Not sure where the problem lies ODBC or OLEDB. It seems on
a web server with MDAC 2.6 it works OK, with MDAC2.7 RTM
or MDAC 2.7 SP1 Refresh I get the problem.

I don't want to have to install MDAC 2.8 unless it will
definitely fix the problem. Reverting to 2.6 is not a
viable option either. Can anyone advise? Can anyone
confirm if this is a problem in MDAC 2.8?


cheers

[Bob Barrows]

madfiddler wrote:

> I am executing the following SQL on an Access table from
> ASP
>
> UPDATE MYTABLE SET MYCOUNT=(MYCOUNT + 1)
>
>
> Unfortunately this seems to add 2! If I add 2 in the code,
> the actual result is +4! If I do -1, then the result is -2.
>

I cannot reproduce this (using ADO 2.6). Here is my test code:

set cn = server.CreateObject("ADODB.Connection")
cn.Open "provider=microsoft.jet.oledb.4.0;" & _
"data source=C:\MyDocu~1\W2KMig~1\w2kmigration.mdb"
cn.Execute "UPDATE MYTABLE SET MYCOUNT=(MYCOUNT + 1)",,129
set rs=cn.Execute("select MYCOUNT FROM MYTABLE",,1)
Response.Write rs(0)
rs.Close:set rs=nothing
cn.Close:set cn=nothing
Response.end

Unfortunately I cannot test this on a machine with 2.7 installed.

Are you sure you are not executing the query twice?

Is using a saved query rather than dynamic sql an option? Does the problem
occur when using a saved query?

Instead of
cn.Execute "UPDATE MYTABLE SET MYCOUNT=(MYCOUNT + 1)",,129

Create a saved query called SavedUpdateQuery using the above sql statement
and do this:
cn.SavedUpdateQuery

HTH.
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[madfiddler]

>madfiddler wrote:

>> I am executing the following SQL on an Access table from
>> ASP
>>
>> UPDATE MYTABLE SET MYCOUNT=(MYCOUNT + 1)
>>
>>
>> Unfortunately this seems to add 2! If I add 2 in the

code,

>> the actual result is +4! If I do -1, then the result

is -2.

>>

Bob Barrows wrote:

>I cannot reproduce this (using ADO 2.6). Here is my test

code:

>
>set cn = server.CreateObject("ADODB.Connection")
>cn.Open "provider=microsoft.jet.oledb.4.0;" & _
>"data source=C:\MyDocu~1\W2KMig~1\w2kmigration.mdb"
>cn.Execute "UPDATE MYTABLE SET MYCOUNT=(MYCOUNT + 1)",,129
>set rs=cn.Execute("select MYCOUNT FROM MYTABLE",,1)
>Response.Write rs(0)
>rs.Close:set rs=nothing
>cn.Close:set cn=nothing
>Response.end
>
>Unfortunately I cannot test this on a machine with 2.7

installed.

>
>Are you sure you are not executing the query twice?
>
>Is using a saved query rather than dynamic sql an option?

Does the problem

>occur when using a saved query?
>
>Instead of
>cn.Execute "UPDATE MYTABLE SET MYCOUNT=(MYCOUNT + 1)",,129
>
>Create a saved query called SavedUpdateQuery using the

above sql statement

>and do this:
>cn.SavedUpdateQuery
>

I have actually installed MDAC 2.8 on a test machine now
and I get the same problem.

Also changing the data type seems to have only temporarily
fixed things - the bug seems to have come back and is
intermittent.

I've scoured all my ASP and this is definitely the only
place the update of the counter occurs. This was the first
thing I checked - also the page is not reloading or
anything strange like that.


Thanks for the saved query idea Bob I'll give it a try and
see what happens.