Question Auto Increment as ID Column

[PhillD]

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]

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]

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.

 

[jmcilhinney]

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]

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]

GUID's are off the table as well as they are far too inefficient.

Exactly how is that?

 

[PhillD]

There are several articles accross the web detailing why GUID's result in poor performance. How Using GUIDs in SQL Server Affect Index Performance

 

[jmcilhinney]

There are several articles accross the web detailing why GUID's result in poor performance. How Using GUIDs in SQL Server Affect Index Performance

Will you be working with the volumes of data where that will be significant?

 

[jmcilhinney]

Also, the article you reference shows the worst performance to be when using NEWID to generate the GUID. I'm suggesting that your application generates it, so that part isn't an issue.

 

[PhillD]

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]

All I need is a simple ID number that is issued to the user when the record is saved.

Investigate "SELECT @@IDENTITY" - @@IDENTITY

 

[PhillD]

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]

Have you tried using your VB6 approach? You can still do it that way.

 

[PhillD]

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]

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.

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.