Solved

Create a record, return its ID

Posted on 2004-08-27
5
219 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:jakyll
  • 3
  • 2
5 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
Comment Utility
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:jakyll
Comment Utility
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
Comment Utility
  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:jakyll
Comment Utility
you, sir, are a god.
0
 
LVL 37

Expert Comment

by:gregoryyoung
Comment Utility
nah just a volunteer, glad to help.

let me know if you need anything else.

Cheers,

Greg
0

Featured Post

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!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help error handling a windows service 2 35
I need C# converted to VB.net 1 52
Expando 4 33
Set form below another form 3 25
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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 explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

12 Experts available now in Live!

Get 1:1 Help Now