Solved

vb.net get insert id

Posted on 2006-07-20
7
352 Views
Last Modified: 2008-01-09
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
Comment
Question by:jackjohnson44
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 17147515
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
 
LVL 17

Expert Comment

by:ZeonFlash
ID: 17147705
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
 
LVL 23

Expert Comment

by:apresto
ID: 17147729
only use a ; between the 2 statements if its an ansi language

"insert into tests (description, offset) values('" & testName & "'," & offsetValue & "); SELECT SCOPE_IDENTITY()"
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jackjohnson44
ID: 17147833
I am using access
0
 
LVL 17

Expert Comment

by:ZeonFlash
ID: 17147918
You should still be ok using @@IDENTITY in Access.  See this article for an example:  http://support.microsoft.com/default.aspx/kb/232144
0
 

Author Comment

by:jackjohnson44
ID: 17147969
how do I use @@IDENTITY?

can you please post some code?
0
 
LVL 14

Accepted Solution

by:
jake072 earned 500 total points
ID: 17148113

[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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now