Retrieve new row identity in C#

[Mark]

Hi,
I'm having a bit of difficulty in getting the identity of a new row
using the SCOPE_IDENTITY() function in SQLServer 2000.

I've added the following code: 'set @LastID = SCOPE_IDENTITY()' to the
end of the select statement in the Stored Procedure, and have the
following code in the aspx.cs file:

SqlCommand cmdEntry;
SqlParameter prmEntryID = new
SqlParameter("RETURN_VALUE",SqlDbType.Int);
prmEntryID.Direction = ParameterDirection.ReturnValue;

cmdEntry = new SqlCommand("kb_Add_Entry", conn);
cmdEntry.CommandType = CommandType.StoredProcedure;
cmdEntry.Parameters.Add ("@kb_entry_description", txtDescription.Text);
cmdEntry.Parameters.Add ("@kb_entry_resolution", txtResolution.Text);
cmdEntry.Parameters.Add ("@kb_entry_maincategory",
ddlCategories.SelectedItem.Value);
cmdEntry.Parameters.Add ("@kb_entry_enteredby", "Mark");
cmdEntry.Parameters.Add ("@kb_entry_source", txtSource.Text);
cmdEntry.Parameters.Add ("@kb_entry_keywords", txtKeywords.Text);
cmdEntry.Parameters.Add (prmEntryID);
conn.Open();
cmdEntry.ExecuteNonQuery();
string strEntryID =
cmdEntry.Parameters["RETURN_VALUE"].Value.ToString();
conn.Close();

However, when I run the code, I get the following error: 'Error
converting data type nvarchar to int.'

Can anyone help?

Thanks in advance,
Mark

*** Sent via Developersdex http://www.developersdex.com ***

[Ignacio Machin \( .NET/ C# MVP \)]

hi,

if you are running an stored procedure then jusr return it :

in the SP:
insert into XXX values YYY
SELECT @@IDENTITY

in the CS code:
int id = Convert.toInt32( cmd.ExecutyScalar() );

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Mark" <an*******@devdex.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl...

Hi,
I'm having a bit of difficulty in getting the identity of a new row
using the SCOPE_IDENTITY() function in SQLServer 2000.

I've added the following code: 'set @LastID = SCOPE_IDENTITY()' to the
end of the select statement in the Stored Procedure, and have the
following code in the aspx.cs file:

SqlCommand cmdEntry;
SqlParameter prmEntryID = new
SqlParameter("RETURN_VALUE",SqlDbType.Int);
prmEntryID.Direction = ParameterDirection.ReturnValue;

cmdEntry = new SqlCommand("kb_Add_Entry", conn);
cmdEntry.CommandType = CommandType.StoredProcedure;
cmdEntry.Parameters.Add ("@kb_entry_description", txtDescription.Text);
cmdEntry.Parameters.Add ("@kb_entry_resolution", txtResolution.Text);
cmdEntry.Parameters.Add ("@kb_entry_maincategory",
ddlCategories.SelectedItem.Value);
cmdEntry.Parameters.Add ("@kb_entry_enteredby", "Mark");
cmdEntry.Parameters.Add ("@kb_entry_source", txtSource.Text);
cmdEntry.Parameters.Add ("@kb_entry_keywords", txtKeywords.Text);
cmdEntry.Parameters.Add (prmEntryID);
conn.Open();
cmdEntry.ExecuteNonQuery();
string strEntryID =
cmdEntry.Parameters["RETURN_VALUE"].Value.ToString();
conn.Close();

However, when I run the code, I get the following error: 'Error
converting data type nvarchar to int.'

Can anyone help?

Thanks in advance,
Mark

*** Sent via Developersdex http://www.developersdex.com ***

[Mark]

Hi,
Thanks for replying. Unfortunately I'm still getting the same error
message, this time on the intEntryID =
Convert.ToInt32(cmdEntry.ExecuteScalar()); line...

Mark

*** Sent via Developersdex http://www.developersdex.com ***

[Ignacio Machin \( .NET/ C# MVP \)]

hi

well I assume that the PK was an integer

do tihs:
object o = cmd.ExecutyScalar();
and put a breakpoint after that line, you will see the type of o , and you
could use the correct method/cast
cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Mark" <an*******@devdex.com> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...

Hi,
Thanks for replying. Unfortunately I'm still getting the same error
message, this time on the intEntryID =
Convert.ToInt32(cmdEntry.ExecuteScalar()); line...

Mark

*** Sent via Developersdex http://www.developersdex.com ***