Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

vb.net get insert id

I am using an OleDbConnection.
I would like to get back the auto incremented id from the insert command.

        executeSQL("insert into tests (description, offset) values('" & testName & "'," & offsetValue & ")")

    Public Function executeSQL(ByVal sqlstring$)
        Dim myCommand As New SqlCommand(sqlstring)
        Dim retValue%

        Dim myConnection As New OleDb.OleDbConnection(connectionString)

        myConnection.Open()

        Dim dataCommand As New OleDb.OleDbCommand(sqlstring, myConnection)

        retValue = dataCommand.ExecuteNonQuery()

        myConnection.Close()

    End Function

0
jackjohnson44
Asked:
jackjohnson44
  • 2
  • 2
  • 2
  • +1
1 Solution
 
aprestoCommented:
Hi jackjohnson44,

what database are you using, if its sql server you can use @@Identity

if its access or mysql you might need to run a select to bring back the max(id) from the table

Apresto
0
 
ZeonFlashCommented:
Yes, you can use @@IDENTITY if you're INSERT will never fire any triggers.  It's actually safer to use SCOPE_IDENTITY(), because it will return the new PKID for that INSERT only.

You'll also probably have to use ExecuteScalar, instead of NonQuery:

        retValue = dataCommand.ExecuteScalar()

Aaaand finally, to bring it all together, the SQL statement would read something like this:

"insert into tests (description, offset) values('" & testName & "'," & offsetValue & ") SELECT SCOPE_IDENTITY()"
0
 
aprestoCommented:
only use a ; between the 2 statements if its an ansi language

"insert into tests (description, offset) values('" & testName & "'," & offsetValue & "); SELECT SCOPE_IDENTITY()"
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jackjohnson44Author Commented:
I am using access
0
 
ZeonFlashCommented:
You should still be ok using @@IDENTITY in Access.  See this article for an example:  http://support.microsoft.com/default.aspx/kb/232144
0
 
jackjohnson44Author Commented:
how do I use @@IDENTITY?

can you please post some code?
0
 
jake072Commented:

[After your update code]

Dim cmd As New OleDbCommand("SELECT @@IDENTITY", [YourConnection])
Dim lngID As Long = cmd.ExecuteScalar()

[Close your connection]

Jake
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now