My Insert Statement Isn't working for Excel in C#

Question

Hey My Insert Statement isn't Working I used the same code for inserting other panel data to excel sheet it's working perfectly there but when I'm trying to insert data in other sheet using second panel it's throwing exception "Insert INTO Statement is not valid" I check every single thing in this i can't find any mistake in it. I'm using OleDb For Insertion. Here is the same code I've been using for first panel insertion.

 private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                String filename1 = @"E:DB\TestDB.xlsx";
                String connection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename1 + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
                OleDbConnection con = new OleDbConnection(connection);
                con.Open();
                int id = 4;
                string user = txtMUserName.Text.ToString();
                string pass = txtMPassword.Text.ToString();
                string role = txtMRole.Text.ToString();
                DateTime date = DateTime.Now;
                string Date = date.ToString("dd/MM/yyyy");
                //string Time = date.ToLongTimeString();
                string Time = "3:00 AM";
                String Command = "Insert into [Test$] (UserID, UserName, Password, Role, Created_Date,Created_Time) VALUES ('"
                        + id.ToString() + "','"
                        + user + "','"
                        + pass + "','"
                        + role + "','"
                        + Date + "','"
                        + Time + "')";
                OleDbCommand cmd = new OleDbCommand(Command, con);
                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Success!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

_"Here is the same code I've been using for first panel"_ - and what about the code that is failing? Please also check that the Excels you use are really the same (sheets missing in the second?). — Markus Deibel, Nov 29 '18 at 10:50
Please see https://stackoverflow.com/q/332365/11683 before you proceed with your problem. — GSerg, Nov 29 '18 at 11:06
Welcome @Muzammil! Two thoughts: First, look into the use of "using" around IDisposables like database connections. Second, txtMUserName.Text.ToString() can be shortened to txtMUserName.Text — n8wrl, Nov 29 '18 at 11:28

score 1 · Accepted Answer · answered Nov 29 '18 at 11:02

Seems like you are using a reserved name for column Password. you need to escape it with []:

string Command = "Insert into [Test$] (UserID, UserName, [Password], Role, Created_Date,Created_Time) VALUES ('"
                        + id.ToString() + "','"
                        + user + "','"
                        + pass + "','"
                        + role + "','"
                        + Date + "','"
                        + Time + "')";

My Insert Statement Isn't working for Excel in C#

1 Answers1