Click here to Skip to main content
15,033,043 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hello

how to load data into datagridview from excel and insert it to db from datagridview

this is my code for load data from excel file

select excel sheet code

C#
private void btnselect_file_Click(object sender, EventArgs e)
      {
          OpenFileDialog _objdlg = new OpenFileDialog();
          DialogResult _objdlgresult = _objdlg.ShowDialog();
          if (_objdlgresult == DialogResult.OK)
          {
              textBox_filename.Text = _objdlg.FileName;
          }
          GetExcelSheetNames(textBox_filename.Text);
      }


GetExcelSheetNames Void

C#
public void GetExcelSheetNames(string path)
     {
         List<string> sheets = new List<string>();
         string connectionString =
             String.Format(
                 @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""",
                 path);
         DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
         DbConnection connection = factory.CreateConnection();
         connection.ConnectionString = connectionString;
         connection.Open();
         DataTable tbl = connection.GetSchema("Tables");
         connection.Close();
         foreach (DataRow row in tbl.Rows)
         {
             string sheetName = (string) row["TABLE_NAME"];
             if (sheetName.EndsWith("$"))
             {
                 sheetName = sheetName.Substring(0, sheetName.Length - 1);
             }
             sheets.Add(sheetName);
         }
         /*Bind data to listbox*/
         combo_sheetname.DataSource = sheets;
     }


load the selected sheet into datagridview

C#
private void btnsload_file_Click(object sender, EventArgs e)
{
    String name = "Items";
    String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox_filename.Text +
                    ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

    OleDbConnection con = new OleDbConnection(constr);
    OleDbCommand oconn = new OleDbCommand("Select * From [" + combo_sheetname.Text + "$]", con);
    con.Open();

    OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
    DataTable data = new DataTable();
    sda.Fill(data);
    dataGridView1.DataSource = data;
}

and this is my code to insert the data from datagridview into db

C#
private void btn_savefromexcel_Click(object sender, EventArgs e)
       {

           SqlConnection cne =
               new SqlConnection(ConfigurationManager.ConnectionStrings["payroll"].ConnectionString);
           for (int i = 0; i < dataGridView1.Rows.Count; i++)
           {
               SqlCommand cmdz =new SqlCommand(@"INSERT INTO WPS_data (emp_id,emp_QID,emp_visa_ID,emp_name,bank_short_name,bank_account_number, salary_frequency, working_days, net_salary, basic_salary, extra_hours, extra_income, Deductions, Payment_Type, Comments) VALUES ("
                                  + dataGridView1.Rows[i].Cells[0].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[1].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[2].Value.ToString() + ","
                                  + dataGridView1.Rows[i].Cells[3].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[4].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[5].Value.ToString() + ","
                                  + dataGridView1.Rows[i].Cells[6].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[7].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[8].Value.ToString() + ","
                                  + dataGridView1.Rows[i].Cells[9].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[10].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[11].Value.ToString() + ","
                                  + dataGridView1.Rows[i].Cells[12].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[13].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[14].Value.ToString() + ");", cne);
               cmdz.Connection = cne;
               cmdz.CommandType = CommandType.Text;
               cne.Open();
               cmdz.ExecuteScalar();
               cne.Close();
               MessageBox.Show(@"Insert New Data ", @"New Data Has Been Imported Successfully !",
                   MessageBoxButtons.OK, MessageBoxIcon.Information);
           }


the error that i faced is invalid column name and its see the cell as column idk why

here is my excel file

http://i.imgur.com/hF4NoZS.png
Posted
Comments
Michael_Davies 18-Nov-15 10:38am
   
Best to use a parameterised sql command rather than build a command string.

If you must build a string then normally a non-numeric field's data is enclosed in single quote and you do not have any anywhere for example:

VALUES ('"+ dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" +
dataGridView1.Rows[i].Cells[1].Value.ToString() + "','" ...etc.

Assuming the two fields above are string.
Developer It 19-Nov-15 0:44am
   
omg yes this was the thing that prevent from insert but after insert i faced this error

An unhandled exception of type 'System.NullReferenceException' occurred in Payroll Operations.exe

Additional information: Object reference not set to an instance of an object.

and please add a solution to accept it
Michael_Davies 19-Nov-15 2:52am
   
You do not verify that any of the fields in the datagridview1's rows actually contain data, one of them obviously does not.
Developer It 19-Nov-15 6:22am
   
Done Thanks For you help could you please past your comment as a solution to ad rep for you and accept it as an answer

Best to use a parameterised sql command rather than build a command string.

If you must build a string then normally a non-numeric field's data is enclosed in single quote and you do not have any anywhere for example:

VALUES ('"+ dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" +
dataGridView1.Rows[i].Cells[1].Value.ToString() + "','" ...etc.

Assuming the two fields above are string.
   
Debug and run the query in the backend (sql server studio).
You will get a better idea about what could be wrong with the query.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900