Help using SCOPE_IDENTITY()

[Diego F.]

Hi. I need to use scope_identity() but I can't make it work. Can you put a
piece of code showing how to use it, please?

Regards,

Diego F.

 

[Miha Markic [MVP C#]]

Diego,

Read this help topic:
Retrieving Identity or Autonumber Values
http://msdn.microsoft.com/library/d...cpconretrievingidentityorautonumbervalues.asp

 

[Diego F.]

Thank you. I'm trying that, but I get an exception:

An unhandled exception of type 'System.NullReferenceException' occurred in
Test.exe

Additional information: Object reference not set to an instance of an
object.

That's what I've done:

CREATE PROCEDURE InsertName
@name char(10),
@value int OUT
AS
INSERT INTO table (name) VALUES (@name)
SET @value = SCOPE_IDENTITY()
GO

Y el código C#

string cadCon = "data source=serverbd\\sql; initial catalog=tempdb;
integrated security=sspi";
SqlConnection con = new SqlConnection(cadCon);

SqlCommand cmd = new SqlCommand("InsertName", con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@name", SqlDbType.Char, 10, "name");
cmd.Parameters["@name"].Value = "test_name";
cmd.Parameters.Add("@value", SqlDbType.Int, 0, "id");
cmd.Parameters["@value"].Direction = ParameterDirection.Output;

int res = 0;

con.Open();
res = (int)cmd.ExecuteScalar();
con.Close();

Do you see the error?

Regards,

Diego F.

Diego,

Read this help topic:
Retrieving Identity or Autonumber Values
http://msdn.microsoft.com/library/d...cpconretrievingidentityorautonumbervalues.asp

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi. I need to use scope_identity() but I can't make it work. Can you put a
piece of code showing how to use it, please?

Regards,

Diego F.

 

[Miha Markic [MVP C#]]

Hi Diego,

Thank you. I'm trying that, but I get an exception:

An unhandled exception of type 'System.NullReferenceException' occurred in
Test.exe

Additional information: Object reference not set to an instance of an
object.

That's what I've done:

CREATE PROCEDURE InsertName
@name char(10),
@value int OUT
AS
INSERT INTO table (name) VALUES (@name)
SET @value = SCOPE_IDENTITY()
GO

Y el código C#

string cadCon = "data source=serverbd\\sql; initial catalog=tempdb;
integrated security=sspi";
SqlConnection con = new SqlConnection(cadCon);

SqlCommand cmd = new SqlCommand("InsertName", con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@name", SqlDbType.Char, 10, "name");
cmd.Parameters["@name"].Value = "test_name";
cmd.Parameters.Add("@value", SqlDbType.Int, 0, "id");
cmd.Parameters["@value"].Direction = ParameterDirection.Output;

int res = 0;

con.Open();
res = (int)cmd.ExecuteScalar();
con.Close();

Do you see the error?

Stupid question:
Are you sure that your database table has an autoinc primary key?
Rather then ExecuteScalar use ExecutNonQuery.

 

[Diego F.]

The table is:

CREATE TABLE [dbo].

(
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE Modern_Spanish_CI_AS NOT NULL
) ON [PRIMARY]
GO

If I use ExecuteNonQuery() it doesn't break, but the value returned is 1:
the number of rows affected, so I need ExecuteScalar().

Regards,

Diego F.

Hi Diego,

Thank you. I'm trying that, but I get an exception:

An unhandled exception of type 'System.NullReferenceException' occurred in
Test.exe

Additional information: Object reference not set to an instance of an
object.

That's what I've done:

CREATE PROCEDURE InsertName
@name char(10),
@value int OUT
AS
INSERT INTO table (name) VALUES (@name)
SET @value = SCOPE_IDENTITY()
GO

Y el código C#

string cadCon = "data source=serverbd\\sql; initial catalog=tempdb;
integrated security=sspi";
SqlConnection con = new SqlConnection(cadCon);

SqlCommand cmd = new SqlCommand("InsertName", con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@name", SqlDbType.Char, 10, "name");
cmd.Parameters["@name"].Value = "test_name";
cmd.Parameters.Add("@value", SqlDbType.Int, 0, "id");
cmd.Parameters["@value"].Direction = ParameterDirection.Output;

int res = 0;

con.Open();
res = (int)cmd.ExecuteScalar();
con.Close();

Do you see the error?

Stupid question:
Are you sure that your database table has an autoinc primary key?
Rather then ExecuteScalar use ExecutNonQuery.