Question Auto Increment as ID Column

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
I am developing a form that is linked to a single table. I am using an auto increment field as the record ID the users are given to identify their record. If 2 users open the form at the same time, they are given the same ID number. When the first user saves, they will keep the number, however, when the second user saves, their number will change.

Record ID’s should remain consistent after being presented to the user, seems like one of the downsides to using unlinked datasets. There has to be a sensible way around this. Does anyone have any suggestions?

Thanks
 

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
So I read the long post that already existed on this topic and still did not arrive at a suitable answer.

Without getting on my high horse, I would like to point out that the Auto ID is probably a good argument FOR a linked DATASET. We are now left with a complex problem which there used to be a simple solution.

For the moment, I will start at the beginning by letting you know that I am trying to write an expenses system. I work in the oil field and our users are far from what you would call I.T. Literate. I have been tasked with writing the simplest solution possible so that users can be reimbursed for their sales expenses.

I have worked with VB, VBA and Access for 15 years but I got stuck at the first hurdle. Database integration. I am not a fan of using wizards load and save my data, but for the sake of being a newbie, I am giving it a try. I watched the Beth Massi videos which got me started but they only got me to a very basic point.

I began working on a simple input form. Taking advice from the other post, I will only show the ID number after the record has been saved. But how? The Auto ID field populates when a new record is created, but i don’t want the user to see it. So I created a new textbox called SavedExpenseID and hid the Auto Increment ID (called ExpenseID). When I save, I populate SavedExpenseID with the text in ExpenseID but SavedExpenseID remains blank. What am I doing wrong? There has to be a better (simpler) way to do this. Please help
 

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
When I unhide the ExpenseID text box, the code works. Why is it everything I seem to try that would work in VB doesn't work in .net? It's still not fixed though.
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,173
Location
Sydney, Australia
Programming Experience
10+
Either don't present the ID to the user until the data has been saved or else don't use an auto-generated ID. If you go with the second option then you would have your application generate the ID in the form of a GUID and then save that to the database with the rest of the data.
 

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
I appreciate the response but you didn't add anything that I already didn't know.

As per my last 2 posts, when you use the wizards to create the dataset and fields etc.. it creates the auto ID column and will populate it with a value when you create records. I hid the field and tried to populate another textbox with after the record was saved but it doesn't work because when i execute SavedID.text = AutoID.text nothing happens, my new box stays blank. If I unhide the AutoID box, the code works. So you can't reference textbox.text if that text box is hidden.

So what I was really asking for was for someone to tell me how they handle this kind of problem in their programs. GUID's are off the table as well as they are far too inefficient.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,173
Location
Sydney, Australia
Programming Experience
10+

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
Our ERP system is written in SQL and is absolutely chocked full of GUID's and SQL Cursors. It performs terribly. It has over 1000 tables and probably as many SP's. Every table has insert, update and delete trigger. It is the worst performing DB I have ever encountered. I can retrieve 20,000 records faster from a networked MS Access database than I can retrieve 200 records from the ERP database. So maybe now you can understand why I don’t want to travel that road. The use of a GUID is inappropriate for what I want to achieve. All I need is a simple ID number that is issued to the user when the record is saved.
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
I wish I could tell you I knew how to pass @@IDENTITY out of the Insert statement back to the form but I don't.

I am a newbie at VB 2008. Back in the VB 6 days, I would code all this data stuff myself. I would write a connect statement and output the data to a recordset. From there, I could output the data to the form or navigate the recordset and perform any data modification I wished all in 1 object. I had COMPLETE control over the data! If I wanted to do something different on a particular insert/update based on some criteria or condition I could. Now, it seems with using the table adapter and binding source, that control has gone and I need to use whatever insert/update statement has been defined for the table adapter.

So for simplification purposes, IF I was writing this in VB 6, I would create a form with 2 text boxes (Employee ID and Expense ID) and a button (Save). When the user opens the form, it is not bound to anything, there’s no dataset or anything like that. The user enters their employee ID and clicks save, the save button creates a connection to the DB and inserts the record and retrieve @@identify from the insert statement. Then I display @@Identity value in a text box. Simple

So how would I do this in VB2008 considering that the form is already bound to the data and the ExpenseID automatically populates when I create a new record?

This is the frustration of not having complete CONTROL over the data like I did in VB6. Someone please help me.
 

rcombs4

Well-known member
Joined
Aug 6, 2008
Messages
189
Programming Experience
3-5
Have you tried using your VB6 approach? You can still do it that way.
 

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
I could, but I thought that perhaps this was a really common problem in VB.net programming and was wondering how other people handle it. As I am such a newb, I thought I would try and use the data wizards etc but I have to say I'm not happy with all the different data objects it creates. I have 12 or so different data objects just for a form that links to 2 related tables and a lookup table.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,173
Location
Sydney, Australia
Programming Experience
10+
OK, so the problem is actually that you don't know how to get the ID generated by the database after saving the record. Is that correct? If so then basically you need to tack a SELECT statement onto your INSERT statement, e.g.
Code:
INSERT INTO ...; SELECT ID = SCOPE_IDENTITY()
or the like. The specifics depend on whether you are saving a single record using ExecuteNonQuery or multiple records using Update.
 

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
Thanks for the advice.

I can't believe how difficult this is!

Here's what I really need. As the ID will be saved in the DataSet when the record is saved, I just need to be able to get the value from the DataSet. Again, nothing works! I have reseached this on other sites and the code that keeps coming up is DataSetName.Tables(0).Rows(0)(0).ToString but when i call this code, it errors with no record at row 0. That is impossible! I just saved the record, there has to be a row 0.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,173
Location
Sydney, Australia
Programming Experience
10+
If the error message says that there's no row at index 0 then there's no row at index 0. You must be either clearing the DataSet, clearing the DataTable or else creating a new one of either or both. What you're trying to do is very simple but you are complicating it somehow. Exactly how I don't know because we've never seen any of your code, VB or SQL.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Thanks for the advice.

I can't believe how difficult this is!

Here's what I really need. As the ID will be saved in the DataSet when the record is saved, I just need to be able to get the value from the DataSet. Again, nothing works! I have reseached this on other sites and the code that keeps coming up is DataSetName.Tables(0).Rows(0)(0).ToString but when i call this code, it errors with no record at row 0. That is impossible! I just saved the record, there has to be a row 0.

If the following words mean nothing to you, read the DW2 link in my signature, section "Creating a Simple Data App"
DataTable, TableAdapter, DataSet

Make a new project

Make a new DataSet file in that project

So, make a new tableadapter in your dataset, query:
SELECT * FROM erpdb.someTable
Ensure as you go through the wizard that you click on the Advanced button and ensure that the option to "refresh the dataset (populate changes by the db back into the dataset)" is ticked

Finish the wizard

Click on the autonumber column, and set
AutoIncrement yes
AutoIncrementSeed -1
AutoIncrementStep -1

Click on the tableadapter itself

Amend the insert query to have ;SELECT name_of_the_autonumber_column = SCOPE_IDENTITY() on the end
(This might have to be ;SELECT @name_of_the_autonumber_parameter = SCOPE_IDENTITY() - i'm not sure; i dont work with sql server - in oracle you populate the parameter you passed in if you want the db generated value back out)

Go to the form designer

Open the datasources window, click the dropdown arrow next to the table name you just added, to DETAILS mode (not grid)

Drag the table to the form

Start the app

Make a new record, using the + button on the provided-for-you bar at the top

See the ID column has -1 in the box

Write details

Save the record

See the ID box now has the ID value generated by the DB

-

To work with these things programmatically you have to appreciate that a dataset is like a db - its a collection of datatables, datatables are collections of datarows. If your local datatable has 10 datarows, and the new one you just added is at position 11 then you have to refer to it as such

Either, because it is the current row:

DirectCast(DirectCast(myBindingSource.Current, DataRowView).Row, MyXYZTableRow).WHATEVER_COLUMN

Or because you know it is at position 11:
myDataSetInstance.MyXYZTable(11).WHATEVER_COLUMN

-

Rarely do we need to do this; the ID is returned to our dataset, but often we don't care, unless we are giing it out as a reference to the record to e.g. a caller on the phone..
 

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
Thanks for posting this. I didn’t receive my usual email update so I only noticed it today.

I would like to ask you 1 question though. In your opinion, is it better to use identity columns as record numbers, or assign them yourself at the point of insert? The reason I ask is because when I use an identity field, the identity box on the form displays “-1”. I understand I could change the way it displays, BUT I don’t want to issue an ID to the user if it could change after they save. If I create the ID myself during insert, the ID box will stay blank until the user saves the record.
 

PhillD

Well-known member
Joined
Mar 5, 2010
Messages
91
Programming Experience
10+
I am now reading that it is a bad idea to manually assign incremental ID values due to the MAX() statement that must be executed during the insert.

So now I am back to the same problem. I am going to start a new thread because this one has changed too much and become clouded.
 
Last edited:
Top Bottom