How to retrieve the generated autonumber from insert query

[jeboy]

How do I get the generated auto number from previous insert query in vb.net?

 

[jmcilhinney]

The specifics depend on how you're performing the insert and what database you're inserting into. Please take the time to provide a description of your situation when posting and don't assume that it will be one size fits all.

 

[SphyNxXx]

The specifics depend on how you're performing the insert and what database you're inserting into. Please take the time to provide a description of your situation when posting and don't assume that it will be one size fits all.

I think what he wants is to pull the autogenerated ID out of a previously inserted row of a database.

If this is what you are wanting to do, in the table of question, do you have any other unique columns that you will know the entry? If you do, you can use a datatable in your code to find the row. The code is something like:


Dim DT as New YourDataTable
Dim adapter as New YourDataTableAdapter
Dim row as YourDataTable.SomethingRow
Dim autoGenId As Integer

adapter.Fill(DT)

row = DT.Select("Column_Name =(**or LIKE**) '*something*')

autoGenId = row.AutoId

There may be a more simple way to do this, but this method worked for me

 

[jeboy]

Yeah thats what I mean SphyNxXx, but?

Yeah SphyNxXx, thats what i mean. Can i ask you another question? How can I insert data to multiple related tables in access database using vb.net? For example, I have two tables that are related to each other namely:
1. Group table
2. Member table
all members in Member Table are belong to a specific group in Group Table.

thank you very much

 

[cjard]

You must perform the inserts one at a time, using a transaction to ensure all-or-nothing success

 

[jeboy]

Sphynxxx, sample code pls?

Can you give me a sample code of your example in pulling the autogenerated id from previous insert query?

 

[SphyNxXx]

Here's the way I did it in my program. Its an opening screen for a system Ive been working on for practice that manages a baseball/softball league:

'Declare Variables
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] league [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SoftballDataSet.LeagueDataTable[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SoftballDataSetTableAdapters.LeagueTableAdapter[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] league_row, row() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SoftballDataSet.LeagueRow[/SIZE]
 
' Create a new row for the table League in my Database
[SIZE=2]league_row = league.NewRow()[/SIZE]
' Take the name the user inputted in a text box and create the new row using this name and an auto generated ID
[SIZE=2]league_row.League_Name = LeagueNameTextBox.Text[/SIZE]
' Not really important to you here...just helps me keep up with stuff later on in the program
[SIZE=2]Softball_Module.CurrentLeague.SetLeagueName(LeagueNameTextBox.Text)[/SIZE]
 
' Add the row and update the database
[SIZE=2]league.Rows.Add(league_row)[/SIZE]
[SIZE=2]adapter.Update(league)[/SIZE]
 
' Here's where I try to find the new row. I search for any rows that contains the text the user inputted in the text box and store it in an array of rows
[SIZE=2]row = league.Select([/SIZE][SIZE=2][COLOR=#800000]"League_Name Like '"[/COLOR][/SIZE][SIZE=2] & Softball_Module.currentLeague.GetLeagueName() & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] x [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0[/SIZE]
[SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] x < row.Length()[/SIZE]
[SIZE=2][COLOR=#0000ff]    If[/COLOR][/SIZE][SIZE=2] row(x).League_Name = Softball_Module.currentLeague.GetLeagueName() [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2]    Softball_Module.CurrentLeague.SetLeagueId(row(x).LeagueId)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]    Exit[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Else[/COLOR][/SIZE]
[SIZE=2]   x += 1[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE]

There could be a better way than this but it worked for me. In the datebase table I was working with on this example, there is 2 columns.
LeagueId (autogenerated int)
LeagueName(string)

 

[cjard]

Can you give me a sample code of your example in pulling the autogenerated id from previous insert query?

Assuming you ran the query with a parameterized command, the value of the autonumber parameter after the insert finishes is the value that was generated.. naturally, your command needs a parameter placeholder for the field..