Insert into sql table

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

Guest

I am inserting a record into a sql table and get two (2) indentical records
each time. I am using a stored procedure. What am I doing to cause this to
happen. In the stored procedure I have the line 'SELECT SCOPE_IDENTITY()'.
The code that I use to execute the stored procedure is here:

try
{

conn = new SqlConnection("Server=icc-server;DataBase=kennel;Integrated
Security=SSPI");

if (cnKennel.State != ConnectionState.Open)
{
conn.Open();
}

SqlCommand cmd = new SqlCommand("InsertAnimal-info", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@owner_id_1", txtOid.Text));
cmd.Parameters.Add(new SqlParameter("@animal_tag_id_2", txtAnimalTag.Text));

cmd.Parameters.Add(new SqlParameter("@animal_implanted_id_3",
txtAnimalImpId.Text));
cmd.Parameters.Add(new SqlParameter("@sp_nu_4", cbSpNu.Text));
cmd.Parameters.Add(new SqlParameter("@pet_name_5",txtAnimalName.Text));
cmd.Parameters.Add(new SqlParameter("@pet_type_6", cbAnimalType.Text));
cmd.Parameters.Add(new SqlParameter("@pet_breed_7",cbAnimalBreed.Text));
cmd.Parameters.Add(new SqlParameter("@pet_sex_8", cbAnimalSex.Text));
cmd.Parameters.Add(new SqlParameter("@pet_weight_9", txtAnimalWeight.Text));
cmd.Parameters.Add(new SqlParameter("@pet_size_10", cbAnimalSize.Text));
cmd.Parameters.Add(new SqlParameter("@pet_color_11", txtAnimalColor.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_years_12",
txtAnimalAgeYears.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_months_13",
txtAnimalAgeMonths.Text));
cmd.Parameters.Add(new SqlParameter("@rate_14",
Convert.ToDecimal(cbRate.Text)));
cmd.Parameters.Add(new SqlParameter("@rate_type_15", cbRateType.Text));
cmd.Parameters.Add(new SqlParameter("@compatable_16", cbCompatable.Text));
cmd.Parameters.Add(new SqlParameter("@medical_condition_17",
txtAnimalMedicalCondition.Text));
cmd.Parameters.Add(new SqlParameter("@pet_location_18",
txtAnimalLocation.Text));
cmd.Parameters.Add(new SqlParameter("@pet_photo_19", pcAnimalPhoto.Text));

rdr = cmd.ExecuteReader();

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

rdr.Close();

IdentityValue = Convert.ToInt32(cmd.ExecuteScalar());
osave = IdentityValue.ToString();
 
1) Does the table have a primary key? If so, this will prevent dup rows.
2) Let's see the SP.

--
____________________________________
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.
__________________________________
 
Hi,

nbohana said:
I am inserting a record into a sql table and get two (2) indentical records
each time. I am using a stored procedure. What am I doing to cause this
to
happen. In the stored procedure I have the line 'SELECT SCOPE_IDENTITY()'.
The code that I use to execute the stored procedure is here:

try
{

conn = new SqlConnection("Server=icc-server;DataBase=kennel;Integrated
Security=SSPI");

if (cnKennel.State != ConnectionState.Open)
{
conn.Open();
}

SqlCommand cmd = new SqlCommand("InsertAnimal-info", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@owner_id_1", txtOid.Text));
cmd.Parameters.Add(new SqlParameter("@animal_tag_id_2",
txtAnimalTag.Text));

cmd.Parameters.Add(new SqlParameter("@animal_implanted_id_3",
txtAnimalImpId.Text));
cmd.Parameters.Add(new SqlParameter("@sp_nu_4", cbSpNu.Text));
cmd.Parameters.Add(new SqlParameter("@pet_name_5",txtAnimalName.Text));
cmd.Parameters.Add(new SqlParameter("@pet_type_6", cbAnimalType.Text));
cmd.Parameters.Add(new SqlParameter("@pet_breed_7",cbAnimalBreed.Text));
cmd.Parameters.Add(new SqlParameter("@pet_sex_8", cbAnimalSex.Text));
cmd.Parameters.Add(new SqlParameter("@pet_weight_9",
txtAnimalWeight.Text));
cmd.Parameters.Add(new SqlParameter("@pet_size_10", cbAnimalSize.Text));
cmd.Parameters.Add(new SqlParameter("@pet_color_11",
txtAnimalColor.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_years_12",
txtAnimalAgeYears.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_months_13",
txtAnimalAgeMonths.Text));
cmd.Parameters.Add(new SqlParameter("@rate_14",
Convert.ToDecimal(cbRate.Text)));
cmd.Parameters.Add(new SqlParameter("@rate_type_15", cbRateType.Text));
cmd.Parameters.Add(new SqlParameter("@compatable_16", cbCompatable.Text));
cmd.Parameters.Add(new SqlParameter("@medical_condition_17",
txtAnimalMedicalCondition.Text));
cmd.Parameters.Add(new SqlParameter("@pet_location_18",
txtAnimalLocation.Text));
cmd.Parameters.Add(new SqlParameter("@pet_photo_19", pcAnimalPhoto.Text));

rdr = cmd.ExecuteReader();

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

rdr.Close();

IdentityValue = Convert.ToInt32(cmd.ExecuteScalar());
osave = IdentityValue.ToString();
Click to expand...

I don't think you are supposed to call both ExecuteReader and ExecuteScalar.
If you used "SELECT SCOPE_IDENTITY()" in the sp then the row returned by
ExecuteReader/Read or the value returned by ExecuteScalar can give you the
pk, call one of them, not both.


hth,
Greetings

 
Yes the table has a primary key. I changed to code to elemnate the data
reader. The sp:

CREATE PROCEDURE [InsertOwner-info]
(@salutation_1 [varchar](5),
@owner_fname_2 [varchar](30),
@owner_lname_3 [varchar](30),
@owner_address_4 [varchar](30),
@owner_city_5 [varchar](30),
@owner_state_6 [char](2),
@owner_zipcode_7 [char](10),
@owner_home_phone_8 [char](15),
@owner_work_phone_9 [char](15),
@owner_cell_phone_10 [char](15),
@owner_emgr_phone_11 [char](15),
@owner_emgr_contact_12 [varchar](30),
@owner_email_13 [varchar](30),
@owner_fax_number_14 [char](15),
@owner_refered_by_15 [varchar](30),
@pets_16 [numeric],
@animal_id_17 [int])

AS INSERT INTO [kennel].[dbo].[owner-info]
( [salutation],
[owner-fname],
[owner-lname],
[owner-address],
[owner-city],
[owner-state],
[owner-zipcode],
[owner-home-phone],
[owner-work-phone],
[owner-cell-phone],
[owner-emgr-phone],
[owner-emgr-contact],
[owner-email],
[owner-fax-number],
[owner-refered-by],
[pets],
[animal-id])

VALUES
( @salutation_1,
@owner_fname_2,
@owner_lname_3,
@owner_address_4,
@owner_city_5,
@owner_state_6,
@owner_zipcode_7,
@owner_home_phone_8,
@owner_work_phone_9,
@owner_cell_phone_10,
@owner_emgr_phone_11,
@owner_emgr_contact_12,
@owner_email_13,
@owner_fax_number_14,
@owner_refered_by_15,
@pets_16,
@animal_id_17)

SELECT SCOPE_IDENTITY()
GO
--
Norm Bohana


William (Bill) Vaughn said:
1) Does the table have a primary key? If so, this will prevent dup rows.
2) Let's see the SP.

--
____________________________________
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.
__________________________________

nbohana said:
I am inserting a record into a sql table and get two (2) indentical records
each time. I am using a stored procedure. What am I doing to cause this
to
happen. In the stored procedure I have the line 'SELECT SCOPE_IDENTITY()'.
The code that I use to execute the stored procedure is here:

try
{

conn = new SqlConnection("Server=icc-server;DataBase=kennel;Integrated
Security=SSPI");

if (cnKennel.State != ConnectionState.Open)
{
conn.Open();
}

SqlCommand cmd = new SqlCommand("InsertAnimal-info", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@owner_id_1", txtOid.Text));
cmd.Parameters.Add(new SqlParameter("@animal_tag_id_2",
txtAnimalTag.Text));

cmd.Parameters.Add(new SqlParameter("@animal_implanted_id_3",
txtAnimalImpId.Text));
cmd.Parameters.Add(new SqlParameter("@sp_nu_4", cbSpNu.Text));
cmd.Parameters.Add(new SqlParameter("@pet_name_5",txtAnimalName.Text));
cmd.Parameters.Add(new SqlParameter("@pet_type_6", cbAnimalType.Text));
cmd.Parameters.Add(new SqlParameter("@pet_breed_7",cbAnimalBreed.Text));
cmd.Parameters.Add(new SqlParameter("@pet_sex_8", cbAnimalSex.Text));
cmd.Parameters.Add(new SqlParameter("@pet_weight_9",
txtAnimalWeight.Text));
cmd.Parameters.Add(new SqlParameter("@pet_size_10", cbAnimalSize.Text));
cmd.Parameters.Add(new SqlParameter("@pet_color_11",
txtAnimalColor.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_years_12",
txtAnimalAgeYears.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_months_13",
txtAnimalAgeMonths.Text));
cmd.Parameters.Add(new SqlParameter("@rate_14",
Convert.ToDecimal(cbRate.Text)));
cmd.Parameters.Add(new SqlParameter("@rate_type_15", cbRateType.Text));
cmd.Parameters.Add(new SqlParameter("@compatable_16", cbCompatable.Text));
cmd.Parameters.Add(new SqlParameter("@medical_condition_17",
txtAnimalMedicalCondition.Text));
cmd.Parameters.Add(new SqlParameter("@pet_location_18",
txtAnimalLocation.Text));
cmd.Parameters.Add(new SqlParameter("@pet_photo_19", pcAnimalPhoto.Text));

rdr = cmd.ExecuteReader();

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

rdr.Close();

IdentityValue = Convert.ToInt32(cmd.ExecuteScalar());
osave = IdentityValue.ToString();
Click to expand...
Click to expand...
 
I changed the code to look this (see the bottom part) takeing out the data
reader code. I stell get two writes to the table?? I will include the sp.
here:

CREATE PROCEDURE [InsertOwner-info]
(@salutation_1 [varchar](5),
@owner_fname_2 [varchar](30),
@owner_lname_3 [varchar](30),
@owner_address_4 [varchar](30),
@owner_city_5 [varchar](30),
@owner_state_6 [char](2),
@owner_zipcode_7 [char](10),
@owner_home_phone_8 [char](15),
@owner_work_phone_9 [char](15),
@owner_cell_phone_10 [char](15),
@owner_emgr_phone_11 [char](15),
@owner_emgr_contact_12 [varchar](30),
@owner_email_13 [varchar](30),
@owner_fax_number_14 [char](15),
@owner_refered_by_15 [varchar](30),
@pets_16 [numeric],
@animal_id_17 [int])

AS INSERT INTO [kennel].[dbo].[owner-info]
( [salutation],
[owner-fname],
[owner-lname],
[owner-address],
[owner-city],
[owner-state],
[owner-zipcode],
[owner-home-phone],
[owner-work-phone],
[owner-cell-phone],
[owner-emgr-phone],
[owner-emgr-contact],
[owner-email],
[owner-fax-number],
[owner-refered-by],
[pets],
[animal-id])

VALUES
( @salutation_1,
@owner_fname_2,
@owner_lname_3,
@owner_address_4,
@owner_city_5,
@owner_state_6,
@owner_zipcode_7,
@owner_home_phone_8,
@owner_work_phone_9,
@owner_cell_phone_10,
@owner_emgr_phone_11,
@owner_emgr_contact_12,
@owner_email_13,
@owner_fax_number_14,
@owner_refered_by_15,
@pets_16,
@animal_id_17)

SELECT SCOPE_IDENTITY()
GO
--
Norm Bohana


Bart Mermuys said:
Hi,

nbohana said:
I am inserting a record into a sql table and get two (2) indentical records
each time. I am using a stored procedure. What am I doing to cause this
to
happen. In the stored procedure I have the line 'SELECT SCOPE_IDENTITY()'.
The code that I use to execute the stored procedure is here:

try
{

conn = new SqlConnection("Server=icc-server;DataBase=kennel;Integrated
Security=SSPI");

SqlCommand cmd = new SqlCommand("InsertAnimal-info", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@owner_id_1", txtOid.Text));
cmd.Parameters.Add(new SqlParameter("@animal_tag_id_2",
txtAnimalTag.Text));

cmd.Parameters.Add(new SqlParameter("@animal_implanted_id_3",
txtAnimalImpId.Text));
cmd.Parameters.Add(new SqlParameter("@sp_nu_4", cbSpNu.Text));
cmd.Parameters.Add(new SqlParameter("@pet_name_5",txtAnimalName.Text));
cmd.Parameters.Add(new SqlParameter("@pet_type_6", cbAnimalType.Text));
cmd.Parameters.Add(new SqlParameter("@pet_breed_7",cbAnimalBreed.Text));
cmd.Parameters.Add(new SqlParameter("@pet_sex_8", cbAnimalSex.Text));
cmd.Parameters.Add(new SqlParameter("@pet_weight_9",
txtAnimalWeight.Text));
cmd.Parameters.Add(new SqlParameter("@pet_size_10", cbAnimalSize.Text));
cmd.Parameters.Add(new SqlParameter("@pet_color_11",
txtAnimalColor.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_years_12",
txtAnimalAgeYears.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_months_13",
txtAnimalAgeMonths.Text));
cmd.Parameters.Add(new SqlParameter("@rate_14",
Convert.ToDecimal(cbRate.Text)));
cmd.Parameters.Add(new SqlParameter("@rate_type_15", cbRateType.Text));
cmd.Parameters.Add(new SqlParameter("@compatable_16", cbCompatable.Text));
cmd.Parameters.Add(new SqlParameter("@medical_condition_17",
txtAnimalMedicalCondition.Text));
cmd.Parameters.Add(new SqlParameter("@pet_location_18",
txtAnimalLocation.Text));
cmd.Parameters.Add(new SqlParameter("@pet_photo_19", pcAnimalPhoto.Text));

MessageBox.Show("Insert Complete. [animal-info] " + conn +
" Successful.","");
if (cnKennel.State != ConnectionState.Open)
{
conn.Open();
IdentityValue = Convert.ToInt32(cmd.ExecuteScalar());
}
osave = IdentityValue.ToString();
conn.Dispose();
Click to expand...

I don't think you are supposed to call both ExecuteReader and ExecuteScalar.
If you used "SELECT SCOPE_IDENTITY()" in the sp then the row returned by
ExecuteReader/Read or the value returned by ExecuteScalar can give you the
pk, call one of them, not both.


hth,
Greetings
Click to expand...
 
What makes you think there are two writes to the table? If you have a
primary key SQL Server won't let two identical rows be added. However, since
you're using an Identity, it will generate a new ID for each row. If you
don't execute the SP twice, it won't add two rows. Does the profiler say
it's getting executed twice? Your code shown below is only calling
ExecuteScalar once. The messagebox seems out of place though--before the
ExecuteScalar.

--
____________________________________
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.
__________________________________

nbohana said:
I changed the code to look this (see the bottom part) takeing out the data
reader code. I stell get two writes to the table?? I will include the
sp.
here:

CREATE PROCEDURE [InsertOwner-info]
(@salutation_1 [varchar](5),
@owner_fname_2 [varchar](30),
@owner_lname_3 [varchar](30),
@owner_address_4 [varchar](30),
@owner_city_5 [varchar](30),
@owner_state_6 [char](2),
@owner_zipcode_7 [char](10),
@owner_home_phone_8 [char](15),
@owner_work_phone_9 [char](15),
@owner_cell_phone_10 [char](15),
@owner_emgr_phone_11 [char](15),
@owner_emgr_contact_12 [varchar](30),
@owner_email_13 [varchar](30),
@owner_fax_number_14 [char](15),
@owner_refered_by_15 [varchar](30),
@pets_16 [numeric],
@animal_id_17 [int])

AS INSERT INTO [kennel].[dbo].[owner-info]
( [salutation],
[owner-fname],
[owner-lname],
[owner-address],
[owner-city],
[owner-state],
[owner-zipcode],
[owner-home-phone],
[owner-work-phone],
[owner-cell-phone],
[owner-emgr-phone],
[owner-emgr-contact],
[owner-email],
[owner-fax-number],
[owner-refered-by],
[pets],
[animal-id])

VALUES
( @salutation_1,
@owner_fname_2,
@owner_lname_3,
@owner_address_4,
@owner_city_5,
@owner_state_6,
@owner_zipcode_7,
@owner_home_phone_8,
@owner_work_phone_9,
@owner_cell_phone_10,
@owner_emgr_phone_11,
@owner_emgr_contact_12,
@owner_email_13,
@owner_fax_number_14,
@owner_refered_by_15,
@pets_16,
@animal_id_17)

SELECT SCOPE_IDENTITY()
GO
--
Norm Bohana


Bart Mermuys said:
Hi,

nbohana said:
I am inserting a record into a sql table and get two (2) indentical
records
each time. I am using a stored procedure. What am I doing to cause
this
to
happen. In the stored procedure I have the line 'SELECT
SCOPE_IDENTITY()'.
The code that I use to execute the stored procedure is here:

try
{

conn = new SqlConnection("Server=icc-server;DataBase=kennel;Integrated
Security=SSPI");

SqlCommand cmd = new SqlCommand("InsertAnimal-info", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@owner_id_1", txtOid.Text));
cmd.Parameters.Add(new SqlParameter("@animal_tag_id_2",
txtAnimalTag.Text));

cmd.Parameters.Add(new SqlParameter("@animal_implanted_id_3",
txtAnimalImpId.Text));
cmd.Parameters.Add(new SqlParameter("@sp_nu_4", cbSpNu.Text));
cmd.Parameters.Add(new SqlParameter("@pet_name_5",txtAnimalName.Text));
cmd.Parameters.Add(new SqlParameter("@pet_type_6", cbAnimalType.Text));
cmd.Parameters.Add(new
SqlParameter("@pet_breed_7",cbAnimalBreed.Text));
cmd.Parameters.Add(new SqlParameter("@pet_sex_8", cbAnimalSex.Text));
cmd.Parameters.Add(new SqlParameter("@pet_weight_9",
txtAnimalWeight.Text));
cmd.Parameters.Add(new SqlParameter("@pet_size_10",
cbAnimalSize.Text));
cmd.Parameters.Add(new SqlParameter("@pet_color_11",
txtAnimalColor.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_years_12",
txtAnimalAgeYears.Text));
cmd.Parameters.Add(new SqlParameter("@pet_age_months_13",
txtAnimalAgeMonths.Text));
cmd.Parameters.Add(new SqlParameter("@rate_14",
Convert.ToDecimal(cbRate.Text)));
cmd.Parameters.Add(new SqlParameter("@rate_type_15", cbRateType.Text));
cmd.Parameters.Add(new SqlParameter("@compatable_16",
cbCompatable.Text));
cmd.Parameters.Add(new SqlParameter("@medical_condition_17",
txtAnimalMedicalCondition.Text));
cmd.Parameters.Add(new SqlParameter("@pet_location_18",
txtAnimalLocation.Text));
cmd.Parameters.Add(new SqlParameter("@pet_photo_19",
pcAnimalPhoto.Text));

Why show a message box? You haven't executed anything yet.
MessageBox.Show("Insert Complete. [animal-info] " + conn +
" Successful.","");
if (cnKennel.State != ConnectionState.Open)
{
conn.Open();
IdentityValue = Convert.ToInt32(cmd.ExecuteScalar());
}
osave = IdentityValue.ToString();
conn.Dispose();
Click to expand...

I don't think you are supposed to call both ExecuteReader and
ExecuteScalar.
If you used "SELECT SCOPE_IDENTITY()" in the sp then the row returned by
ExecuteReader/Read or the value returned by ExecuteScalar can give you
the
pk, call one of them, not both.


hth,
Greetings
Click to expand...
Click to expand...
 
Back
Top