Error: external table is not in the expected format

Question

Hi I am getting an error when fetching the excel sheet to datagridview.This is the error i am getting external table is not in the expected format in vb.net.So any one find where exactly the error.The values values are not Binding to dataset. This is my code Private Sub Button1_Click(ByVal sender As System.Object ByVal e As System.EventArgs) Handles Button1.Click Try Dim openbox As New OpenFileDialog openbox.Filter = Excel Workbook (*.xls) |*.xls;*.xlsx openbox.ShowDialog() Dim fname As String = openbox.FileName Dim MyConnection As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter MyConnection = New System.Data.OleDb.OleDbConnection _ (Provider=Microsoft.Jet.Oledb.4.0; Data Source= & fname & ; Extended Properties=Excel 8.0) MyCommand = New System.Data.OleDb.OleDbDataAdapter _ (select * from [Sheet1$] MyConnection) Dim workbook As DataSet = New DataSet() MyCommand.TableMappings.Add(Table testtable) DtSet = New System.Data.DataSet MyCommand.Fill(DtSet testtable) DataGridView1.DataSource = DtSet.Tables(0) MyConnection.Close() Catch ex As Exception MsgBox(ex.ToString) End Try End Sub Thanks & Regards sathya

reshma ss · Answer

can u explain in detail

Shiva kumar · Answer

change the excel oledb string from new OleDbConnection(provider=Microsoft.Jet.OLEDB.4.0;  + data source=' + s +  ';  + Extended Properties=Excel 8.0;); this to new OleDbConnection(provider=Microsoft.ACE.OLEDB.12.0;  + data source=' + s +  ';  + Extended Properties=Excel 8.0;);

Abhay Shanker · Answer

Try as below:- string conStr=  ; conStr = ConfigurationManager .ConnectionStrings[ Excel03ConString ] .ConnectionString; conStr = String .Format(conStr FilePath isHDR); OleDbConnection connExcel = new OleDbConnection (conStr); OleDbCommand cmdExcel = new OleDbCommand (); OleDbDataAdapter oda = new OleDbDataAdapter (); DataTable dt = new DataTable (); cmdExcel.Connection = connExcel; //Get the name of First Sheet connExcel.Open(); DataTable dtExcelSchema; dtExcelSchema = connExcel.GetOleDbSchemaTable( OleDbSchemaGuid .Tables null ); string SheetName = dtExcelSchema.Rows[0][ TABLE_NAME ].ToString(); connExcel.Close(); //Read Data from First Sheet connExcel.Open(); cmdExcel.CommandText = SELECT * From [ + SheetName + ] ; oda.SelectCommand = cmdExcel; oda.Fill(dt); connExcel.Close(); //Bind Data to GridView GridView1.Caption = Path .GetFileName(FilePath); GridView1.DataSource = dt; GridView1.DataBind();

Satyapriya Nayak · Answer

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace Retrieving_excel_data { public partial class Form1 : Form { public OleDbConnection con; public void pintu(string s) { //con = new OleDbConnection(provider=Microsoft.Jet.OLEDB.4.0;  + data source=' + s +  ';  + Extended Properties=Excel 8.0;); con = new OleDbConnection(Provider=Microsoft.ACE.OLEDB.12.0;  + data source=' + s +  ';  + Extended Properties=Excel 12.0;); } public OleDbCommand com; public DataSet ds; public OleDbDataAdapter oledbda; public DataTable dt; public string str; public Form1() { InitializeComponent(); } private void btnbrowse_Click(object sender EventArgs e) { OpenFileDialog openfiledialog1 = new OpenFileDialog(); openfiledialog1.ShowDialog(); openfiledialog1.Filter = allfiles|*.xls; TextBox1.Text = openfiledialog1.FileName; } private void btndisplay_Click(object sender EventArgs e) { pintu(TextBox1.Text); try { con.Open(); str = select * from [sheet1$]; com = new OleDbCommand(str con); ds = new DataSet(); oledbda = new OleDbDataAdapter(com); oledbda.Fill(ds [sheet1$]); con.Close(); DataGridView1.DataSource = ds; DataGridView1.DataMember = [sheet1$]; } catch (Exception ex) { MessageBox.Show(ex.Message); } } } }