I can't add the data into excel sheet even after executing query

Question

0.00/5 (No votes)

See more:

C#

private void btn_submit_Click(object sender, EventArgs e)
   {
       try
       {

           string name = textBox1.Text;
           string filepath="D://"+name;

           System.Data.OleDb.OleDbConnection MyConnection;
           System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
           string sql = null;
           MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filepath+".xls;Extended Properties=Excel 8.0;");
           MyConnection.Open();
           myCommand.Connection = MyConnection;
           string createsql = null;
           createsql = "CREATE TABLE [Sheet1$] (F1 char(255), F2 char(255))";
           myCommand.CommandText = createsql;
           myCommand.ExecuteNonQuery();
           //myCommand.CommandText = createsql;
           //myCommand.ExecuteNonQuery();
           sql = "Insert into [Sheet1$] (F1,F2) values('"+textBox1.Text+"','"+textBox2.Text+"')";
           myCommand.CommandText = sql;
           myCommand.ExecuteNonQuery();
           MyConnection.Close();
       }
       catch (Exception ex)
       {
           MessageBox.Show(ex.ToString());
       }
   }

What I have tried:

i have written this code to add data into excel sheet.though my query is running perfectly but data is not getting written in excel file

Posted 27-Aug-16 22:46pm

Member 10549697

Updated 5-Sep-16 10:34am

Add a Solution

Comments

Richard MacCutchan 28-Aug-16 8:14am

Nothing obviously wrong that I can see. Try stepping through with your debugger and check the path to see that the file is getting saved in the right place.

Member 10549697 29-Aug-16 1:25am

sir path is right.there is no problem in code but when i open my excel sheet it shows an empty sheet

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2016-09-05T10:34:00

Take a look at MSDN documentation: How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook[^]

On the first look, you don't need this piece of code:
C#

createsql = "CREATE TABLE [Sheet1$] (F1 char(255), F2 char(255))"; myCommand.CommandText = createsql; myCommand.ExecuteNonQuery();

An Excel file and Sheet1 already exist, so...

Additional note:
Never, never use such of code

C#

sql = "Insert into [Sheet1$] (F1,F2) values('"+textBox1.Text+"','"+textBox2.Text+"')";

to insert data into database (even if it's an Excel file). Use parameterized queries instead.

C#

//Define SQL query.
cmn.CommandText = "INSERT INTO [Sheet1$] (F1, F2) VALUES (@one, @two);";
//add parameters with their values.
cmn.Parameters.AddWithValue("@one", textBox1.Text);
cmn.Parameters.AddWithValue("@two", textBox2.Text);

For further details, please see:
OleDbParameterCollection.AddWithValue Method (String, Object) (System.Data.OleDb)[^]
OleDbCommand.Parameters Property (System.Data.OleDb)[^]
ADO.NET Code Examples[^]
Configuring Parameters and Parameter Data Types[^]