Link to home
Start Free TrialLog in
Avatar of BlackCollarWorker
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=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("../");
            }
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

There are a couple of way you can do that:

1.  Modify your stored procedure to return the @@IDENTITY (use SCOPE_IDENTITY if using SQL Server 2K), either using RETURN or using an OUTPUT parameter.  Post your stored procedure if you are having difficulty with this.

2.  Better still, don't bother to return the @@IDENTITY, but instead pass all the parameters including the "other data based on a few criterias" and insert into both table in the one stored procedure.
Avatar of BlackCollarWorker
BlackCollarWorker

ASKER

Hi,

I'm actually not even using a stored procedure. Is there a way to do this not using a store procedure?

Mike
Also, there is no need to cross-post the same question in more than one Topic Area:
1 08/07/2004 50 How do I get the record ID of the record...  Open C#
2 08/07/2004 50 How do I get the record ID of the record...  Open ASP.NET
3 08/07/2004 50 How do I get the record ID of the record...  Open .NET
(I have added a comment to each thread to that effect)

And finally please maintain your old open questions.  Here are your abandoned questions:
1 02/02/2004 125 Operation must use an updateable query.  Open C#
2 02/05/2004 50 System.Data.OleDb.OleDbException: Syntax...  Open C#
>>Is there a way to do this not using a store procedure?<<
Yes there is. But you would be better off with a stored procedure.
put the @@identity part in a new SQL command and execute an ExecuteScaler set some variable to the result, and you will have the last inserted ID.  Oh, and you must use the active open connection, that is you must issue a second command the one with the @@identity in it on the same connection.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp

Regards,

Aeros
1. Use a stored procedure
   and try and pass all the related information to it in one go so it can ...
    do all the database activity in one pass

2. You need to use scope_identity  not @@Identity to ensure you get the correct value.....

3. maintain/cancel the questions acperkins has pojnted out please
Lowfatspread,

1.) Still learning how store procedures work.
2.)Okay I see what I can do with scope_identity
3.)How do you close a question? By accepting an answer?

Thanks for your help.
ASKER CERTIFIED SOLUTION
Avatar of gregoryyoung
gregoryyoung
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
still need to use scope_identity to ensure that you get what your after....

and thats still multiple round trips and a lot of difficult to understand dynamic sql embedded in your program code...

put the sql into a stored procedure where it belongs.. and then reap the benefits of modularisation and performance
tuning...

 
whoops I copy and pasted his code you are right lowfatspread it should be scope_identity.

"and thats still multiple round trips and a lot of difficult to understand dynamic sql embedded in your program code..."
public int32 InsertRecord(string sql) {
    return ConnectionObject.ExecuteScalara(sql + ";SELECT SCOPE_IDENTITY()");
}

how is that more than 1 trip ?

I agree with the embedded SQL problem but I will however also greatly disagree on the use of stored procedures in the context of a .net app for simple tasks. The use of sprocs for general things like inserting actually breaks encapsulation and for all intensive purposes marries your app to your database, simply put a database is a mechanism of storage not a layer of encapsulation. I personally also do not recommend the use of "SQL" per say, I recommend the use of persistance layers to generate your SQL for you. One of the main advantages of this mechanism is the isolation that is provided...

1) 1 class represents a data entity
2) valdiation logic resides with this class
3) functional entitites only use this class
4) polymorphism is a available for extension tables ... i.e.

Person_tbl
Contact_tbl
Customer_tbl
Employee_tbl
Vender_tbl

let assume that customers, employees and venders all have a key to their contact record when you write a screen to edit a contact, you can pass in a customer, employee, vender, or contact since the actual implementation classes will all be inheriting from contact.

because of all of the above, moving from 1 database to another becomes as simple as moving the data and setting a flag ... There are some performance trade offs but in every system I have worked on they have been outweighed by the long term benefits in terms of maintainability (especially moving the project from the context of "developers" to "supporting developers" (if you use seperate teams).

Thts not to say that there no gains to be had from using sprocs. The benchmarks that they provide are often times misleading due to 1 round trip vs 2 round trips. A large gain sprocs have is when you look at security they are much easier to manage than tables and they are less likely to fail than your own security code if not using SQL server's security.

BlackCollarWorker: I would recommend taking a look at DataObjects.Net or if you are just curious and don't want to spend money, Gentle.Net both are fairly good persistance layers that are already coded for you. Over the last few years  MS developers have finally begus to support persistance layers over the model listed with sprocs as it is much more scalable to large scale apps. If you are completely unfamiliar with the concept of a persistance layer I would recommend reading up on them and more generally, the agile model  ... (especially use of data patterns and process patterns).

Scott Ambler wrote a great white paper on the design of a persaistance layer: http://www.ambysoft.com/persistenceLayer.html
he also wrote a good introduction to process patterns http://www.ambysoft.com/processPatternsPaper.html

If you search on amazon you will find a TON of books on these subjects. "The Agile Model" "Persistance Layer" "Design Patterns" "The Unified Process"

Cheers,

Greg