C# Insert Value Foreign Key

Question

I have 2 tables which are tableCustomerLogin and tableCustomerRegister. There is Foreign Key for the tableCustomerLogin i.e. cust_id.

In the tableCustomerLogin, I have tableCustomerLogin

    cust_login_id
    cust_id
    cust_email
    cust_username
    cust_password

and for tableCustomerRegister,

    tableCustomerRegister
    cust_id
    cust_fullname
    cust_username
    cust_email
    cust_password
    cust_mobile_number
    cust_image
    cust_address1
    cust_address2
    cust_city
    cust_postcode
    cust_create_acc_time

When customer register, the data will store in the tableCustomerRegister. How to make it register in the tableCustomerLogin?

string sql = @"INSERT INTO tableCustomerRegister VALUES (@cust_fullname, @cust_username, @cust_email, @password, @cust_mobile_phone, @cust_address1, @cust_address2, @cust_image, @cust_city, @cust_state, @cust_postcode, @cust_create_acc_time, @role, @enabled)";

            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@cust_fullname", txtFirstName.Text + " " + txtLastName.Text);
            cmd.Parameters.AddWithValue("@cust_username", txtUsername.Text);
            cmd.Parameters.AddWithValue("@cust_email", txtEmail.Text);
            cmd.Parameters.AddWithValue("@password", passwordhash);
            cmd.Parameters.AddWithValue("@cust_mobile_phone", txtMobilePhone.Text);
            cmd.Parameters.AddWithValue("@cust_address1", txtAddress1.Text);
            cmd.Parameters.AddWithValue("@cust_address2", txtAddress2.Text);
            cmd.Parameters.AddWithValue("@cust_image", txtProfilePicture.Text);
            cmd.Parameters.AddWithValue("@cust_city", ICityString());
            cmd.Parameters.AddWithValue("@cust_state", ddState.SelectedValue.ToString());
            cmd.Parameters.AddWithValue("@cust_postcode", txtPostcode.Text);
            cmd.Parameters.AddWithValue("@cust_create_acc_time", DateTime.Now);
            cmd.Parameters.AddWithValue("@role", "user");
            cmd.Parameters.AddWithValue("@enabled", enabled);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                lblStatus.Text = "Status: Data successfully saved.";
            }

try to add another "INSERT" clause for tableCustomerLogin and call it after inserting the values in the tableCustomerRegister. — Alexis Villar
– Alexis Villar, Commented Oct 24, 2018 at 5:58
First of all, why do you have to insert the same data in tableCustomerLogin table too? Here is the case where foreign key comes into picture and that you have already used it in your table. You can just map the tables by using foreign key relationships. — KamalaH
– KamalaH, Commented Oct 24, 2018 at 6:01
When user want to login, I will just call the tableCustomerLogin only. @KamalaHB — Ticherhaz FreePalestine
– Ticherhaz FreePalestine, Commented Oct 24, 2018 at 6:03

Parvez · Accepted Answer · 2018-11-07 03:15:45Z

well, first of all you need to change your query

string sql = @"INSERT INTO tableCustomerRegister  OUTPUT INSERTED.cust_id VALUES (@cust_fullname, @cust_username, @cust_email, @password, @cust_mobile_phone, @cust_address1, @cust_address2, @cust_image, @cust_city, @cust_state, @cust_postcode, @cust_create_acc_time, @role, @enabled)";
 SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@cust_fullname", txtFirstName.Text + " " + txtLastName.Text);
        cmd.Parameters.AddWithValue("@cust_username", txtUsername.Text);
//so on (all your parameters)
var custid  = (int)cmd.ExecuteScalar()

ExecuteScalar return int, in this case it will return cust_id as your query have OUTPUT INSERTED.cust_id. Now You have your inserted cust_id saved in tableCustomerRegister. Now all you need to do just write another query for save data into your tableCustomerLogin with foreign key cust_id. like this,

string Sql2 = "INSERT INTO tableCustomerLogin (column_names) VALUES (parameters values)";
SqlCommand cmd = new SqlCommand(sq2, conn);
cmd.Parameters.AddWithValue("@cust_id",custid);  //as foreign key
//all other Parameters

Hüseyin Burak Karadag · Accepted Answer · 2018-10-24 06:25:04Z

You can first insert a tableCustomerRegister record and then insert another data the tableCustomerLogin table. You would be better do this in the transaction block.

The other way , You can add a trigger to the tableCustomerLogin table.

CREATE TRIGGER trg_tableCustReg ON tableCustomerRegister
FOR INSERT
AS   
  /*
   *  if CustLoginID is a identity , no dont need to add  
   */ 
    INSERT INTO tableCustomerLogin 
            (cust_login_id, cust_id, cust_email, cust_username, cust_password)
        Select
            'CustLoginID', 
            cust_id , 
            cust_email, 
            cust_username, 
            user_password
            FROM inserted

go

Marco · Accepted Answer · 2018-10-24 06:09:29Z

0

Probably the best solution is to follow the DRY principle, Dont Repeat Yourself.

I think that you can store all information in a single table customer_table for example and then retrive the necessary data with a more simple logic from only this table.

Instead, if you want to stay on your actual data structure, simply add new insert statement after firstseparate by semicolon

how to insert data into multiple tables at once

answered Oct 24, 2018 at 6:09

Marco

114 bronze badges

Comments

go.. · Accepted Answer · 2018-10-24 06:18:11Z

there are two different way. you shold use SCOPE_IDENTITY

string sql = @"INSERT INTO tableCustomerRegister VALUES (@cust_fullname, @cust_username, @cust_email, @password, @cust_mobile_phone, @cust_address1, @cust_address2, @cust_image, @cust_city, @cust_state, @cust_postcode, @cust_create_acc_time, @role, @enabled) SELECT SCOPE_IDENTITY()";

SCOPE_IDENTITY Returns the last identity value inserted into an identity column in the same scope

var newId= cmd.ExecuteScaler();

newId created new Primary Key ID for you cust_id

you have this ID(cust_id) and you can register in the Login table.

INSERT INTO  tableCustomerLogin (cust_login_id,**cust_id**,cust_email,cust_username,cust_password)


cust_id = newId

Mangesh Auti · Accepted Answer · 2018-10-24 06:22:23Z

If Cust_id is auto-created in the tableCustomerRegister table, then you can save the same id in the tableCustomerLogin table (cust_id). then only your foreign Key relation work.

     try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        SqlCommand get_custid_cmd = new SqlCommand("select @@identity", conn);
                        int cust_id = Convert.ToInt32(get_custid_cmd.ExecuteScalar());
                        string sql_insert = @"INSERT INTO tableCustomerLogin VALUES (@cust_id, @cust_username, @cust_email, @password)";

                SqlCommand cmd_insert = new SqlCommand(sql_insert, conn);
                cmd_insert.Parameters.AddWithValue("@cust_id",cust_id);
                cmd_insert.Parameters.AddWithValue("@cust_username", txtUsername.Text);
                cmd_insert.Parameters.AddWithValue("@cust_email", txtEmail.Text);
                cmd_insert.Parameters.AddWithValue("@password", passwordhash);
                cmd_insert.ExecuteNonQuery();
                            lblStatus.Text = "Status: Data successfully saved.";

                    }

Collectives™ on Stack Overflow

C# Insert Value Foreign Key

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Linked

Related