Solved

Create a record, return its ID

Posted on 2004-08-27
5
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 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