Create a record, return its ID

This code doesn't work - how do I create a record, and return its record number to a variable?

------------------------------------------------------
   Dim ans As String
        Dim cmd1 As New SqlClient.SqlCommand
        Dim sql1 As String
        Dim sql2 As String = "INSERT INTO tblShipments (Date) VALUES (GETDATE())"
        Dim shipid As String

        ' Quick and dirty way to confirm.
        ans = MsgBox("Complete Shipment and Print Bill of Lading?", MsgBoxStyle.YesNo, "Completeing Load")
        If ans = vbNo Then Exit Sub

        ' fill dataset with the pallets ready to go.
        DAShipmentPrep.Fill(DSShipmentPrep)
        ' DEBUG: MsgBox(DSShipmentPrep.Tables(0).Rows.Count.ToString)

        ' create a new shipment main record
        ' and find out what the Shipment ID is for this new shipment.
        SqlConnection1.Open()
        cmd1.CommandText = sql2
        cmd1.Connection = SqlConnection1
        cmd1.ExecuteNonQuery()
        shipid = cmd1.ExecuteReader.GetSqlString(0).ToString ' THIS FAILS I AM TRYING TO FIDN OUT THE ID.
        SqlConnection1.Close()

        MsgBox(shipid)
----------------------
THANKS!
Dan FloodDeveloperAsked:
Who is Participating?
 
gregoryyoungCommented:
  Dim ans As String
        Dim cmd1 As New SqlClient.SqlCommand
        Dim sql1 As String
        Dim sql2 As String = "INSERT INTO tblShipments (Date) VALUES (GETDATE());SELECT SCOPE_IDENTITY()"

        Dim shipid As String

        ' Quick and dirty way to confirm.
        ans = MsgBox("Complete Shipment and Print Bill of Lading?", MsgBoxStyle.YesNo, "Completeing Load")
        If ans = vbNo Then Exit Sub

        ' fill dataset with the pallets ready to go.
        DAShipmentPrep.Fill(DSShipmentPrep)
        ' DEBUG: MsgBox(DSShipmentPrep.Tables(0).Rows.Count.ToString)

        ' create a new shipment main record
        ' and find out what the Shipment ID is for this new shipment.
        SqlConnection1.Open()
        cmd1.CommandText = sql2
        cmd1.Connection = SqlConnection1
        dim foo as integer = cmd1.ExecuteScalar()
        SqlConnection1.Close()

        MsgBox(shipid)
0
 
gregoryyoungCommented:
run excutescalar() on the command ...

the query should be sql2 & ";SELECT SCOPE_IDENTITY()"

this will force it to return the ID ... some may also say to use @@IDENTITY but there are alot of issues there, scope identity is your best bet.
0
 
Dan FloodDeveloperAuthor Commented:
thank you - but i don't know how to implement that?  can you please tell me the exact code?
0
 
Dan FloodDeveloperAuthor Commented:
you, sir, are a god.
0
 
gregoryyoungCommented:
nah just a volunteer, glad to help.

let me know if you need anything else.

Cheers,

Greg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.