SCOPE_IDENTITY() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SCOPE_IDENTITY()

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-10 : 00:05:39

Here's what I have (OrderID and CustID are PK's and autoincrement):

tblOrders__________tblCustomers
---------__________------------
OrderID____________CustID
CustID_____________Name

Just a simple application I created in ASP.NET and C# with those tables in an SQL database. The user enters their name, clicks Submit, and their information is put into Customers. After that, I want a new Order to be created with the CustID from the Customer just created.

I know I'm supposed to SCOPE_IDENTITY() to create it, but I'm not sure how to use it. I've been told to use a stored procedure, but I'm not sure how to do that either. Here's my code:

SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("INSERT INTO tblCustomers(Name)VALUES('"+TextBox1.Text+"');", conn);
//cmd2 with SCOPE_IDENTITY() inserting into tblOrders

conn.Open();
cmd.ExecuteNonQuery();
//cmd2.ExecuteNonQuery();
conn.Close();

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 00:48:39

You need script like this
INSERT INTO tblCustomers(Name)VALUES(...)
INSERT INTO tblOrders(CustID,..) VALUES (SCOPE_IDENTITY(),...)

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-10 : 00:54:32

Awesome, that worked perfectly, thank you very much.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-10 : 00:55:24

your code is vulnerable to sql injection. You should use parameters instead of string concat.

if you don't fix it, a criminal could take over your server and steal your data.

elsasoft.org

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-10 : 09:58:08

OK, now I've got another problem. Here's what I have (only ToppingID in tblToppings is a PK, tblOrderToppings is a linking table):

tblOrderToppings____________tblToppings
----------------____________-----------
OrderID_____________________ToppingID
ToppingID___________________Topping

In the web form, there is a checkbox that is bound to tblToppings, and the user checks off each topping they want on their order. Whenever an order is placed, the OrderID should be inserted into tblOrderToppings as well as the topping, and it should do that for each topping. There's only four toppings, so if the user selected the first and third topping, it would look like this:

tblOrderToppings
---------------------------
OrderID | ToppingID
---------------------------
1 | 1
1 | 3

How can I insert that? Would I use a join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 10:04:55

INSERT INTO tblCustomers(Name)VALUES(...)
INSERT INTO tblOrders(CustID,..) VALUES (SCOPE_IDENTITY(),...)--here CustID is SCOPE_IDENTITY()
INSERT INTO tblOrderToppings(OrderID,ToppingID)
SELECT SCOPE_IDENTITY(),--here OrderID is the SCOPE_IDENTITY()
ToppingID
FROM tblToppings WHERE Topping =<selected topping value for application>

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-10 : 10:48:01

Here's what I have and it's giving me an error:

  foreach (ListItem item in CheckBoxList1.Items)
        {
            if(item.Selected)
            {
                cmd3.CommandText = "INSERT INTO tblOrderToppings(OrderID, ToppingID) " +
                                   "SELECT SCOPE_IDENTITY(), ToppingID " +
                                   "FROM tblToppings " +
                                   "WHERE Topping = " + item.Text;
                cmd3.Connection = conn;
                cmd3.ExecuteNonQuery();
            }
        }

What am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 11:15:18

Where's the insert statement before?SCOPE_IDENTITY requires an insert to immediately happen before it. It returns the last inserted ID value. so you should insert value to tblorders and then do this.

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-10 : 11:29:02

Sorry, I guess I should put this:

INSERT INTO tblCustomers(Name) VALUES('"+TextBox1.Text+"')
INSERT INTO tblOrders(CustID) VALUES (SCOPE_IDENTITY())

And then this code runs:


        //insert toppings data into tblOrderToppings
        foreach (ListItem item in CheckBoxList1.Items)
        {
            if(item.Selected)
            {
                cmd3.CommandText = "INSERT INTO tblOrderToppings(OrderID, ToppingID) " +
                                   "SELECT SCOPE_IDENTITY(), ToppingID " +
                                   "FROM tblToppings " +
                                   "WHERE Topping = " + item.Text;
                cmd3.Connection = conn;
                cmd3.ExecuteNonQuery();
            }
        }

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 11:43:25

quote:

Originally posted by Apples

Sorry, I guess I should put this:

INSERT INTO tblCustomers(Name) VALUES('"+TextBox1.Text+"')
INSERT INTO tblOrders(CustID) VALUES (SCOPE_IDENTITY())

And then this code runs:


        //insert toppings data into tblOrderToppings
        foreach (ListItem item in CheckBoxList1.Items)
        {
            if(item.Selected)
            {
                cmd3.CommandText = "INSERT INTO tblOrderToppings(OrderID, ToppingID) " +
                                   "SELECT SCOPE_IDENTITY(), ToppingID " +
                                   "FROM tblToppings " +
                                   "WHERE Topping = " + item.Text;
                cmd3.Connection = conn;
                cmd3.ExecuteNonQuery();
            }
        }

Exactly. As what SCOPE_IDENTITY does is to give you the autogenerated ID in same connection by the last insert statement.

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-10 : 11:47:56

.NET is still giving me an error in my query, but I don't see one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 12:00:15

First try executing commands on query analyser and see if it errors. Once everythings executing fine in QA put it in .NET code

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-01-10 : 12:21:20

quote:

cmd3.CommandText = "INSERT INTO tblOrderToppings(OrderID, ToppingID) " +
"SELECT SCOPE_IDENTITY(), ToppingID " +
"FROM tblToppings " +
"WHERE Topping = " + item.Text;

You are getting errors because you are concatenating together SQL statements with data and not properly escaping or delimiting your data, causing syntax errors, messy code, unnecessary data type conversions, and security vulnerabilities. You should ALWAYS use parameters for your sqlCommands, whether you use stored procedures or not, no exceptions, ever.

Read this carefully:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

and be sure to read up on SQL Injection:

http://en.wikipedia.org/wiki/SQL_injection

- Jeff
http://weblogs.sqlteam.com/JeffS

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-10 : 12:48:43

OK, I finally got it working, here's my code:


        foreach (ListItem item in CheckBoxList1.Items)
        {
            if(item.Selected)
            {
                cmd.CommandText = "INSERT INTO tblOrderToppings(OrderID, ToppingID) " +
                                  "SELECT SCOPE_IDENTITY(), ToppingID " +
                                  "FROM tblToppings " +
                                  "WHERE '" + item.Text + "' = Topping";
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
        }

Only problem now is that say you are order #12, and you want two toppings on your pizza, topping 2 and topping 4, whatever those may be. This is what is in the table because of the SCOPE_IDENTITY():

tblOrderToppings
-------------------
OrderID | ToppingID
-------------------
12 | 2
NULL | 4

How can I fix that?

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-10 : 12:51:20

Jeff -
Thanks for the link on parameters, I'll be using them from now on. But do you also have a link that will easily explain how to use stored procedures? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 12:58:55

quote:

Originally posted by Apples

OK, I finally got it working, here's my code:


        foreach (ListItem item in CheckBoxList1.Items)
        {
            if(item.Selected)
            {
                cmd.CommandText = "INSERT INTO tblOrderToppings(OrderID, ToppingID) " +
                                  "SELECT SCOPE_IDENTITY(), ToppingID " +
                                  "FROM tblToppings " +
                                  "WHERE '" + item.Text + "' = Topping";
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
        }

Take the SCOPE_IDENTIITY onto a variable as

SET @Var=SCOPE_IDENTITY after

INSERT INTO tblOrders(CustID,..) VALUES (SCOPE_IDENTITY(),...)--here CustID is SCOPE_IDENTITY()

and use this for two inserts after

INSERT INTO tblOrderToppings(OrderID, ToppingID)
SELECT @Var, ToppingID FROM tblToppings

INSERT INTO tblOrderToppings(OrderID, ToppingID)
SELECT @Var, ToppingID FROM tblToppings

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 12:59:57

quote:
Originally posted by visakh16

quote:
Originally posted by Apples

OK, I finally got it working, here's my code:

foreach (ListItem item in CheckBoxList1.Items)
{
if(item.Selected)
{
cmd.CommandText = "INSERT INTO tblOrderToppings(OrderID, ToppingID) " +
"SELECT SCOPE_IDENTITY(), ToppingID " +
"FROM tblToppings " +
"WHERE '" + item.Text + "' = Topping";
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
}

Only problem now is that say you are order #12, and you want two toppings on your pizza, topping 2 and topping 4, whatever those may be. This is what is in the table because of the SCOPE_IDENTITY():

tblOrderToppings
-------------------
OrderID | ToppingID
-------------------
12 | 2
NULL | 4

How can I fix that?

Take the SCOPE_IDENTIITY onto a int variable as

SET @Var=SCOPE_IDENTITY after

INSERT INTO tblOrders(CustID,..) VALUES (SCOPE_IDENTITY(),...)--here CustID is SCOPE_IDENTITY()

and use this for two inserts after

INSERT INTO tblOrderToppings(OrderID, ToppingID)
SELECT @Var, ToppingID FROM tblToppings

INSERT INTO tblOrderToppings(OrderID, ToppingID)
SELECT @Var, ToppingID FROM tblToppings

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-10 : 16:21:49

OK, this will probably be the last question on this. I'm now trying to use stored procedures for the queries, and I'm having problems because I don't know where to put the SCOPE_IDENTITY(). I have:

tblOrders__________tblCustomers
---------__________------------
OrderID____________CustID
CustID_____________Name

And I have my first stored procedure that is:


ALTER PROCEDURE sproc_InsertCustomers 
	(
		@Name varchar(50)
	)
AS
	INSERT INTO tblCustomers(Name)
	VALUES     (@Name)
RETURN

And is called in the C# application by:


        SqlCommand cmd = new SqlCommand("sproc_InsertCustomers", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = TextBox1.Text;

        cmd.ExecuteNonQuery();

But now I'm trying to do a sproc_InsertOrder that will take the CustID using SCOPE_IDENTITY, but I don't know how to use it in a stored procedure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 00:43:50

Is there any need for seperate sp? you can do everything in same sp

ALTER PROCEDURE sproc_InsertCustomers
(
@Name varchar(50)
)
AS
INSERT INTO tblCustomers(Name)
VALUES (@Name)
INSERT INTO tblOrders(CustID,..) VALUES (SCOPE_IDENTITY(),...)
--other inserts to follow this
RETURN

and if you really want to use seperate sp's for each insert then do like this

ALTER PROCEDURE sproc_InsertCustomers
(
@Name varchar(50),
@CustID int OUTPUT
)
AS
INSERT INTO tblCustomers(Name)
VALUES (@Name)
RETURN SCOPE_IDENTITY()

and refer this link for .NET code to retrieve it in appln:-

[url]http://aspnet.4guysfromrolla.com/articles/062905-1.aspx[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources