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
371 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
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: 11743531
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 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
            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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run time Error 4 49
Greetings C# program 17 108
free scanner TWAIN can be operated with a Web application 9 63
Can I command line build a C# application? 6 33
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…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 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