Generate autonumber 2

[mansukh20]

Hi guys,

I have developed a system using oracle 9i and visual basic 6. Im not quite sure how to generate an autonumber for the next row. All i need is when i click on add button on my form, i want the next number in the series to be generated and displayed on the assigned text box. It would be a great help if you guys can help.

Thank you in advance.

 

[rchandr3]

There are n number of ways to generate a number...

It depends on your application, uniqueness, saving the number, etc.

So if you could give the problem a little eloborately, We can give a shot at that...


Cheers,

Ravi

 

[mansukh20]

I have a table with customerID which starts from 100000, each number is unique since it identifies a customer. All i want is when i click on the add button i want the system to give me the next number in the series and by displaying it in the text box. But if i can then then i want the system not to save this on the table

In oracle i have created a sequence which automatically generates the number and i added this code in vb:

insert into Customer (CustomerID)
values(mansukh.seqCustomerID.nextval);

when i click on add this gets triggered and a number is added but how do i stop it from adding when i click on the cancel button

I hope this helps. Would you need further information then please let me know. Thanks for the reply

Manny

 

[ArtieChoke]

You could do a select max(CustomerID) from the table, add 1 to it and put that in your text box. Of course, when you save the record you'll need to check for errors in case someone else inserted one in-between your select and add.

If you need to do it with the insert because it's an autonumber field, then you'll just have to delete the record if cancel is pressed.


"I think we're all Bozos on this bus!" - Firesign Theatre

 

[mansukh20]

I took your advice and implemented the following code:

Dim strSQL As String

strSQL = "select max(customerid) from customer2"
adoCustomer.RecordSource = strSQL
adoCustomer.Refresh

incCustomerID = adoCustomer.Recordset.Fields("CUSTOMERID")
incCustomerID = incCustomerID + 1
txtCustomerID.Text = incCustomerID

But getting an error:

Item cannot be found in the collection corresponding to the requested name or ordinal

I havent come across this one before. Would you kindly help me with this?

Manny

 

[mansukh20]

ArtieChoke,

I have found a solution to my problem. Thanks alot for your help

Manny

 

[Forri]

Does this application work on one computer if not you will have problems when adding 1 to the current number if not...well oyu've solved your problem! Else i could have solution for you because i asked the same question 2years ago and they helped to it too! Thanks to you guys!!

Nick

 

[mansukh20]

Forri,

What is your solution so as i can have a look at it and possibly implement it as a precaution.

Manny

 

[Forri]

Well i had a problem when i was issueing Cash Sale numbers, because the client wanted to see the number as soon as he clicked on the new button! I used the autonumber for generating unique CashSale numbers.

What i did was i inserted two new fields within the table; 1 Used and 1 Locked. Then created a function that when the user creates a new cash sale it insert a new record and sets the Locked field as true, returning the Autonumber. If the user cancels the cash sale then the Locked Field will get to False again releasing the record, else if he saves the record than the field Used will also be set to true!

If the cash sale had been canceled than that Cash Sale number would have been vacant. If the user starts a new cash sale, the function will first go tru the records checking which has the Locked field and the Used fields set as false, if it finds one it uses it if not it creates a new one! this way no record will be left empty!

Now the problem i created for you; that is when multiple users are using the system...this will be useful as you will still be using the autonumber locking each record for the current individual!

Hope i gave you abit of my mind over here...its not that easy to explain! If you need more info just ask!

Nick

 

[mansukh20]

It seems clear enough. Thanks for that. I will look into it.

Manny

 

[Forri]

No probs...would you need any help ask me! i will keep it flaged for notification!

Nick

 

[mansukh20]

Forri

So sorry to go out of subject but would you be able to look at another query for me. its under "Using Data Environment and Data Report"

Im stuck on this

Manny

 

[Forri]

I did!! :>