@@ Identity

[Guest]

I have a stored procedure that insert data into a sql table. I need to get
the record identity for another process. When I execute the program I get
this message " System.FormException: Input string was not in a correct
format". The line with *** is where the proglem crashed, Please tell me what
I doing wrong??

SqlCommand cmd = new SqlCommand("InsertOwner-info", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@salutation_1", txtSal.Text));
cmd.Parameters.Add(new SqlParameter("@owner_fname_2", txtFname.Text));
cmd.Parameters.Add(new SqlParameter("@owner_lname_3", txtLname.Text));
cmd.Parameters.Add(new SqlParameter("@owner_address_4", tAddress.Text));
cmd.Parameters.Add(new SqlParameter("@owner_city_5", txtCity.Text));
cmd.Parameters.Add(new SqlParameter("@owner_state_6", txtState.Text));
cmd.Parameters.Add(new SqlParameter("@owner_zipcode_7", txtZcode.Text));
cmd.Parameters.Add(new SqlParameter("@owner_home_phone_8",
txtHphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_work_phone_9", txtWphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_cell_phone_10", txtCphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_emgr_phone_11", txtEphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_emgr_contact_12",
txtEcontact.Text));
cmd.Parameters.Add(new SqlParameter("@owner_email_13", txtEmail.Text));
cmd.Parameters.Add(new SqlParameter("@owner_fax_number_14", txtFphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_refered_by_15",
txtRefered.Text));
cmd.Parameters.Add(new SqlParameter("@pets_16", txtNpets.Text));
cmd.Parameters.Add(new SqlParameter("@animal_id_17", txtOnum.Text));

rdr = cmd.ExecuteReader();

if (rdr.Read())
{
MessageBox.Show("Update Complete. [animal-info] " + conn +
" Successful.","");
}

rdr.Close();

cmdGetIdentity.CommandText = "SELECT @@IDENTITY";
cmdGetIdentity.Connection = cnKennel;
*** rsIdentity = int.Parse(cmdGetIdentity.ExecuteScalar().ToString()); ***
txtOnum.Text = System.Convert.ToString(rsIdentity);

cnKennel.Close();

 

[Marina]

Whatever is being returned by SELECT @@IDENTITY, it is not something .NET
can parse into an integer.

 

[WJ]

Modify your stored procedure to return an integer. Example

create procedure someSproc
@your input fields here
,@rsIdentity int out as

...Insert data here...

return @@IDENTITY

GO

This will properly return your @@Identity number each time a new record is
successfully added to your DB. Use DAAB, it is better.

John

 

[Sahil Malik [MVP]]

Why are you using ExecuteReader to Insert rows? That is the first mistake.
Change it to executenonquery, and batch the select @@Identity and your
problem should go away.

 

[William \(Bill\) Vaughn]

But stop using @@Identity. It's dangerous and won't fail until you least
expect it. Use Scope_Identity() instead.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Why are you using ExecuteReader to Insert rows? That is the first mistake.
Change it to executenonquery, and batch the select @@Identity and your
problem should go away.


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

I have a stored procedure that insert data into a sql table. I need to
get
the record identity for another process. When I execute the program I
get
this message " System.FormException: Input string was not in a correct
format". The line with *** is where the proglem crashed, Please tell me
what
I doing wrong??

SqlCommand cmd = new SqlCommand("InsertOwner-info", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@salutation_1", txtSal.Text));
cmd.Parameters.Add(new SqlParameter("@owner_fname_2", txtFname.Text));
cmd.Parameters.Add(new SqlParameter("@owner_lname_3", txtLname.Text));
cmd.Parameters.Add(new SqlParameter("@owner_address_4", tAddress.Text));
cmd.Parameters.Add(new SqlParameter("@owner_city_5", txtCity.Text));
cmd.Parameters.Add(new SqlParameter("@owner_state_6", txtState.Text));
cmd.Parameters.Add(new SqlParameter("@owner_zipcode_7", txtZcode.Text));
cmd.Parameters.Add(new SqlParameter("@owner_home_phone_8",
txtHphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_work_phone_9",
txtWphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_cell_phone_10",
txtCphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_emgr_phone_11",
txtEphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_emgr_contact_12",
txtEcontact.Text));
cmd.Parameters.Add(new SqlParameter("@owner_email_13", txtEmail.Text));
cmd.Parameters.Add(new SqlParameter("@owner_fax_number_14",
txtFphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_refered_by_15",
txtRefered.Text));
cmd.Parameters.Add(new SqlParameter("@pets_16", txtNpets.Text));
cmd.Parameters.Add(new SqlParameter("@animal_id_17", txtOnum.Text));

rdr = cmd.ExecuteReader();

if (rdr.Read())
{
MessageBox.Show("Update Complete. [animal-info] " + conn +
" Successful.","");
}

rdr.Close();

cmdGetIdentity.CommandText = "SELECT @@IDENTITY";
cmdGetIdentity.Connection = cnKennel;
*** rsIdentity = int.Parse(cmdGetIdentity.ExecuteScalar().ToString());
***
txtOnum.Text = System.Convert.ToString(rsIdentity);

cnKennel.Close();

 

[Sahil Malik [MVP]]

Thats true. There are however very few instances where scope_identity won't
work and you have to use @@identity, but yeah in most cases you should use
scope_identity instead of @@identity.


- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

But stop using @@Identity. It's dangerous and won't fail until you least
expect it. Use Scope_Identity() instead.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Why are you using ExecuteReader to Insert rows? That is the first mistake.
Change it to executenonquery, and batch the select @@Identity and your
problem should go away.


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------

--

I have a stored procedure that insert data into a sql table. I need to
get
the record identity for another process. When I execute the program I
get
this message " System.FormException: Input string was not in a correct
format". The line with *** is where the proglem crashed, Please tell me
what
I doing wrong??

SqlCommand cmd = new SqlCommand("InsertOwner-info", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@salutation_1", txtSal.Text));
cmd.Parameters.Add(new SqlParameter("@owner_fname_2", txtFname.Text));
cmd.Parameters.Add(new SqlParameter("@owner_lname_3", txtLname.Text));
cmd.Parameters.Add(new SqlParameter("@owner_address_4", tAddress.Text));
cmd.Parameters.Add(new SqlParameter("@owner_city_5", txtCity.Text));
cmd.Parameters.Add(new SqlParameter("@owner_state_6", txtState.Text));
cmd.Parameters.Add(new SqlParameter("@owner_zipcode_7", txtZcode.Text));
cmd.Parameters.Add(new SqlParameter("@owner_home_phone_8",
txtHphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_work_phone_9",
txtWphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_cell_phone_10",
txtCphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_emgr_phone_11",
txtEphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_emgr_contact_12",
txtEcontact.Text));
cmd.Parameters.Add(new SqlParameter("@owner_email_13", txtEmail.Text));
cmd.Parameters.Add(new SqlParameter("@owner_fax_number_14",
txtFphone.Text));
cmd.Parameters.Add(new SqlParameter("@owner_refered_by_15",
txtRefered.Text));
cmd.Parameters.Add(new SqlParameter("@pets_16", txtNpets.Text));
cmd.Parameters.Add(new SqlParameter("@animal_id_17", txtOnum.Text));

rdr = cmd.ExecuteReader();

if (rdr.Read())
{
MessageBox.Show("Update Complete. [animal-info] " + conn +
" Successful.","");
}

rdr.Close();

cmdGetIdentity.CommandText = "SELECT @@IDENTITY";
cmdGetIdentity.Connection = cnKennel;
*** rsIdentity = int.Parse(cmdGetIdentity.ExecuteScalar().ToString());
***
txtOnum.Text = System.Convert.ToString(rsIdentity);

cnKennel.Close();

 

[Otis Mukinfus]

But stop using @@Identity. It's dangerous and won't fail until you least
expect it. Use Scope_Identity() instead.

Right on Bill
Otis