CODEWITHSUNDEEP
c#sql
amer
amer

Reputation: 623

How can I insert into two sql related tables?

I have two tables, one for storing person information and one for storing his phone numbers, tbl -persons is connected to tbl_phones table using forgin key.

I have one form to insert into two tables and this is the function 

 private void save()
    {
        SqlCommand cmd = new SqlCommand("insert into tbl_persons (name,address,fax,mobile,email,website) values (@name,@address,@fax,@mobile,@email,@website)" + "Select Scope_Identity()", conn);
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("@name", T_name.Text.Trim());
        cmd.Parameters.AddWithValue("@address", T_address.Text.Trim());
        cmd.Parameters.AddWithValue("@fax", T_fax.Text.Trim());
        cmd.Parameters.AddWithValue("@mobile", T_mobile.Text.Trim());
        cmd.Parameters.AddWithValue("@email", T_mobile.Text.Trim());
        cmd.Parameters.AddWithValue("@website", T_mobile.Text.Trim());

        int ID = Int32.Parse(cmd.ExecuteScalar().ToString());
        SqlCommand cmd2 = new SqlCommand("insert into tbl_phones (phone,person_id) values (@phone,@person)", conn);
        cmd2.CommandType = CommandType.Text;

        cmd2.Parameters.AddWithValue("@phone", T_phone.Text.Trim());
        cmd2.Parameters.AddWithValue("@person", ID);

        int val = cmd.ExecuteNonQuery();
        int val2 = cmd2.ExecuteNonQuery();
        if (val > 0)
        {
            MessageBox.Show("تم إدخال البيانات بنجاح"+ID, "تم", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign | MessageBoxOptions.RtlReading);
            resetFields();
            GetDate();
        }
    }

but the error is that the person info is inserted twice and the phone number is inserted one one time, what is the error ?

Upvotes: 2

Views: 1233

Answers (4)

AJC
AJC

Reputation: 1893

You are executing the first query twice:

int ID = Int32.Parse(cmd.ExecuteScalar().ToString());

and 

int val = cmd.ExecuteNonQuery();

Remove the second one.

Upvotes: 0

Icarus
Icarus

Reputation: 63966

The problem is that you are calling cmd.ExecuteNonQuery twice!

int ID = Int32.Parse(cmd.ExecuteScalar().ToString());

And then .... 

int val = cmd.ExecuteNonQuery();

Hence, inserting 2 records

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Looks like you're executing cmd twice.

Once here:

                      \/
int ID = Int32.Parse(cmd.ExecuteScalar().ToString());
                      /\

and once here:

           \/
int val = cmd.ExecuteNonQuery();
           /\

Upvotes: 3

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

You are executing the command in cmd twice. One is here:

int ID = Int32.Parse(cmd.ExecuteScalar().ToString());

And the other here:

int val = cmd.ExecuteNonQuery();

Both instructions (cmd.ExecuteScalar() and cmd.ExecuteNonQuery()) run the command. Make sure you only have one of them (probably the first one, but you are the owner of your logic, so that's your call).

Upvotes: 6

Related Questions