How to Insert values into two tables?

Question

I have a medicalcentre and windowsadmin table which have relationship with each other using mcID. I have 4 inputs in the form. two of the inputs which is the mcType and mcCentre values that are entered should insert to medicalcentre table and two of the other two inputs which is winUsername and winPassword values that are entered should go to the windowsadmin table.

That means what I want is, I type in vales for mcType, mcCentre, winUsername and winPassword. When click register, the mcCentre and mcType will be inserted into medicalcentre table and auto increment one mcID, that mcID will be inserted into window admin table along with the winUsername and winPassword that are entered. How to do it? For my codes below. I only know how to insert mcType and mcCentre which is in the medicalcentre table only.

My Register MedicalCentre and WindowsAdmin account form

Radio button names - Hospital : rbHopistal

Radio button names - Clinic : rbClinic

textbox name - mcCentre : txtmcCentre

textbox name - winUsername : txtwinUsername

textbox name - winPassword : txtwinPassword

MEDICAL CENTRE AND WINDOWSADMIN table

Error Trying to follow @Sergey Berezovskiy code. Must declare the scalar variable "@mcID".

My tables Relationship

    private int AddMedicalCentreRecord()
            {
                int result = 0;

                string strConnectionString = ConfigurationManager.ConnectionStrings["SACPConnection"].ConnectionString;

                SqlConnection myConnect = new SqlConnection(strConnectionString);

                String strCommandText = "INSERT MEDICALCENTRE(mcType, mcCentre) "
                    + " VALUES (@NewmcType,@NewmcCentre)";

                SqlCommand updateCmd = new SqlCommand(strCommandText, myConnect);

                if (rbHopistal.Checked)
                    updateCmd.Parameters.AddWithValue("@NewmcType", "Hospital");
                else
                    updateCmd.Parameters.AddWithValue("@NewmcType", "Clinic");
                updateCmd.Parameters.AddWithValue("@NewmcCentre", txtmcCentre.Text);

                myConnect.Open();

                result = updateCmd.ExecuteNonQuery();

                // STEP 5: Close
                myConnect.Close();
                return result;
            }

Error Trying to follow @Sergey Berezovskiy code. Must declare the scalar variable "@mcID".

private int AddMedicalCentreRecord()
    {
        int result = 0;

        string strConnectionString = ConfigurationManager.ConnectionStrings["SACPConnection"].ConnectionString;

        using (SqlConnection conn = new SqlConnection(strConnectionString))
        {
            conn.Open();
            using (SqlTransaction transaction = conn.BeginTransaction())
            {

                string insertCentreQuery =
                    @"INSERT MEDICALCENTRE (mcType, mcCentre) 
          VALUES (@NewmcType, @NewmcCentre)
          SELECT SCOPE_IDENTITY()";

                SqlCommand insertCentreCmd =
                    new SqlCommand(insertCentreQuery, conn, transaction);

                if (rbHopistal.Checked)
                    insertCentreCmd.Parameters.AddWithValue("@NewmcType", "Hospital");
                else
                    insertCentreCmd.Parameters.AddWithValue("@NewmcType", "Clinic");
                insertCentreCmd.Parameters.AddWithValue("@NewmcCentre", txtmcCentre.Text);

                int mcID = Convert.ToInt32(insertCentreCmd.ExecuteScalar());  

                string insertUserQuery =
                    @"INSERT WINDOWSADMIN (winUsername, winPassword, mcID)
          VALUES (@userName, @password, @mcID)";
                SqlCommand insertUserCmd =

                    new SqlCommand(insertUserQuery, conn, transaction);

                insertUserCmd.Parameters.AddWithValue("@userName", txtwinUsername.Text);
                insertUserCmd.Parameters.AddWithValue("@password", txtwinPassword.Text);

                insertUserCmd.ExecuteNonQuery();

                transaction.Commit();

                //conn.Open();

                //result = insertUserCmd.ExecuteNonQuery();

                conn.Close();
                return result;
            }
        }         


    }

Answer 1

You need to use transaction - begin transaction, then insert data into medicalcentre table and obtain mcID. After that insert data to windowsadmin table and commit transaction:

using(SqlConnection conn = new SqlConnection(strConnectionString))
{
    conn.Open();
    using(SqlTransaction transaction = conn.BeginTransaction())
    {
        // execute first command
        string insertCentreQuery = 
            @"INSERT MEDICALCENTRE (mcType, mcCentre) 
              VALUES (@NewmcType, @NewmcCentre)
              SELECT SCOPE_IDENTITY()";

        SqlCommand insertCentreCmd = 
            new SqlCommand(insertCentreQuery, conn, transaction);
        // ...provide command parameters
        int mcID = Convert.ToInt32(insertCentreCmd.ExecuteScalar());         

        // execute second command
        string insertUserQuery =
            @"INSERT WINDOWSADMIN (winUsername, winPassword, mcID)
              VALUES (@userName, @password, @mcID)";
        SqlCommand insertUserCmd = 
            new SqlCommand(insertUserQuery, conn, transaction);
        // ...provide command parameters
        insertUserCmd.ExecuteNonQuery();

        transaction.Commit();
    }   
}