Insert into sql table

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();
 
W

William \(Bill\) Vaughn

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

Bart Mermuys

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

 
G

Guest

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...
 
G

Guest

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...
 
W

William \(Bill\) Vaughn

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

insert stored procedure does not commit to the database More options 4
Error sql parameter SqlDbType.Float won't accept null value, AND/OR variable as DOUBLE won't accept 4
GUID from SQL Uniqueidentifier - Empty?! 1
GUID from SQL Uniqueidentifier -> Empty?! 1
Trouble opening a connection to SQL 2000 DB 5
Best way to Insert multiple records into a table? 1
SQL Server Insert Problem 1

Top