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? 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?
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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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....
|
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...
|
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
|
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...
|
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$"...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |