@@ Identity

  • Thread starter Thread starter Guest
  • Start date Start date
G

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();
 
Whatever is being returned by SELECT @@IDENTITY, it is not something .NET
can parse into an integer.
 
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
 
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.
 
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.
__________________________________

Sahil Malik said:
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
----------------------------------------------------------------------------

nbohana said:
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();
Click to expand...
Click to expand...
 
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
----------------------------------------------------------------------------
---------------

William (Bill) Vaughn said:
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.
__________________________________

Sahil Malik said:
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
--------------------------------------------------------------------------
Click to expand...
--
nbohana said:
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();
Click to expand...
Click to expand...
Click to expand...
 
Back
Top