Click here to Skip to main content
15,885,998 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
i have a database in excel and i want to delete a row from that excel sheet.
Here i am mentioning my code written in c#

private void pbDelete_Click(object sender, EventArgs e)
        {
            if (MobileNumber == "")
            {
                MessageBox.Show("Please select a Name !..");
            }
            else
            {
                if (MessageBox.Show("Do you realy want to delete this data?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    if (index != -1)
                    {
                        string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.GetDirectoryName(Application.ExecutablePath) + "\\Taas SMS.xls;Extended Properties=Excel 8.0;";
                        //oledbConn.Open();
                        //cmd1 = new OleDbCommand();
                        //cmd.Connection = oledbConn;
                        //cmd.CommandText = "INSERT INTO ExecutiveMember (Name,Designation,TravelAgent, MobileNumber) values ('" + dt.Rows[i][0] + "', '" + dt.Rows[i][1] + "','" + dt.Rows[i][2] + "', '" + dt.Rows[i][3] + "')";
                        //cmd.ExecuteNonQuery();
                        //oledbConn.Close();
                        OleDbConnection oledbConn1 = new OleDbConnection(conn);
                        oledbConn1.Open();
                        OleDbCommand cmd1 = new OleDbCommand();
                        cmd1.Connection = oledbConn1;
                        cmd1.CommandText = "select * from ExecutiveMember";
                        OleDbDataAdapter da = new OleDbDataAdapter(cmd1);
                        DataTable dt1 = new DataTable();
                        //dt1.Clear();
                        da.Fill(dt1);

                        OleDbConnection oledbConn = new OleDbConnection(conn);
                        oledbConn.Open();
                        OleDbCommand cmd2 = new OleDbCommand();
                        cmd2.Connection = oledbConn;
                        cmd2.CommandText = "update ExecutiveMember set Name='',Designation='',TravelAgent='',MobileNumber='' where MobileNumber='" + dt1.Rows[index][3] + "'";
                        cmd2.ExecuteNonQuery();
                        oledbConn.Close();

                        //DataRow dtRowDelete = dt1.Rows[index];
                        //dtRowDelete.Delete();
                       
                        //dt1.AcceptChanges();
                        da.Update(dt1);

                        dgvExecutiveMember.DataSource = dt1;
                        
                        //conn = new OleDbConnection();
                        //conn.ConnectionString = gm.ConnectionString;
                        //conn.Open();
                        ////cmd = new OleDbCommand("select * from ExecutiveMember where MobileNumber='" + MobileNumber + "'", conn);
                        //cmd = new OleDbCommand("select * from ExecutiveMember", conn);
                        //da = new OleDbDataAdapter(cmd);
                        //dt.Clear();
                        //da.Fill(dt);
                        //DataRow dtRowDelete = dt.Rows[index];
                        //dtRowDelete.Delete();
                        //dt.AcceptChanges();
                        //da.Update(dt);

                        
                        OleDbConnection oledbConn2 = new OleDbConnection(conn);
                        oledbConn.Open();

                        //conn = new OleDbConnection();
                        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [ExecutiveMember]", oledbConn2);
                        OleDbDataAdapter oleda = new OleDbDataAdapter();
                        oleda.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        oleda.Fill(ds, "ExecutiveMember");

                        int rowcount = ds.Tables[0].Rows.Count;
                        oledbConn.Close();

                        
                        

                        for (int i = 0; i < rowcount-2; i++)
                        {
                            
                            oledbConn.Open();
                            cmd = new OleDbCommand();
                            cmd.Connection = oledbConn;
                            cmd.CommandText = "update ExecutiveMember set Name='" + dt1.Rows[i][0] + "',Designation='" + dt1.Rows[i][1] + "',TravelAgent='" + dt1.Rows[i][2] + "',MobileNumber='" + dt1.Rows[i][3] + "' where MobileNumber='" + dt1.Rows[i][3] + "'";
                            //cmd.CommandText = "INSERT INTO ExecutiveMember (Name,Designation,TravelAgent, MobileNumber) values ('" + dt1.Rows[i][0] + "', '" + dt1.Rows[i][1] + "','" + dt1.Rows[i][2] + "', '" + dt1.Rows[i][3] + "')";
                            cmd.ExecuteNonQuery();
                            oledbConn.Close();
                            Clear();

                            
                        }

                        //    conn = new OleDbConnection();
                        //    conn.ConnectionString = gm.ConnectionString;
                        //    conn.Open();
                        //    cmd = new OleDbCommand();
                        //    cmd.Connection = conn;

                        //    cmd.CommandText = "INSERT INTO ExecutiveMember (Name,Designation,TravelAgent, MobileNumber) values ('" + dt.Rows[i][0] + "', '" + dt.Rows[i][1] + "','" + dt.Rows[i][2] + "', '" + dt.Rows[i][3] + "')";
                        //    cmd.ExecuteNonQuery();
                        //    conn.Close();
                        //    Clear();
                        //    FillGrid();
                        //}
                         //conn.Close();

                        
                        Clear();
                        MobileNumber = "";
                        //FillGrid();
                                                                       
                        index = -1;
                        MessageBox.Show("Data Deleted!...");
                                                
                    }
                }
                else
                {

                }
            }
            
        }



These code can delete row from that Excel file, but i want to replace the null field with the next row value of that Excel sheet.
Please help me to solve this. Thanks in advance.
Posted
Updated 25-Aug-11 19:22pm
v2
Comments
[no name] 24-Aug-11 12:30pm    
Your title doesn't really match the question you have at the end of your post. Should we assume you receiving Object null reference exception when running this code?

If this is the case TheyCallMeMrJames has answered your question.

When you get an error such as

Object reference not set to an instance of an object


It means that one of your objects is not initialized. With your debugger attached, you should get a line number and exception details right within the IDE. You can use that to work through the error above.

There are several samples and troubleshooting efforts here[^] for a range of problems.

Cheers.
 
Share this answer
 
You are using dt.Clear();
I don't see a place where you have defined dt.rows.
 
Share this answer
 
v2
Comments
wizardzz 24-Aug-11 13:42pm    
If that were the case, wouldn't it not compile?

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