• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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

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
BlackCollarWorker
Asked:
BlackCollarWorker
1 Solution
 
Timbo87Commented:
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
 
BlackCollarWorkerAuthor Commented:
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
 
Anthony PerkinsCommented:
I have requested this thread to be "un-answered", see here:
http://www.experts-exchange.com/Community_Support/Q_21085760.html
0
 
gregoryyoungCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now