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

Question

I am adding an entry 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 autodial field called UserID. So, how do I get the primary key of the record I just inserted?

Thanks.

Answer 1

I believe that a parameterized query will 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 may be a bit, since I'm more used to using the Sql Server library, rather than the Odbc library, but this should get you started.

Answer 2

"SELECT @@IDENTITY" immediately after the insertion should work with the Microsoft.Jet provider.

Answer 3

You want to add

 ;select scope_identity(); 

after your request and run executeScalar to return the value.

In addition, on a completely unrelated note, you really need to change your code to use parameterized queries so that you do not fall into SQL injection attacks or fail due to insecure strings concatenated into your sql statement.

Answer 4

Got it. Here is the code above:

  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() 

Answer 5

The easiest way is to generate your unique identifier value at the code level, add it to your line 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 the suggestion for stGuidGen guid generation code here .

This client-side method is clear, clean, and useful.