Trouble Retrieving Excel Data using VB.NET 2003

[AndyJ]

Hi and thanks in advance for any assistance recieved,

There is most likely a simple solution to this as usual...

I am trying to access data from an Excell worksheet using ADO.

The problem is that the OleDbCommand does not seem to recognise the Date
parameter in the command string.

Code follows:

Dim dDate As Date = dtp1.Value

Debug.WriteLine (dDate & " is the selected date.")

Dim conn As New System.Data.Ole Db.OleDbConnect ion(sConn)
Try
conn.Open()
Dim dvResult As New System.Data.Dat aView
Dim cmd As New System.Data.Ole Db.OleDbCommand ( _
"SELECT * FROM [DailyAnalysis$] WHERE [Date] = " &
dDate.ToShortDa teString, conn)

Dim ds As DataSet = New DataSet
Dim da As New OleDbDataAdapte r(cmd)
da.Fill(ds, "DailyAnalysis" )

'Debugging routine
For Each dr As DataRow In ds.Tables(0).Ro ws

Debug.WriteLine (System.String. Format("{0},{1} ,{2},{3},{4},{5 },{6},{7},{8},{ 9},{10},{11},{1 2}," & _
"{13},{14}" , _
dr("DATE"), dr("FACT"), dr("MACH"), dr("PROFILE"),
dr("FORMULA"), dr("LENGTH"), _
dr("QUANTITY") , dr("TOTAL MTRS"), dr("WPM"),
dr("TOTAL KG"), dr("BALANCE"), dr("STATUS"), _
dr(12), dr("CODE"), dr("Category")) )
Next

dvResult = New DataView(ds.Tab les(0))
DataGrid1.DataS ource = dvResult
....

If I Use this statement:

Dim cmd As New System.Data.Ole Db.OleDbCommand ( _
"SELECT * FROM [DailyAnalysis$] WHERE [Date] IS NOT NULL,
conn)

I get to populate my DataGrid but obviously with data that I dont require.

Any suggestions please?

[Cor Ligthert [MVP]]

Andy,

I was already stopping when you wrote that you did it in Ado. Don't tell
that you use ADO when it is ADONET. The differences are the same as between
a car and a plane.

I have no answer for you , however dates are always a pain. In your case I
would try it with an OleDB parameter.

Have a look at this sample for that.
http://www.vb-tips.com/default.aspx?...3-eb8b44af0137

I hope this helps,

Cor

"AndyJ" <An***@discussi ons.microsoft.c om> schreef in bericht
news:46******** *************** ***********@mic rosoft.com...

Hi and thanks in advance for any assistance recieved,

There is most likely a simple solution to this as usual...

I am trying to access data from an Excell worksheet using ADO.

The problem is that the OleDbCommand does not seem to recognise the Date
parameter in the command string.

Code follows:

Dim dDate As Date = dtp1.Value

Debug.WriteLine (dDate & " is the selected date.")

Dim conn As New System.Data.Ole Db.OleDbConnect ion(sConn)
Try
conn.Open()
Dim dvResult As New System.Data.Dat aView
Dim cmd As New System.Data.Ole Db.OleDbCommand ( _
"SELECT * FROM [DailyAnalysis$] WHERE [Date] = " &
dDate.ToShortDa teString, conn)

Dim ds As DataSet = New DataSet
Dim da As New OleDbDataAdapte r(cmd)
da.Fill(ds, "DailyAnalysis" )

'Debugging routine
For Each dr As DataRow In ds.Tables(0).Ro ws

Debug.WriteLine (System.String. Format("{0},{1} ,{2},{3},{4},{5 },{6},{7},{8},{ 9},{10},{11},{1 2},"
& _
"{13},{14}" , _
dr("DATE"), dr("FACT"), dr("MACH"), dr("PROFILE"),
dr("FORMULA"), dr("LENGTH"), _
dr("QUANTITY") , dr("TOTAL MTRS"), dr("WPM"),
dr("TOTAL KG"), dr("BALANCE"), dr("STATUS"), _
dr(12), dr("CODE"), dr("Category")) )
Next

dvResult = New DataView(ds.Tab les(0))
DataGrid1.DataS ource = dvResult
...

If I Use this statement:

Dim cmd As New System.Data.Ole Db.OleDbCommand ( _
"SELECT * FROM [DailyAnalysis$] WHERE [Date] IS NOT NULL,
conn)

I get to populate my DataGrid but obviously with data that I dont require.

Any suggestions please?

[AndyJ]

Cor,

You are right ADONET - I did some digging and found the solution:

Dim cmd As New System.Data.Ole Db.OleDbCommand ( _
"SELECT * FROM [DailyAnalysis$] WHERE [Date] = #" & dDate &
"#",conn)

Formatting of the dDate variable did the trick nicely.

Regards

"Cor Ligthert [MVP]" wrote:

Andy,

I was already stopping when you wrote that you did it in Ado. Don't tell
that you use ADO when it is ADONET. The differences are the same as between
a car and a plane.

I have no answer for you , however dates are always a pain. In your case I
would try it with an OleDB parameter.

Have a look at this sample for that.
http://www.vb-tips.com/default.aspx?...3-eb8b44af0137

I hope this helps,

Cor

"AndyJ" <An***@discussi ons.microsoft.c om> schreef in bericht
news:46******** *************** ***********@mic rosoft.com...

Hi and thanks in advance for any assistance recieved,

There is most likely a simple solution to this as usual...

I am trying to access data from an Excell worksheet using ADO.

The problem is that the OleDbCommand does not seem to recognise the Date
parameter in the command string.

Code follows:

Dim dDate As Date = dtp1.Value

Debug.WriteLine (dDate & " is the selected date.")

Dim conn As New System.Data.Ole Db.OleDbConnect ion(sConn)
Try
conn.Open()
Dim dvResult As New System.Data.Dat aView
Dim cmd As New System.Data.Ole Db.OleDbCommand ( _
"SELECT * FROM [DailyAnalysis$] WHERE [Date] = " &
dDate.ToShortDa teString, conn)

Dim ds As DataSet = New DataSet
Dim da As New OleDbDataAdapte r(cmd)
da.Fill(ds, "DailyAnalysis" )

'Debugging routine
For Each dr As DataRow In ds.Tables(0).Ro ws

Debug.WriteLine (System.String. Format("{0},{1} ,{2},{3},{4},{5 },{6},{7},{8},{ 9},{10},{11},{1 2},"
& _
"{13},{14}" , _
dr("DATE"), dr("FACT"), dr("MACH"), dr("PROFILE"),
dr("FORMULA"), dr("LENGTH"), _
dr("QUANTITY") , dr("TOTAL MTRS"), dr("WPM"),
dr("TOTAL KG"), dr("BALANCE"), dr("STATUS"), _
dr(12), dr("CODE"), dr("Category")) )
Next

dvResult = New DataView(ds.Tab les(0))
DataGrid1.DataS ource = dvResult
...

If I Use this statement:

Dim cmd As New System.Data.Ole Db.OleDbCommand ( _
"SELECT * FROM [DailyAnalysis$] WHERE [Date] IS NOT NULL,
conn)

I get to populate my DataGrid but obviously with data that I dont require.

Any suggestions please?