"AndyJ" <An***@discussions.microsoft.com> schreef in bericht
news:46F3BBFC-B1B3-43C0-BA46-C1C060C3812A@microsoft.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 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?ID=550279ec-6767-44ff-aaa3-eb8b44af0137
>
> I hope this helps,
>
> Cor
>
> "AndyJ" <An***@discussions.microsoft.com> schreef in bericht
> news:46F3BBFC-B1B3-43C0-BA46-C1C060C3812A@microsoft.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?
>
>
>