473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Trouble Retrieving Excel Data using VB.NET 2003

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?
Mar 19 '06 #1
2 1478
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***@discussions.microsoft.com> schreef in bericht
news:46**********************************@microsof t.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.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?

Mar 19 '06 #2
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

"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***@discussions.microsoft.com> schreef in bericht
news:46**********************************@microsof t.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.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?


Mar 19 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table....
0
by: Mike Knight | last post by:
(I've also posted this problem on microsoft.public.excel.programming) I have a MS Access 2003 Database named "AS400 Fields.mdb". This database contains links to tables on an AS400. In MS...
17
by: Mansi | last post by:
I need to do some research on how to use excel automation from c#. Does anyone know of any good books related to this subject? Thanks. Mansi
3
by: Rik Moed | last post by:
Hi, I am having a problem with Excel 2003 worksheets when I upload them using the HtmlIputFile. After the upload, I start to download the worksheet and it appears to be currupt. I recieve the...
3
by: ineedahelp | last post by:
Hi and HELP!, I am using Excel and Access 2003. I am trying to create a link to an excel spreadsheet and append the data from a specific sheet called "PEJ". My error is runtime error 3125 "PEJ$"...
10
by: Hendri Adriaens | last post by:
Hi, I'm trying to automate the creation of an excel file via COM. I copied my code below. I read many articles about how to release the COM objects that I create. The code below runs just fine...
4
by: indrajith_varma1 | last post by:
Hi I am working on an application to retrieve data from excel and insert into SQL Server database. Yesterday, I faced a strange problem. In a column in the excel, if 1st 8 rows are number, then...
4
by: =?Utf-8?B?THluZXJz?= | last post by:
Hello All, We have a VB.NET application writen using VS 2003. This application apens an excel file from a vendor, reads the data and performs whatever functions it needs. We recently upgraded our...
3
by: keirnus | last post by:
hello, been searching for a solution on this but all i get are advices for importing data from excel directly to MS Access DB. my case here is different...the sheet in Excel file is not ready...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.