retrieve id when adding a new record in SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I’m adding a record to a SQL database. One of the columns in the database
generates a uniquely identifying integer when a record is added. I would like
to retrieve this number when I add a new record to the table. By the way I do
not want to use stored procedures.

I’m using the following VB.NET code to add a record to a database how would
I change this so that at the same time I can retrieve the unique record id
generated by the database in the table?

Imports System.Data.SqlClient

Dim conDB As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand

conDB = New SqlConnection("connection string")

strInsert="Insert Products (ProdName,ProdCat) Values ('Flat Chair',’Chair’)"

cmdInsert = New SqlCommand(strInsert, conDB)

conDB.Open()

cmdInsert.ExecuteNonQuery()

conDB.Close()


Thank you.
 
Basically SELECT SCOPE_IDENTITY() would return the new id. Might just be
that it's nicer to use procedures and output parameters whatever, to handle
these.
 
Using a SP to do this would be easier, but you can still execute a batch to
do this from the client.
Add a

SELECT SCOPE_IDENTITY()

to the end of your INSERT statement. Of course, I don't recommend using
INSERT statements (or any ad hoc SQL) without use of a Command and
Parameters collection.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top