Nikhil Kurkure

Nikhil Kurkure

  • NA
  • 5
  • 309

Excel upload to datagridview 3rd cell not working

Aug 10 2015 9:01 AM
private void Upload_Excel_click(object sender, EventArgs e)
{
// For Uploading Excel file From Computer
int count = 0;
string[] FilenameName;
foreach (string item in openFileDialog1.FileNames)
{
FilenameName = item.Split('\\');
File.Copy(item, @"Images\" + FilenameName[FilenameName.Length - 1]);
count++;
}
//for storing data from Excel to datagridview
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\Edbeans Windows App Modified\\Edbeans Windows App\\Project\\Registration\\bin\\Debug\\Images\\Book1.xlsx';Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("ams_records ", "temp_reg_details");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dataGridView1.DataSource = DtSet.Tables[0];
MySqlConnection local_connection = myConnection.getConnection();
local_connection.Open();
string str = this.dataGridView1.Rows.Count.ToString();
int num = int.Parse(str);
for (int row = 0; row < num - 1; row++)
{

string query = "INSERT INTO temp_reg_details(fname, mname, lname, class_code,guard_fname,guard_mname,guard_lname,guard_mob_no,guard_email,rfid_cardNo,rfid_password) VALUES (@fname, @mname, @lname, @class_code, @guard_fname, @guard_mname, @guard_lname, @guard_mob_no, @guard_email, @rfid_cardNo, @rfid_password)";
MySqlCommand cmd = new MySqlCommand(query, local_connection);
cmd.Parameters.AddWithValue(
"@fname", dataGridView1.Rows[row].Cells[1].Value.ToString());
cmd.Parameters.AddWithValue(
"@mname", dataGridView1.Rows[row].Cells[2].Value.ToString());
//Its shows error of NullrefereceException was unhandled(Object reference not set to an instance of an object.) while fetching data from 3rd cell
cmd.Parameters.AddWithValue(
"@lname", dataGridView1.Rows[row].Cells[3].Value.ToString());
//below are working fine
cmd.Parameters.AddWithValue(
"@class_code", dataGridView1.Rows[row].Cells[4].Value.ToString());
cmd.Parameters.AddWithValue(
"@guard_fname", dataGridView1.Rows[row].Cells[5].Value.ToString());
cmd.Parameters.AddWithValue(
"@guard_mname", dataGridView1.Rows[row].Cells[6].Value.ToString());
cmd.Parameters.AddWithValue(
"@guard_lname", dataGridView1.Rows[row].Cells[7].Value.ToString());
cmd.Parameters.AddWithValue(
"@guard_mob_no", dataGridView1.Rows[row].Cells[8].Value.ToString());
cmd.Parameters.AddWithValue(
"@guard_email", dataGridView1.Rows[row].Cells[9].Value.ToString());
cmd.Parameters.AddWithValue(
"@rfid_cardNo", dataGridView1.Rows[row].Cells[10].Value.ToString());
cmd.Parameters.AddWithValue(
"@rfid_password", dataGridView1.Rows[row].Cells[11].Value.ToString());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); //for proper looping
}
MessageBox.Show("sucess");
local_connection.Close();

}

Answers (2)