Connecting to excel datasheets using C#

[Narwe]

I am attempting to write a program that takes Excel datasheet information and
puts it in a Access database.
I'm having problems.

My datasheet is called 'Baseline & Week1'

Here is the relevant code:

OleDbDataAdapter oleCmd = new OleDbDataAdapter("SELECT * FROM
[Baseline & Week1$]", oleConn);

.. . .

foreach(DataRow oleRow in oDS.Tables[0].Rows)
{
write some data
}

The above code works.

However, if I modify my foreach to look like this:

foreach(DataRow oleRow in oDS.Tables["[Baseline & Week1$]"].Rows),

substituting the actual name of my datasheet in place of '0', I get an
"Object reference not set to an instance of an object" error.

Is my ' oDS.Tables{"Baseline & Week1$]"] ' syntax incorrect?

 

[Jamie Collins]

I am attempting to write a program that takes Excel datasheet information and
puts it in a Access database.
I'm having problems.

My datasheet is called 'Baseline & Week1'

Here is the relevant code:

OleDbDataAdapter oleCmd = new OleDbDataAdapter("SELECT * FROM
[Baseline & Week1$]", oleConn);

. . .

foreach(DataRow oleRow in oDS.Tables[0].Rows)
{
write some data
}

The above code works.

However, if I modify my foreach to look like this:

foreach(DataRow oleRow in oDS.Tables["[Baseline & Week1$]"].Rows),

substituting the actual name of my datasheet in place of '0', I get an
"Object reference not set to an instance of an object" error.

Is my ' oDS.Tables{"Baseline & Week1$]"] ' syntax incorrect?

Better to let the OLE DB provider handle the INSERT e.g.

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='C:\\Tempo\\db.xls';Extended Properties='Excel 8.0;HDR=YES'";
OleDbConnection Conn = new OleDbConnection(strConn);
Conn.Open();
string strSQL = "INSERT INTO
[Database=C:\\Tempo\\New_Jet_DB.mdb;].MyTable (MyKeyCol, MyIntCol)
SELECT * FROM ['Baseline & Week1$'];";
OleDbCommand Comm = new OleDbCommand(strSQL, Conn);
Comm.ExecuteNonQuery();
Comm.Connection.Close();

Jamie.

--