C# - error loading excel file into datatable

[acole1]

I have used this method before to run a search on a database, then load the results into a datatable, but I can't seem to translate the code into this situation....


I have an Excel file that I need to load into a datatable, then after some massaging, take the datatable and load it into a SQL table.

I am able to read the Excel file and output the value of B1 to a message box.

I then took the SQL search code from a previous program, and adapted it (so I thought) to allow me to load the excel file into the datagrid.

The code is below... let me know what I need to fix. I'm not exactly looking for best practices since I will probably only use this code a couple times, but it needs to work.

The error I receive with the code below is:
NullReferenceException was unhandled
Object reference not set to an instance of an object.

Thanks!


private void button1_Click(object sender, EventArgs e)
{

string filename = @"C:\ExcelFile.xls"; // Set to your xls file
string Type = "Excel 8.0"; // For Excel 97/2000/2003
// string Type = "Excel 5.0"; // For Excel 5.0/95
bool Header = true; // True if the first row in the Excel file is a header
// (used for column names, not data)
bool TreatIntermixedAsText = true; // True if columns containing different data types are
// treated as text (note that columns containing only
// integer types are still treated as integer, etc)

OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.DataSource = filename;
builder.Provider = "Microsoft.Jet.OLEDB.4.0";
builder["Extended Properties"] = Type +
";HDR=" + (Header ? "Yes" : "No") +
";IMEX=" + (TreatIntermixedAsText ? "1" : "0");

OleDbConnection connection = new OleDbConnection(builder.ConnectionString);
connection.Open();


//Message box code
string ExcelCommand = "SELECT * FROM [Sheet1$];";

OleDbCommand cmd = new OleDbCommand(ExcelCommand, connection);

OleDbDataReader reader = cmd.ExecuteReader();

reader.Read();
string returned = "NA";
returned = reader["Plant"].ToString();
//MessageBox.Show(returned


//Datagrid code
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);

dataAdapter.SelectCommand.Parameters.Add("Plant", OleDbType.VarChar).Value = textBox1.Text;

// Populate a new data table and bind it to the BindingSource.
DataTable table = new DataTable();
table.Locale = CultureInfo.InvariantCulture;
dataAdapter.Fill(table);

PartTranBindingSource.DataSource = table;

// Resize the DataGridView columns to fit the newly loaded content.
PartTranDataGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

// you can make it grid readonly.
PartTranDataGrid.ReadOnly = true;

// finally bind the data to the grid
PartTranDataGrid.DataSource = PartTranBindingSource;

}

 

[acole1]

It looks like DataBind() is not needed in winforms only in webforms. I commented the line out, but still no data shows up in my DataGrid.


Edit:
I had too much code in place. Here is the solution that worked.

string connstr ="Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + @"C:\ExcelFile.xls" + ";Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(connstr);
string strSQL = "SELECT * FROM [Sheet1$]";

OleDbCommand cmd = new OleDbCommand(strSQL, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataTable dt = new DataTable();
da.Fill(dt);
DataGrid.DataSource = dt;

 

[winstongel]

Site link and irrelevant solution to four year-old question removed.

Markbnj