CODEWITHSUNDEEP
asp.netvb.netms-accessinsert
Gezim
Gezim

Reputation: 7308

Getting primary key after an insert in asp.net (visual basic)

I'm adding a record like this:

    Dim pathString As String = HttpContext.Current.Request.MapPath("Banking.mdb")
    Dim odbconBanking As New OleDbConnection _
             ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" + pathString)
    Dim sql As String
    sql = "INSERT INTO tblUsers ( FirstName, LastName, Address, City, Province, Zip, Phone, UserName, [Password])" & _
              " VALUES ('" & firstName & "', '" & lastName & "', '" & address & _
            "', '" & city & "', '" & province & "', '" & zip & "', '" & phone & "', '" & username & "', '" & password & "');"
    odbconBanking.Open()
    Dim cmd As New OleDbCommand(sql, odbconBanking)
    cmd.ExecuteNonQuery()
    odbconBanking.Close()

The primary key is an autonumber field called UserID. So, how do I get the primary key of the record I just inserted?

Thanks.

Upvotes: 2

Views: 16088

Answers (5)

Philippe Grondier
Philippe Grondier

Reputation: 11138

The simplest way would be to generate your uniqueIdentifier value at the code level, add it to your string and send it to the server.

Dim sql As String, _
    myNewUserId as variant

myNewUserId = stGuidGen    'this function will generate a new GUID value)'
sql = "INSERT INTO tblUsers ( userId, LastName)" & _
      " VALUES ('" & myNewUserId & "','" & LastName);"

You can check a proposal for the stGuidGen guid generating code here.

This client-side technique is clear, clean and usefull

Upvotes: 1

Chad Braun-Duin
Chad Braun-Duin

Reputation: 2208

I believe a parameterized query would look something like this:

Dim pathString As String = HttpContext.Current.Request.MapPath("Banking.mdb")
Dim odbconBanking As New OleDbConnection _
     ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" + pathString)
Dim sql As String
sql = "INSERT INTO tblUsers ( FirstName, LastName, Address, City, Province, Zip, Phone, UserName, [Password])" & _
      " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
odbconBanking.Open()
Dim cmd As New OleDbCommand(sql, odbconBanking)

//Add Params here
cmd.Parameters.Add(new OdbcParameter("@FirstName", firstName))
cmd.Parameters.Add(new OdbcParameter("@LastName", lastName))
//..etc

//End add Params here

cmd.ExecuteNonQuery()
Dim newcmd As New OleDbCommand("SELECT @@IDENTITY", odbconBanking)
uid = newcmd.ExecuteScalar

odbconBanking.Close()

My syntax might be a bit off as I am more accustomed to using the Sql Server library and not the Odbc library, but that should get you started.

Upvotes: 5

Gezim
Gezim

Reputation: 7308

Got it. Here's how the above code looks now:

    Dim pathString As String = HttpContext.Current.Request.MapPath("Banking.mdb")
    Dim odbconBanking As New OleDbConnection _
             ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" + pathString)
    Dim sql As String
    sql = "INSERT INTO tblUsers ( FirstName, LastName, Address, City, Province, Zip, Phone, UserName, [Password])" & _
              " VALUES ('" & firstName & "', '" & lastName & "', '" & address & _
            "', '" & city & "', '" & province & "', '" & zip & "', '" & phone & "', '" & username & "', '" & password & "');"
    odbconBanking.Open()
    Dim cmd As New OleDbCommand(sql, odbconBanking)
    cmd.ExecuteNonQuery()
    Dim newcmd As New OleDbCommand("SELECT @@IDENTITY", odbconBanking)
    uid = newcmd.ExecuteScalar

    odbconBanking.Close()

Upvotes: 2

Kibbee
Kibbee

Reputation: 66122

You'll want to add 

;select scope_identity();

after your query, and run executeScalar to get the value back.

Also, on a completely unrelated note, you really should change your code to use parameterized queries so you don't get caught with SQL injection attacks, or crashes due to inproperly escaped strings concatenated into your sql statement.

Upvotes: 2

Christian C. Salvadó
Christian C. Salvadó

Reputation: 827296

"SELECT @@IDENTITY" just after the insertion should work with Microsoft.Jet Provider.

Upvotes: 4

Related Questions