problem with scope_identity

[Dan]

Hi,

after inserting a record in a table (sql server), i need the last value of
the primary key of that table, which increments automatically, before
inserting that value in another table.

I did like this:

comd.CommandText = "insert into [mytable] (field1, field2) values(@datbeg
,@datend')"
comd.Parameters.Add("@datbeg", SqlDbType.DateTime).Value = tda
comd.Parameters.Add("@datend", SqlDbType.DateTime).Value = tda2
connection.Open()
comd.CommandText = "DECLARE @orderid int"
comd.CommandText = "SET @orderid = SCOPE_IDENTITY()"
comd.CommandText = "select @orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())

This giives an error:
"error: Must declare the scalar variable "@orderid". "

Thanks
Dan

 

[Bob Johnson]

Hi Dan,

Your code and approach have a number of problems. Please stick with me; I'm
not being critical... just pointing out the facts:

1. In every line where you have [comd.CommandText = ...] you are completely
changing the value of comd.CommandText. That is, each line *overwrites* the
previous value of CommandText. So, when you finally get around to
comd.ExecuteScalar(), the value of comd.CommandText is simply "select
@orderid".

2. The solution to the above problem (of overwriting the value of
CommandText in each successive line) is to concatenate the incremental
values, possibly via +=, and ensuring you add a blank space between each).
But you DON'T want to do that in your situation because of #3 below:

3. It appears that you are trying to create a stored procedure without
creating one (and instead putting all of the T-SQL in your CommandText.
There's no way that's going to work the way you are attempting.

What will work is to do the following:
1. Create a stored procedure that does the INSERT, followed immediately by
SET @orderid = SCOPE_IDENTITY, and returning exactly one result set via
SELECT @OrderID.

Then In your client code

2. Set CommandText = name of the stored procedure

3. Set ComandType = CommandType.StoredProcedure

4. Add to the Command.Parameters collection one SqlParameter object for each
of the parameters in the stored procedure.

5. Finally execute the stored procedure via the ExecuteScalar method (as you
were already trying to do).

The above assumes you have opened a connection etc..

-HTH

 

[Dan]

Thanks, you're right of course with the concatenation.

But, instead of using a stored procedure (which i know is beter), would it
be posiible to do that in code-behind, more or less like this:
comd.CommandText = "DECLARE @orderid int," _
& "SET @orderid = SCOPE_IDENTITY()," _
& "select @orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())

because i get the error:
Incorrect syntax near the keyword 'SET'.
Incorrect syntax near ',

Thanks again

Hi Dan,

Your code and approach have a number of problems. Please stick with me;
I'm not being critical... just pointing out the facts:

1. In every line where you have [comd.CommandText = ...] you are
completely changing the value of comd.CommandText. That is, each line
*overwrites* the previous value of CommandText. So, when you finally get
around to comd.ExecuteScalar(), the value of comd.CommandText is simply
"select @orderid".

2. The solution to the above problem (of overwriting the value of
CommandText in each successive line) is to concatenate the incremental
values, possibly via +=, and ensuring you add a blank space between each).
But you DON'T want to do that in your situation because of #3 below:

3. It appears that you are trying to create a stored procedure without
creating one (and instead putting all of the T-SQL in your CommandText.
There's no way that's going to work the way you are attempting.

What will work is to do the following:
1. Create a stored procedure that does the INSERT, followed immediately by
SET @orderid = SCOPE_IDENTITY, and returning exactly one result set via
SELECT @OrderID.

Then In your client code

2. Set CommandText = name of the stored procedure

3. Set ComandType = CommandType.StoredProcedure

4. Add to the Command.Parameters collection one SqlParameter object for
each of the parameters in the stored procedure.

5. Finally execute the stored procedure via the ExecuteScalar method (as
you were already trying to do).

The above assumes you have opened a connection etc..

-HTH

Hi,

after inserting a record in a table (sql server), i need the last value
of the primary key of that table, which increments automatically, before
inserting that value in another table.

I did like this:

comd.CommandText = "insert into [mytable] (field1, field2) values(@datbeg
,@datend')"
comd.Parameters.Add("@datbeg", SqlDbType.DateTime).Value = tda
comd.Parameters.Add("@datend", SqlDbType.DateTime).Value = tda2
connection.Open()
comd.CommandText = "DECLARE @orderid int"
comd.CommandText = "SET @orderid = SCOPE_IDENTITY()"
comd.CommandText = "select @orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())

This giives an error:
"error: Must declare the scalar variable "@orderid". "

Thanks
Dan

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Thanks, you're right of course with the concatenation.

But, instead of using a stored procedure (which i know is beter), would it
be posiible to do that in code-behind, more or less like this:
comd.CommandText = "DECLARE @orderid int," _
& "SET @orderid = SCOPE_IDENTITY()," _
& "select @orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())

because i get the error:
Incorrect syntax near the keyword 'SET'.
Incorrect syntax near ',

Use semicolon to separate the SQL statements, then it might work.

comd.CommandText = "DECLARE @orderid int;" _
& "SET @orderid = SCOPE_IDENTITY();" _
& "select @orderid"

But why not simply:

comd.CommandText = "select SCOPE_IDENTITY()"

 

[Bob Johnson]

RE:
<< then it might work >>

Right - can you (op) please let us know if you get this to work? I'm
curious.

 

[Bob Johnson]

Use semicolon to separate the SQL statements, then it might work.

comd.CommandText = "DECLARE @orderid int;" _
& "SET @orderid = SCOPE_IDENTITY();" _
& "select @orderid"

But why not simply:

comd.CommandText = "select SCOPE_IDENTITY()"

A couple of other thoughts:
1. set comd.CommandType = CommandType.Text
2. In all those strings you are concatenating for the .CommandText value, be
sure to add white space where appropriate.
3. to test this, first get the script to work in query analyzer (SS2K) or
Management Studio (2005). Once it works there, then move it to your client
code.

-HTH

 

[Dan]

Yes, it works like this:

comd.CommandText = "insert into [mytable] (field1, field2) values(@datbeg
,@datend');" _
& " select SCOPE_IDENTITY()"

Thanks

 

[Bob Johnson]

How are you populating @datbeg and @datend? That query won't work unless you
send parameters. Is that your actual query?

Just curious. Thanks!