?
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
1 solution
Medium Priority
?
377 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
Join The Community
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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Watch The Video
Question has a verified solution.

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

A simple trick to resize a control at runtime
Article by: trestan
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…
Signal R - A Real-Time Application
Article by: Anandhi
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Bar Graphs in an Access Query
Video by: crystal
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse
Video by: Durga Charan
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Advertise Here
Suggested Courses

752 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

Advertise Here