Trouble Retrieving Excel Data using VB.NET 2003

[Guest]

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.OleDb.OleDbConnection(sConn)
Try
conn.Open()
Dim dvResult As New System.Data.DataView
Dim cmd As New System.Data.OleDb.OleDbCommand( _
"SELECT * FROM [DailyAnalysis$] WHERE [Date] = " &
dDate.ToShortDateString, conn)

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

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

Debug.WriteLine(System.String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12}," & _
"{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.Tables(0))
DataGrid1.DataSource = dvResult
....

If I Use this statement:

Dim cmd As New System.Data.OleDb.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?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

I hope this helps,

Cor

 

[Guest]

Cor,

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

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

Formatting of the dDate variable did the trick nicely.

Regards

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?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

I hope this helps,

Cor

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.OleDb.OleDbConnection(sConn)
Try
conn.Open()
Dim dvResult As New System.Data.DataView
Dim cmd As New System.Data.OleDb.OleDbCommand( _
"SELECT * FROM [DailyAnalysis$] WHERE [Date] = " &
dDate.ToShortDateString, conn)

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

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

Debug.WriteLine(System.String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},"
& _
"{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.Tables(0))
DataGrid1.DataSource = dvResult
...

If I Use this statement:

Dim cmd As New System.Data.OleDb.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?