Get ID from new record - Xtreme Visual Basic Talk

07-23-2012, 11:57 AM

Fat_Lip

Centurion

Join Date: Feb 2005

Posts: 113

Get ID from new record

Hello, I need to get the ID (Primary Key) from the record that I just added to my database. I've been looking for answers on MSDN and other places on the internet for a couple of days but I cant get any of the sample code to work with mine because there are so many different ways of doing things and I'm pretty new to writing database code. Here is my code so far.

Code:

Dim da As OleDbDataAdapter
Dim qry As String = _
       "SELECT ID, Species, Information " & _
       "FROM tblSpecies"
da = New OleDbDataAdapter(qry, G_Connection1)
dsQuery = New DataSet()
da.Fill(dsQuery)

da.InsertCommand = New OleDbCommand( _
        "INSERT INTO tblSpecies(Species, Information) " & _
        "VALUES(@Species, @Information) " & _
        "SELECT ID = @@IDENTITY")
da.InsertCommand.Connection = G_Connection1
da.InsertCommand.Parameters.Add("@Species", OleDbType.VarChar, 255, "Species")
da.InsertCommand.Parameters.Add("@Information", OleDbType.Variant, txtInfo.MaxLength, "Information")

Dim dr As DataRow
'get a refference to a new row
dr = dsQuery.Tables(0).NewRow

If txtSpecies.Text = "" Then
     dr("Species") = "Unknown"
Else
     dr("Species") = txtSpecies.Text
End If

dr("Information") = txtInfo.Text

dsQuery.Tables(0).Rows.Add(dr)
da.Update(dsQuery)
dsQuery.AcceptChanges()

da.Dispose()
dsQuery.Clear()

Me.Close()

07-26-2012, 03:42 AM

DrPunk

Senior Contributor

* Expert *

Join Date: Apr 2003

Location: Never where I want to be

Posts: 1,285

I think the way to do it with a dataadapter is to run the select query again after the insert.

Code:

da.InsertCommand = New OleDbCommand( _
        "INSERT INTO tblSpecies(Species, Information) VALUES(@Species, @Information);" & _
        "SELECT ID, Species, Information FROM tblSpecies WHERE ID = @@IDENTITY")

So, after running the Insert it queries all the fields again for whatever was added and you should have the ID of the added rows in the dataset.

The query you've written is more like how you'd do with with a Command object and output parameters, where ID would be an output parameter that you read after executing the Command. But that doesn't work with datasets, as such.

__________________
There are no computers in heaven!

07-26-2012, 10:30 PM

Fat_Lip

Centurion

Join Date: Feb 2005

Posts: 113

Thanks a lot! I was looking for answers everywhere and couldnt find one so I had to do a little hack. I was adding a blank record and then searching the database for the blank record and editing it. I hate it when I have to do things like that, so hopefully this will solve my problem. I'll check it out tonight. Thanks again.

07-27-2012, 09:00 AM

DrPunk

Senior Contributor

* Expert *

Join Date: Apr 2003

Location: Never where I want to be

Posts: 1,285

Something just occurred to me.

In order for this to work then you probably need the primary key field set in the table in your dataset. There's not enough code to tell if that has been done or not. But if it doesn't work then that's something to look at as to why not.

__________________
There are no computers in heaven!