Autonumber Problem

[PLH]

Hi guys,
I have searched three forums and didn’t find any similar problem to mine. That really surprised me because this should be very conmen problem with database apps. Now, I have a database connected to the app which was created by the wizard (in the design time). The problem is that if a user deletes the last record in a parent table of a relational database and terminates the app, the next time it runs the added new row’s recordID number will differ from the recordID (autonumber) in the database. I understand that dataset’s autonumber gets the database’s last autonumber when app starts but how can it get the next real autonumber. For example the last database autonumber is 6 and the next will be 8 because the last time app run the last record was deleted. Now the datasets gets 6 and when a new record is added the recordID in the datagrid will show 7. As you can see the chilledtable’s records will point to a different parenttable record. How can I fix this problem? Any suggestions are welcome!

 

[ManicCW]

This should not be a happening. Could you send some code you are using?

 

[PLH]

Well, since I have connected database to the app in the design time, I jus use

[FONT=Times New Roman][SIZE=3]dataAdapter.Fill(dataset, “Table”) ‘fill the dataset.[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'And [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]dataAdapter.Update(dataset, “Table”) ‘updates the table.[/SIZE][/FONT]

The problem is that the database’s last record was deleted at the last time the app run so no meter when I start the app the next autonumber going to be one more then it spouse to be. I need some how to get the next autonumber to solve the problem. I mean the dataset should now what is going to be the next number which will correspond with database autonumber!

 

[sevenhalo]

Is recordID a one to one relationship?

 

[PLH]

no it is one to many

 

[sevenhalo]

Maybe I'm a little confused, by why would the child table point to the parent? Shouldn't the parent reference child records?

The only reason I ask is this...
If your child table (the many in the oo-1) is referencing the parent that has an identity field (sql's autonumbering mechanism), removing a record does not cause the parent table to remove records. They're out of sight, but to keep data integrity, it continues down the list (A nice precaution incase you ever need to recall deleted data, you keep all your identifiers). If the child is referencing the parent table, then this is a hole for orphaning records and causing unintentional cross referencing.

 

[PLH]

Yes you are right, but the table I am talking is a parent table. To be more clear let say I have a parent table named ‘Customers” whit five customers and a chilled table “Orders” with no orders. Now, the user deletes the last customer from the “Customers” table and terminates the app. Next time user starts the app where there are four customer records. Note that the last recordId is 4. Now the user adds a new customer the fifth one, the database autonumber generates a unique number which would be 6 because of the last time deleted record. The dataset generates a autonumber too but it will be 5 since when app started the dataset got the last database autonumber (4) as a start number. So the next autonumber that dataset is going to create is going to be (5). The dataset autonumbers and the database autonumbers are generated independently from each other there for I have this problem. I hope I made my self clear.

 

[sevenhalo]

Very clear, thanks.

Why not retreive the Databases number when you populate the dataset initially? Instead of using the datasets autonumber/index? That would take care of the immediate problem (there'd be a hole, it would show as 1,2,3,4,6... But it would be tightened down).

Of course, with every easy solution comes more problems

If you do inserts to the dataset, it would need to be two commands (assuming you don't use sprocs) to update both sides.

Edit: You probably know about this already, but Commit/Rollback is a god send when doing this.

 

[PLH]

< Why not retreive the Databases number when you populate the dataset initially?>
Well I can retrieve the database number but I can’t retrieve the database’s possible next number!
Actually, populating the database is not the problem but the updating would be the problem. Let take the last example, the user adds the fifth customer and an order. As I said the dataset will generate number 5 and when it saved into database the customer record number will be 6 in the database. As you can see the order will point to non existing customer record.

<Instead of using the datasets autonumber/index?>
Well, I am using datagrid and it uses dataset.
I solved this problem by generating my own recordID number through the sub that I created and I got rid of MS Access autonumber. I changed the data type autonumber to a number and wuala it works perfect. I jus tried to make use of MS Access autonumber but it is not worth.

 

[sevenhalo]

Ok, move away from relying on how the dataset organizes things. Best advice I can give at this point. Silver bullet or not, you still have to be an exceptional shot for it to make a difference.

In this case, all the dataset should be to you is a quick means to show information. The problem lies solely in what you're putting into it and how frequently you update it (the paradigm of every developer).

Datasets autonumbers are like comboboxes indexes except they are static upon load.

The ONLY way to get around this is to have a field in the datasets table that is the databases unique identifier and do all your deletes/updates based on that. When you Create/Update/Delete; rebuild and rebind the table(s) in the dataset. It's one of the downfalls of using bulk objects, it can take massive maintenance. You can simplify it and only requery parts you need, but it's still a pain.

IMHO - datasets are almost ways too much for any task, but to each their own.

 

[Jeevi]

Hi,
This is always the prolem of using data adapter update cmd to perform inserts except using datagrid to do this.u can use sql insert statement with odbccommand or sqlcommand to perform this.This could solve your autogenerate number problem.
otherwise u need to create NewDatarow manually and assign values to that row and then do the update.