When I insert the values in excel using windows forms.

Question

1.00/5 (2 votes)

See more:

C#

private void btn_save_Click(object sender, EventArgs e)
     {
         DataTable sheet1 = new DataTable("StockTaking");
         OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
         csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
         csbuilder.DataSource = "D:\\Anwesh\\New Microsoft Excel Worksheet.xls";
         csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
         string selectSql = @"SELECT * FROM [New Microsoft Excel Worksheet$]";
         using (OleDbConnection con = new OleDbConnection(csbuilder.ConnectionString))
         using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, con))
         {
             con.Open();
             adapter.Fill(sheet1);
         }

         //Insert
         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.ACE.OLEDB.12.0;Data Source='C:\\Users\\557546\\Downloads\\New Microsoft Excel Worksheet.xls';Extended Properties=Excel 8.0;");
         MyConnection.Open();
         myCommand.Connection = MyConnection;
         myCommand.Parameters.AddWithValue("@col1", txt_warehousNo.Text);
         myCommand.Parameters.AddWithValue("@col2", txt_barcd.Text);
         myCommand.Parameters.AddWithValue("@col3", txt_batchnum.Text);
         myCommand.Parameters.AddWithValue("@col4", txt_itmcode.Text);
         myCommand.Parameters.AddWithValue("@col5", txt_qty.Text);
         sql = "Insert into [New Microsoft Excel Worksheet$] (WareHouse No,Barcode,BatchNumber,ItemCode,Quantity) values(@col1,@col2,@col3,@col4,@col5)";
         myCommand.CommandText = sql;
         myCommand.ExecuteNonQuery();
         MyConnection.Close();
     }

What I have tried:

The Microsoft Office Access database engine could not find the object 'New Microsoft Excel Worksheet$'. Make sure the object exists and that you spell its name and the path name correctly.

Posted 22-Sep-16 23:15pm

Member 12716681

Updated 23-Sep-16 2:03am

Add a Solution

2 solutions

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)

Mathew Soji · Answer 1 · 2016-09-22T23:27:00

Solution 1

The reason for this error is you are trying to access a sheet named 'New Microsoft Excel Worksheet' . Please verify the sheet name in the excel one more time .
It could have been renamed or deleted. The default sheet name is sheet1 in excel .
Make sure the file exists in the path specified also.

https://support.microsoft.com/en-in/kb/316809[^]

Posted 22-Sep-16 23:27pm

Mathew Soji

Updated 22-Sep-16 23:31pm

v3

Comments

Member 12716681 23-Sep-16 5:32am

Only one file is there

Mathew Soji 23-Sep-16 5:37am

1) Make sure the file exist in the path given (D:\\Anwesh\\New Microsoft Excel Worksheet.xls) .

2) Open the file and see whether there exist a tab named "New Microsoft Excel Worksheet" , since you are inserting into this tab .

Member 12716681 23-Sep-16 5:40am

When i change the path i got same error.

Mathew Soji 23-Sep-16 5:42am

Have a look at the below code samples .

http://csharp.net-informations.com/excel/csharp-excel-oledb-insert.htm

Richard MacCutchan · Answer 2 · 2016-09-23T02:03:00

Add the following code to your application, and use the debugger to step through it and verify the sheet name:

C#

DataTable schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null });

foreach (DataRow dataRow in schemaTable.Rows)
{
    string tableName = dataRow["TABLE_NAME"].ToString();	// gets the sheet name from each schema entry
}