How do I get the record ID of the record I just inserted? Using C# ASP.NET SQL Server 2000

Posted on 2004-08-07
Last Modified: 2010-04-15

In MSSQL I have auto incrementing PK for the row I'm inserting into. The the code I use below is to insert into the table. What I need is as soon as I insert the record, I also need to return back what the PK was of my newly inserted row. I will need to use this value for another section of the FORM for use as a FK into another table in which I going insert some other data based on a few criterias. I tried to us @@IDENTITY to no avail. Any help is greatly appreciated. I pretty new at this stuff and just learning. Thank You.

void InsertCommand(Object sender, System.EventArgs e) {
            SqlConnection myConnection;
            myConnection = new SqlConnection("Server=NAME;uid=USERNAME;pwd=PASSWORD;database=DBNAME;");
            SqlCommand myCommand;
            myCommand = new SqlCommand("Insert Into fundAcctTBL (orgName, taxID, address01, address02, city, state, zip, phone, webURL, fax, contactName, dayPhone, evePhone, email, typeOrgID, typeOrgOther, org501C, listOrg, promoKit, addToWeb) Values (@orgName, @taxID, @address01, @address02, @city, @state, @zip, @phone, @webURL, @fax, @contactName, @dayPhone, @evePhone, @email, @typeOrgID, @typeOrgOther, @org501C, @listOrg, @promoKit, @addToWeb)", myConnection);
            myCommand.Parameters.Add("@orgName", SqlDbType.VarChar, 255).Value = txtbxOrgName.Text;
            myCommand.Parameters.Add("@taxID", SqlDbType.VarChar, 50).Value = txtbxTaxID.Text;
            myCommand.Parameters.Add("@address01", SqlDbType.VarChar, 500).Value = txtbxAddress.Text;
            myCommand.Parameters.Add("@address02", SqlDbType.VarChar, 500).Value = txtbxAddress02.Text;
            myCommand.Parameters.Add("@city", SqlDbType.VarChar, 255).Value = txtbxCity.Text;
            myCommand.Parameters.Add("@state", SqlDbType.VarChar, 255).Value = txtbxState.Text;
            myCommand.Parameters.Add("@zip", SqlDbType.VarChar, 15).Value = txtbxZip.Text;
            myCommand.Parameters.Add("@phone", SqlDbType.VarChar, 15).Value = txtbxPhoneAreaCode.Text + txtbxPhone.Text;
            myCommand.Parameters.Add("@webURL", SqlDbType.VarChar, 255).Value = txtbxWebURL.Text;
            myCommand.Parameters.Add("@fax", SqlDbType.VarChar, 15).Value = txtbxFaxAreaCode.Text + txtbxFax.Text;
            myCommand.Parameters.Add("@contactName", SqlDbType.VarChar, 255).Value = txtbxContactName.Text;
            myCommand.Parameters.Add("@dayPhone", SqlDbType.VarChar, 15).Value = txtbxDayPhoneAreaCode.Text + txtbxDayPhone.Text;
            myCommand.Parameters.Add("@evePhone", SqlDbType.VarChar, 15).Value = txtbxEvePhoneAreaCode.Text + txtbxEvePhone.Text;
            myCommand.Parameters.Add("@email", SqlDbType.VarChar, 255).Value = txtbxEmail.Text;
            myCommand.Parameters.Add("@typeOrgID", SqlDbType.Int, 4).Value = radbtnDescOrg.SelectedItem.Value;
            myCommand.Parameters.Add("@typeOrgOther", SqlDbType.VarChar, 255).Value = txtDescOrgOther.Text;
            myCommand.Parameters.Add("@org501C", SqlDbType.Int, 4).Value = radbtn501Org.SelectedItem.Value;
            myCommand.Parameters.Add("@listOrg", SqlDbType.Int, 4).Value = radbtnListing.SelectedItem.Value;
            myCommand.Parameters.Add("@promoKit", SqlDbType.Int, 4).Value = radbtnFundKit.SelectedItem.Value;
            myCommand.Parameters.Add("@addToWeb", SqlDbType.Int, 4).Value = radbtnAddWeb.SelectedItem.Value;
Question by:BlackCollarWorker
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
LVL 15

Expert Comment

ID: 11743503
You can add a return to the stored procedure and return @@identity.

In the stored procedure:

Insert ... into ...
return @@identity

And in the code:
myCommand.Parameters.Add("@return", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;

Author Comment

ID: 11743524
I'm actually not using a stored procedure, I'm just inserting this directly to the database, is using a stored procedure the only way of doing this? Thanks.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 11743531
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11743628
I have requested this thread to be "un-answered", see here:
LVL 37

Accepted Solution

gregoryyoung earned 50 total points
ID: 11744566
just to show you a better way ... 1 round trip instead of 2 ...

Private Sub WriteCustomerInfo()
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim strCustomerID As String
        cmd.CommandType = CommandType.Text
        If Me.rdoCC.Checked Then
            cmd.CommandText = "INSERT INTO Customers (SessionID, FirstName, LastName, Address, Location, City, State, ZipCode, Country, PhoneNumber, " & _
            "CCName, CCNumber, CCExpDate) VALUES (" & CInt(Session("SessionID")) & ",'" & Me.txtFirstName.Text & "','" & Me.txtLastName.Text & "','" & Me.txtAddress.Text & "','" & _
            Me.txtLocation.Text & "','" & Me.txtCity.Text & "','" & Me.txtState.Text & "','" & Me.txtZipCode.Text & "','" & Me.lblCountry.Text.ToString & "','" & (CStr(Session("PhoneNumber"))) & _
            "','" & Me.txtCCName.Text & "','" & Me.txtCCNumber.Text & "','" & Me.txtCCExpDate.Text & "')"
            cmd.Connection = cnn
            cmd.CommandText = "INSERT INTO Customers (SessionID, FirstName, LastName, Address, Location, City, State, ZipCode, Country, PhoneNumber, " & _
            "EFTName, EFTType, EFTAccountNumber, EFTBankName, EFTRoutingNumber) VALUES (" & CInt(Session("SessionID")) & ",'" & Me.txtFirstName.Text & "','" & Me.txtLastName.Text & "','" & Me.txtAddress.Text & "','" & _
            Me.txtLocation.Text & "','" & Me.txtCity.Text & "','" & Me.txtState.Text & "','" & Me.txtZipCode.Text & "','" & Me.lblCountry.Text.ToString & "','" & (CStr(Session("PhoneNumber"))) & _
            "','" & Me.txtAccountName.Text & "','" & Me.txtAccountType.SelectedValue.ToString & "','" & Me.txtAccountNumber.Text & "','" & Me.txtBankName.Text & "','" & Me.txtRoutingNumber.Text & "')"
            cmd.Connection = cnn
        End If
        cmd.CommandText = "SELECT @@IDENTITY AS 'CustomerID';"
        strCustomerID = CStr(cmd.ExecuteScalar())
        Me.lblCustomerID.Text = strCustomerID
    End Sub
    Private Sub WriteOrdererdItems()
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim strOrderID As String
        Dim ThisDay As Date
        ThisDay = Today
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "INSERT INTO OrderedITems (CustomerID, OrderDate, ProductID, ProductName, ProductPrice, Quantity, OrderSessionString) " & _
                "SELECT " & Me.lblCustomerID.Text & ",'" & ThisDay & "', TempSession.ProductID, TempSession.ProductName, " & _
                "TempSession.ProductPrice, TempSession.Quantity, '" & Me.Session.SessionID.ToString & "' FROM TempSession WHERE TempSession.SessionString = '" & Me.Session.SessionID & ";'SELECT @@IDENTITY AS 'OrderID'"
        cmd.Connection = cnn
        strOrderID = CStr(cmd.ExecuteScalar())
        Me.lblOrderID.Text = strOrderID
    End Sub

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 describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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