Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update Autonumber field

Posted on 2006-10-23
2
Medium Priority
?
685 Views
Last Modified: 2008-01-16
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.

0
Comment
Question by:Ricky11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 58

Accepted Solution

by:
amit_g earned 2000 total points
ID: 17786913
>>i dont want to change the datatype to integer in access, it must remain autonumber,

Then you can't update this field.

If you don't want the change the datatype of this field, you will have to change the workflow a little bit. Mark this created order as cancelled or some status so that you know that this was cancelled and create a new order with same values.

Another rather simple alternative is to add a new field in the order table. Make 0 as default in that field. Send the OrderId-ThisFiledValue to your CC processor as OrderId. So your most orders would become 123-0, 124-0, 125-0 and so on. For the orders that fail to charge the CC for any reason and you need to do it again, update this field to the next number i.e. 1 and then the OrderId sent to the CC processor becomes 125-1. IF that fails again, the next time increment this field again and the OrderId for Cc processor would then be 125-2 and so on. This way you will never have to update the OrderId and the CC processor would always see a unique OrderId.
0
 

Author Comment

by:Ricky11
ID: 17786936
i see interesting.


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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question