Click here to Skip to main content
15,891,951 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
C#
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();

    }
Posted
Updated 10-Aug-15 3:04am
v3
Comments
Suvendu Shekhar Giri 10-Aug-15 9:11am    
Have you checked what dataGridView1.Rows[row].Cells[3].Value contains, while debugging?
Nikhil_Kurkure 10-Aug-15 9:18am    
Its shows error of NullrefereceException was unhandled(Object reference not set to an instance of an object.) while fetching also while debugging it does not show any value..directly shows error messege
Suvendu Shekhar Giri 10-Aug-15 9:24am    
Just put a breakpoint before that line and debug.
Suvendu Shekhar Giri 10-Aug-15 9:27am    
Can you share the data contained in the excel sheet for that particular row which is throwing the exception?
Nikhil_Kurkure 10-Aug-15 9:31am    
header coloumn contains following coloumn
1)fname
2)mname
3)lname
4)class_code
5)guard_fname
6)guard_mname
7)guard_lname
8)guard_mob_no
9)guard_email
10)rfid_cardNo
11)rfid_password

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