Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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
6
Medium Priority
?
379 Views
Last Modified: 2010-04-15
Hi,

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;
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();
            Response.Redirect("../");
            }
0
Comment
Question by:BlackCollarWorker
[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
6 Comments
 
LVL 15

Expert Comment

by:Timbo87
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;
0
 

Author Comment

by:BlackCollarWorker
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.

Michael
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11743628
I have requested this thread to be "un-answered", see here:
http://www.experts-exchange.com/Community_Support/Q_21085760.html
0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 150 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
            cnn.Open()
            cmd.ExecuteNonQuery()
        Else
            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
            cnn.Open()
            cmd.ExecuteNonQuery()
        End If
        cmd.CommandText = "SELECT @@IDENTITY AS 'CustomerID';"
        cmd.ExecuteScalar()
        strCustomerID = CStr(cmd.ExecuteScalar())
        Me.lblCustomerID.Text = strCustomerID
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
    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
        cnn.Open()
        strOrderID = CStr(cmd.ExecuteScalar())
        Me.lblOrderID.Text = strOrderID
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
    End Sub
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

618 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