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("R ETURN_VALUE",Sq lDbType.Int);
prmEntryID.Dire ction = ParameterDirect ion.ReturnValue ;

cmdEntry = new SqlCommand("kb_ Add_Entry", conn);
cmdEntry.Comman dType = CommandType.Sto redProcedure;
cmdEntry.Parame ters.Add ("@kb_entry_des cription", txtDescription. Text);
cmdEntry.Parame ters.Add ("@kb_entry_res olution", txtResolution.T ext);
cmdEntry.Parame ters.Add ("@kb_entry_mai ncategory",
ddlCategories.S electedItem.Val ue);
cmdEntry.Parame ters.Add ("@kb_entry_ent eredby", "Mark");
cmdEntry.Parame ters.Add ("@kb_entry_sou rce", txtSource.Text) ;
cmdEntry.Parame ters.Add ("@kb_entry_key words", txtKeywords.Tex t);
cmdEntry.Parame ters.Add (prmEntryID);
conn.Open();
cmdEntry.Execut eNonQuery();
string strEntryID =
cmdEntry.Parame ters["RETURN_VAL UE"].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.ExecutyScal ar() );

cheers,

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

"Mark" <an*******@devd ex.com> wrote in message
news:ex******** ******@tk2msftn gp13.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("R ETURN_VALUE",Sq lDbType.Int);
prmEntryID.Dire ction = ParameterDirect ion.ReturnValue ;

cmdEntry = new SqlCommand("kb_ Add_Entry", conn);
cmdEntry.Comman dType = CommandType.Sto redProcedure;
cmdEntry.Parame ters.Add ("@kb_entry_des cription", txtDescription. Text);
cmdEntry.Parame ters.Add ("@kb_entry_res olution", txtResolution.T ext);
cmdEntry.Parame ters.Add ("@kb_entry_mai ncategory",
ddlCategories.S electedItem.Val ue);
cmdEntry.Parame ters.Add ("@kb_entry_ent eredby", "Mark");
cmdEntry.Parame ters.Add ("@kb_entry_sou rce", txtSource.Text) ;
cmdEntry.Parame ters.Add ("@kb_entry_key words", txtKeywords.Tex t);
cmdEntry.Parame ters.Add (prmEntryID);
conn.Open();
cmdEntry.Execut eNonQuery();
string strEntryID =
cmdEntry.Parame ters["RETURN_VAL UE"].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.Execu teScalar()); 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.ExecutyScal ar();
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*******@devd ex.com> wrote in message
news:%2******** **********@TK2M SFTNGP09.phx.gb l...

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

Mark

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