Question How do I get the new row ID in an access database table BEFORE insert the new row.

[aeskan]

Hi everybody.

I'm programming with VB .NET 2010 and using a Microsoft Access Database file which is bound to my project as a Dataset. Now I want to know: BEFORE inserting a new row into my dataset table what will be the new ID number, or totally is it possible to know?

Surely you know the autonumber ID of a table is different with row count number, for there may be deleted rows between the table rows. Like the following example:

Row 0: ID=1
Row 1: ID=2
Row 2: ID=4 (ID number 3 is deleted)
Row 3: ID=5
Row 4: ID=9 (ID numbers 6,7,8 are deleted)

Now how do we know what ID number of Row 5 will be before inserting the new row? Perhaps ID numbers 10 and 11 are either deleted or not, so it may be 10, may be 11 or 12 or whatever! Is there a way to know it BEFORE adding or inserting the new row into the table?

Thank you beforehand.

 

[jmcilhinney]

Your description doesn't really make sense. If you insert five rows then they will all have sequential IDs. The only reason for an ID to be skipped is if an error occurs inserting the row it was generated for. Regardless of that, there's no way to know what the ID will be for a row that you haven't inserted yet. You know for sure that it won't be equal to or less than the last ID already in the table. You could guess that it would be 1 greater then the last ID but that will not be the case if an error occurred on a previous insert. All you can do is retrieve the ID after the row has been inserted.

 

[aeskan]

Thank you.. Yes, seems there is now way to know it unless we insert a new row. But the only reason causes ID numbers not to be sequential is not just when an error occurs, as I mentioned above, when you remove or deleted a row it also happens. Thanks anyway.:smile:

 

[s1ckOh]

well... i suppose if the new id is an autonumber and it increments +1 per new row you could read the last number in the first field to a msgbox or whatever and assume the next row number is +1

Dim sql As String = "SELECT * FROM [table] WHERE [column name]=" & [param]
Dim cmd As OleDbCommand = New OleDbCommand(sql, [oledbconnection])
Dim dr As OleDbDataReader = cmd.ExecuteReader

'i dont know how to go to the last row but this is how you read it:

while dr.read
     msgbox(dr(0))   'maybe dr(0).tostring idk
end while

 

[aeskan]

Dear s1ckOh

With greeting for your joining to this conversation and your helpfull suggestion, for more clarity I illustrate the question again as below.

Consider there is an empty MS Access DataTable, and then the End User saves some unknown amount of data (via user interface) to the table. Ok? The table will be for example as following:

Row 1; ID=1: John
Row 2; ID=2: Mike
Row 3; ID=3: Sam
Row 4; ID=4: Peter
Row 5; ID=5: Carol

Consider again after a while End User removes some unknown amount of data (e.g Mike, Sam and Carol) from DataTable, so our example will be as following:

Row 1; ID=1: John
Row 2; ID=4: Peter

Now in our example, what will be the "New" record ID number? It will be (6), not (4+1=5), because the last ID before romoving was 5 and ID numbers are unique, as following:

Row 1; ID=1: John
Row 2; ID=4: Peter
Row 3; ID=6: Emma

Here we knew that because we saw all of the processes. Sadly we are not always with our DataTable and do not know End User how much data and records would insert to the table and how much and which one of rows would be removed by them! So with considering that New Record ID is different with Last Record ID plus 1, the question is: If some where in our source codes, we needed to know the next New Record ID number (primary key), how can we guess or compute it, or generally is it possible?

Thanks again.

 

[s1ckOh]

hmm... good point, if emmas id would have been 5 not 6 my solution might have worked. the only other way i could think of "guessing" the next record id is by assigning the value yourself. still be primary key but not autonumber.

use my previous example to find the last assigned id

Dim sql As String = "SELECT * FROM [table] WHERE [column name]=" & [param]
Dim cmd As OleDbCommand = New OleDbCommand(sql, [oledbconnection])
Dim dr As OleDbDataReader = cmd.ExecuteReader

while dr.read 
     msgbox(dr(0))
end while

change

while dr.read
     Dim dat(2) As String
     Dim str As String = "INSERT INTO [Table] VALUES(" & dat(0) & "," & dat(1) & ")"
     Dim cmd As OleDbCommand = New OleDbCommand(str, [oledbconnection])
     dim i as integer = val(dr(0)) + 1
     dat(0) = "'" & i & "'"
     dat(1) = "'" & textbox1.text & "'"
     cmd.ExecuteNonQuery()
end while

sorry if im not getting it.

 

[aeskan]

Dont be sorry, any kind of disccussion is useful and thought provoking. Now I'm sure that assigning the value myself (or saving the greatest ID number somewhere) is the only known way to reach that purpose. Thank you. Good companionship.

 

[aeskan]

Finally I found a practical trick for the problem (may be yours or some body else's future problem). If after removing each row, we Compact our database file (.mdb or .accdb) the problem of difference between last ID and new ID will solve and it will follow the rule of "Last Record ID plus 1", because after compacting our database the last Primary Keys of last removed records will disappear and the new record ID actually will be last record ID plus 1, and the Emma ID will be (5) as you said.:smile:

P.s
For more information see How to compact a Microsoft Access database by using Visual Basic .NET