Ricky11
asked on
Update Autonumber field
I have a asp script which pulls a bunch of fields from a access database, one of the fields ia a autonumber field, its not direclty editible in access.
But from asp, I would like to be able to edit this field, I tried but I get the following error message :
[Microsoft][ODBC Microsoft Access Driver] Cannot update 'orderid'; field not updateable.
The reason why i want to update it is becuase my credit card processor does not accept duplicate orderid fields, for example if the cc did not go through the first time it will record the orderid, then for exmaple if i correct the cc numbers and process it again it will come back with a duplicate order recieved, so i thought i could change the orderid filed by adding a -1 in front of the exisitng orderid number and running a update query as follws :
strsql = "UPDATE [ORDER] "
Strsql = Strsql & "SET [orderid]=" & orderidnew & " "
Strsql = Strsql & "WHERE [orderid] = " & orderid & ""
datacon.Execute(strSQL)
howver it doesn't seem to want to update the filed since is a autonuber field... any workarounds with this.. i dont want to change the datatype to integer in access, it must remain autonumber, but using some script in asp, i need to be able to change it if needs be.
i am sure there is a way around this. For example
AUTONUMBER is 123,124,125,126
if i want i should be able to change 124 to 124-1 so :
123,124-1,124,126 and the next autonumber remains 127.
tks.
But from asp, I would like to be able to edit this field, I tried but I get the following error message :
[Microsoft][ODBC Microsoft Access Driver] Cannot update 'orderid'; field not updateable.
The reason why i want to update it is becuase my credit card processor does not accept duplicate orderid fields, for example if the cc did not go through the first time it will record the orderid, then for exmaple if i correct the cc numbers and process it again it will come back with a duplicate order recieved, so i thought i could change the orderid filed by adding a -1 in front of the exisitng orderid number and running a update query as follws :
strsql = "UPDATE [ORDER] "
Strsql = Strsql & "SET [orderid]=" & orderidnew & " "
Strsql = Strsql & "WHERE [orderid] = " & orderid & ""
datacon.Execute(strSQL)
howver it doesn't seem to want to update the filed since is a autonuber field... any workarounds with this.. i dont want to change the datatype to integer in access, it must remain autonumber, but using some script in asp, i need to be able to change it if needs be.
i am sure there is a way around this. For example
AUTONUMBER is 123,124,125,126
if i want i should be able to change 124 to 124-1 so :
123,124-1,124,126 and the next autonumber remains 127.
tks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
if i change the datatype to integer, then i would have to code the orderid to increment automaticly correct? dont think i want to to that. would require a lot of changes.
i could re-create the order but then in that case if the cc declines 3 times or 4 times there would be 4 dead orders in the system marked as cancelled, would be a waste of space in the long run.
i am looking in to your 3rd idea of adding a new field, and defaulting to zero.
Let me type outlound... Hmm... If carderror field = declined then if submiting order again, change counter = counter + 1, then join the orderid field and the counter field together and pass this as the order id ...
okay sounds like it could work.