Multiple records are getting inserted at once using asp.net c#

Last Reply 4 months ago By pandeyism

Posted 4 months ago

Hello Sir, I'm trying to insert data into two different tables using a single button click event. I'm using a dropdown to select the staff for which the details has to be inserted, so once the data is getting inserted for the selected one and the other time for the first option like "---Select staff--". Below is the code which i'm using to insert.

            if (FileUpload1.HasFile)
            { 
                foreach (HttpPostedFile postedFile in FileUpload1.PostedFiles)
                {
                    string filename = Path.GetFileName(postedFile.FileName);
                    string contentType = postedFile.ContentType;
                    string staffname=DropDownList1.SelectedItem.Text;
                    Stream fs = postedFile.InputStream;
                    BinaryReader br = new BinaryReader(fs);
                    byte[] bytes = br.ReadBytes((Int32)fs.Length);
                    string constr = ConfigurationManager.ConnectionStrings["crm_db"].ConnectionString;
                    SqlConnection con = new SqlConnection(constr);

                    //code for first table
                    SqlCommand cmd = new SqlCommand("crm_sp_insert_staffdocuments", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@Name", filename);
                    cmd.Parameters.AddWithValue("@ContentType", contentType);
                    cmd.Parameters.AddWithValue("@Data", bytes);
                    cmd.Parameters.AddWithValue("@staffname", staffname);
                    cmd.Parameters.AddWithValue("@created_on", indianTime);
                    cmd.Parameters.AddWithValue("@created_by", uname);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    //code for second table
                    SqlCommand cmd1 = new SqlCommand("crm_sp_insert_staffcompliance", con);
                    cmd1.CommandType = CommandType.StoredProcedure;
                    cmd1.Connection = con;
                    cmd1.Parameters.AddWithValue("@email", txt_email.Text);
                    cmd1.Parameters.AddWithValue("@Birth_Certificate", str1);
                    cmd1.Parameters.AddWithValue("@Degree_Certificate", str2);
                    cmd1.Parameters.AddWithValue("@Address_Proof", str3);
                    cmd1.Parameters.AddWithValue("@Resume", str4);
                    cmd1.Parameters.AddWithValue("@Driving_Licence", str5);
                    if (licencedate.Text == "")
                    { cmd1.Parameters.AddWithValue("@driving_date", DateTime.MinValue.ToLongDateString());                    }
                    else
                    { cmd1.Parameters.AddWithValue("@driving_date", Convert.ToDateTime(licencedate.Text));                    }
                    cmd1.Parameters.AddWithValue("@Visa_document", str6);
                    if (txt_date.Text == "")
                    { cmd1.Parameters.AddWithValue("@visa_date", DateTime.MinValue.ToLongDateString());                    }
                    else
                    { cmd1.Parameters.AddWithValue("@visa_date", Convert.ToDateTime(txt_date.Text));                    }
                    cmd1.Parameters.AddWithValue("@Police_check", str7);
                    if (txt_policechk.Text == "")
                    { cmd1.Parameters.AddWithValue("@police_date", DateTime.MinValue.ToLongDateString());                    }
                    else
                    { cmd1.Parameters.AddWithValue("@police_date", Convert.ToDateTime(txt_policechk.Text));                    }
                    cmd1.Parameters.AddWithValue("@ID_check", str8);
                    cmd1.Parameters.AddWithValue("@staffname", DropDownList1.SelectedItem.Text);
                    cmd1.Parameters.AddWithValue("@created_on", indianTime);
                    cmd1.Parameters.AddWithValue("@created_by", uname);
                    con.Open();
                    cmd1.ExecuteNonQuery();
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "SweetAlert", "swal('Success!', 'Staff Documents Uploaded successfully.!', 'success');", true);
                    con.Close();
                }
            }

Thanks in advance

Posted 4 months ago

Hey democloud,

Please take reference to the below code and correct you code.

Remove second table code and put it outside of loop and first table code put in loop.

Code

protected void Insert(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string staffname = DropDownList1.SelectedItem.Text;
        string constr = ConfigurationManager.ConnectionStrings["crm_db"].ConnectionString;
        SqlConnection con = new SqlConnection(constr);
        //code for second table
        SqlCommand cmd1 = new SqlCommand("crm_sp_insert_staffcompliance", con);
        cmd1.CommandType = CommandType.StoredProcedure;
        cmd1.Connection = con;
        cmd1.Parameters.AddWithValue("@email", txt_email.Text);
        cmd1.Parameters.AddWithValue("@Birth_Certificate", str1);
        cmd1.Parameters.AddWithValue("@Degree_Certificate", str2);
        cmd1.Parameters.AddWithValue("@Address_Proof", str3);
        cmd1.Parameters.AddWithValue("@Resume", str4);
        cmd1.Parameters.AddWithValue("@Driving_Licence", str5);
        if (licencedate.Text == "")
        {
            cmd1.Parameters.AddWithValue("@driving_date", DateTime.MinValue.ToLongDateString());
        }
        else
        {
            cmd1.Parameters.AddWithValue("@driving_date", Convert.ToDateTime(licencedate.Text));
        }
        cmd1.Parameters.AddWithValue("@Visa_document", str6);
        if (txt_date.Text == "")
        {
            cmd1.Parameters.AddWithValue("@visa_date", DateTime.MinValue.ToLongDateString());
        }
        else
        {
            cmd1.Parameters.AddWithValue("@visa_date", Convert.ToDateTime(txt_date.Text));
        }
        cmd1.Parameters.AddWithValue("@Police_check", str7);
        if (txt_policechk.Text == "")
        {
            cmd1.Parameters.AddWithValue("@police_date", DateTime.MinValue.ToLongDateString());
        }
        else
        {
            cmd1.Parameters.AddWithValue("@police_date", Convert.ToDateTime(txt_policechk.Text));
        }
        cmd1.Parameters.AddWithValue("@ID_check", str8);
        cmd1.Parameters.AddWithValue("@staffname", DropDownList1.SelectedItem.Text);
        cmd1.Parameters.AddWithValue("@created_on", indianTime);
        cmd1.Parameters.AddWithValue("@created_by", uname);
        con.Open();
        cmd1.ExecuteNonQuery();
        con.Close();

        foreach (HttpPostedFile postedFile in FileUpload1.PostedFiles)
        {
            string filename = Path.GetFileName(postedFile.FileName);
            string contentType = postedFile.ContentType;
            Stream fs = postedFile.InputStream;
            BinaryReader br = new BinaryReader(fs);
            byte[] bytes = br.ReadBytes((Int32)fs.Length);

            //code for first table
            SqlCommand cmd = new SqlCommand("INSERT INTO tblfiles(Name,ContentType,Data,staffname) VALUES(@Name,@ContentType,@Data,@staffname)", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@Name", filename);
            cmd.Parameters.AddWithValue("@ContentType", contentType);
            cmd.Parameters.AddWithValue("@Data", bytes);
            cmd.Parameters.AddWithValue("@staffname", staffname);
            cmd.Parameters.AddWithValue("@created_on", indianTime);
            cmd.Parameters.AddWithValue("@created_by", uname);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
        ScriptManager.RegisterStartupScript(this, this.GetType(), "SweetAlert", "swal('Success!', 'Staff Documents Uploaded successfully.!', 'success');", true);
    }
}