Solved

Create a record, return its ID

Posted on 2004-08-27
5
225 Views
Last Modified: 2010-04-23
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!
0
Comment
Question by:Dan Flood
  • 3
  • 2
5 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11919048
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
 

Author Comment

by:Dan Flood
ID: 11919119
thank you - but i don't know how to implement that?  can you please tell me the exact code?
0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 11919276
  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
 

Author Comment

by:Dan Flood
ID: 11919411
you, sir, are a god.
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11919477
nah just a volunteer, glad to help.

let me know if you need anything else.

Cheers,

Greg
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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