CODEWITHSUNDEEP
asp.netsql-serverstored-procedures
Octavient
Octavient

Reputation: 603

SQL Server stored procedure with SCOPE_IDENTITY and ADO.NET parameterized query

My stored procedure should be returning the identity of my newly created row, but it isn't.

Stored procedure syntax (in a nutshell):

Alter Procedure dbo.udsp_insert_record
    @record_title VARCHAR(100)
AS
BEGIN
    INSERT INTO tbl_records (RecordTitle) VALUES (@record_title)
    SELECT SCOPE_IDENTITY()
END

This works as expected when I execute the procedure in SQL Server Management Studio -- a new row is inserted and its identity is returned. However, when I execute from my web app, using the code below, I get an error.

dim obj_command as New SqlCommand("udsp_insert_record",obj_connection)
obj_command.CommandType = CommandType.StoredProcedure
obj_command.Parameters.Add("@record_title",SqlDbType.VarChar).Value = "just testing"
dim n_new_record_id as Integer = obj_command.ExecuteScalar()
obj_command.Dispose()  'etc., etc.

When this runs, I get the error

Input string was not in a correct format

on the line with the ExecuteScalar command.

My interpretation is that SQL Server is not returning an integer value -- it must be returning an object, or a DBNull, or something.

But why is this? The INSERT and SELECT SCOPE_IDENTITY() are happening in the same scope, so the identity value SQL Server hands back must be valid (as borne out by my testing via SQL Server Management Studio).

I thought I read somewhere that when you use parameterized queries with ADO.NET that talk to SQL Server stored procedures, the scope is not what you expect it to be, because it actually uses another stored procedure to execute the one you asked for. Any merit to this?

Any ideas?

EDIT: Using SELECT CAST(SCOPE_IDENTITY() AS INT) doesn't work either. Also: I get this error even if I'm not assigning the result of the ExecuteScalar to an Integer value--I get the same error even if I simply do response.write obj_command.ExecuteScalar()

Upvotes: 1

Views: 1188

Answers (2)

Wiktor Zychla
Wiktor Zychla

Reputation: 48230

I would try to change the stored procedure a little bit:

Alter Procedure dbo.udsp_insert_record
  @record_title VARCHAR(100)
AS
BEGIN
  INSERT INTO tbl_records (RecordTitle) VALUES (@record_title)
  SELECT CAST(SCOPE_IDENTITY() AS INT)
END

From what I remember, the SCOPE_IDENTITY() is of decimal type so that you can't so easily convert it to int. You do it either on the server (as I propose) or at the client, by casting the ExecuteScalar to Decimal.

Upvotes: 1

Ankush Jain
Ankush Jain

Reputation: 7049

ExecuteScaler always returns an object datatype. so to retrieve integer value from that you have to convert it like this.

eg.

object id=cmd.ExecuteScaler(...........
if(id != null)
{   
 int newId=Convert.ToInt32(id);
}

Upvotes: 0

Related Questions