BlackCollarWorker
asked on
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=USERN AME;pwd=PA SSWORD;dat abase=DBNA ME;");
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(" @contactNa me", 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(" @typeOrgOt her", 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("../");
}
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
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("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myCommand.Parameters.Add("
myConnection.Open();
myCommand.ExecuteNonQuery(
myConnection.Close();
Response.Redirect("../");
}
ASKER
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
Michael
Question cross-posted here:
https://www.experts-exchange.com/questions/21085723/How-do-I-get-the-record-ID-of-the-record-I-just-inserted-Using-C-ASP-NET-SQL-Server-2000.html
https://www.experts-exchange.com/questions/21085724/How-do-I-get-the-record-ID-of-the-record-I-just-inserted-Using-C-ASP-NET-SQL-Server-2000.html
https://www.experts-exchange.com/questions/21085727/How-do-I-get-the-record-ID-of-the-record-I-just-inserted-Using-C-ASP-NET-SQL-Server-2000.html
https://www.experts-exchange.com/questions/21085723/How-do-I-get-the-record-ID-of-the-record-I-just-inserted-Using-C-ASP-NET-SQL-Server-2000.html
https://www.experts-exchange.com/questions/21085724/How-do-I-get-the-record-ID-of-the-record-I-just-inserted-Using-C-ASP-NET-SQL-Server-2000.html
https://www.experts-exchange.com/questions/21085727/How-do-I-get-the-record-ID-of-the-record-I-just-inserted-Using-C-ASP-NET-SQL-Server-2000.html
I have requested this thread to be "un-answered", see here:
https://www.experts-exchange.com/questions/21085760/Points-awarded-incorrectly.html
https://www.experts-exchange.com/questions/21085760/Points-awarded-incorrectly.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In the stored procedure:
Insert ... into ...
return @@identity
And in the code:
myCommand.Parameters.Add("