CODEWITHSUNDEEP
c#sqlado.net
Ticherhaz FreePalestine
Ticherhaz FreePalestine

Reputation: 2377

C# Insert Value Foreign Key

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.";
            }

Upvotes: 0

Views: 2364

Answers (5)

Parvez
Parvez

Reputation: 187

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

Upvotes: 1

Hüseyin Burak Karadag
Hüseyin Burak Karadag

Reputation: 1888

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

Upvotes: 1

Mangesh Auti
Mangesh Auti

Reputation: 1153

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

                    }

Upvotes: 0

go..
go..

Reputation: 948

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

Upvotes: 0

Marco
Marco

Reputation: 11

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

Upvotes: 0

Related Questions