VS2008 (VB) - Retrieving records from Access DB By DATE

[Patrick Pirtle]

Having programmed for a number of years with VB6 (but very little
database stuff), I'm trying to learn Visual Studio, but am just about
at the end of my rope as far as wrapping my mind around DataSets,
TableAdapters, DataViews, etc.

1. I have an Access database. One of the fields ("StartDate") is
defined as a Date.



2. In my app, I have created a DataSet with a TableAdapter Query as
follows:

SELECT ProjectNumber, Hours, StartDate, EndDate, StartTime,
FROM Assignments WHERE StartDate < lastDay

Ultimately, my goal is to select rows where the "StartDate" is less
than what I pass as "lastDay" and "EndDate" is greater than what I
pass as "firstDay."



3. I've created parameters ("firstDay" and "lastDay") for this query
that are "Date" DbType.



4. Just to see if I'm close, I'm trying to retrieve the data with this
code:

Private Sub getAssignments()
Dim firstDay as String
Dim lastDay As String
firstDay = "#2008-09-08#"
lastDay = "#2008-09-11#"

AssignmentsTableAdapter.FillByAssignments(DraftingScheduleDataSet.Assignments,
firstDay, lastDay)

'Create a dataview to work with the retrieved data
Dim dvAssignments As DataView
dvAssignments = New
DataView(DraftingScheduleDataSet.Assignments)

For Each row In DraftingScheduleDataSet.Assignments
Debug.Print(row("ProjectNumber"))
Next row

End Sub


and I'm getting...nothing. Obviously, I don't know what I'm doing.



I went to theDataSet view and did a "Preview Data" on this query. If
I put two dates in the "firstDay" and "lastDay" parameters in the form
"#2008-09-11#", if returns rows where the value of "StartDate" is less
than "firstDay" (Although the query is using "lastDay"!!!!)



So, my questions are:

1. Can anyone clarify some of my obvious mistakes so that I can learn
what I'm doing?

2. I found this EXAMPLE code on the MSDN site:

'Private Sub ReadOrderData(ByVal connectionString As String)
' Dim queryString As String = "SELECT OrderID, CustomerID FROM
dbo.Orders;"

' Using connection As New SqlConnection(connectionString)
' Dim command As New SqlCommand(queryString, connection)
' connection.Open()

' Dim reader As SqlDataReader = command.ExecuteReader()

' ' Call Read before accessing data.
' While reader.Read()
' Console.WriteLine(String.Format("{0}, {1}", _
' reader(0), reader(1)))
' End While

' ' Call Close when done reading.
' reader.Close()
' End Using
'End Sub

Does this imply I could skip the DataSets, TableAdapters, DataViews,
etc., altogether and do something similar to what I used to do with
VB6?

3. If the answer to question #2 is "yes," then *should* I go that
route, or should I persevere with learning about DataSets, etc.?



Thanks, in advance, for your comments and suggestions.

 

[Armin Zingler]

Having programmed for a number of years with VB6 (but very little
database stuff), I'm trying to learn Visual Studio, but am just
about at the end of my rope as far as wrapping my mind around
DataSets, TableAdapters, DataViews, etc.

1. I have an Access database. One of the fields ("StartDate") is
defined as a Date.



2. In my app, I have created a DataSet with a TableAdapter Query as
follows:

SELECT ProjectNumber, Hours, StartDate, EndDate, StartTime,
FROM Assignments WHERE StartDate < lastDay

Ultimately, my goal is to select rows where the "StartDate" is less
than what I pass as "lastDay" and "EndDate" is greater than what I
pass as "firstDay."



3. I've created parameters ("firstDay" and "lastDay") for this query
that are "Date" DbType.



4. Just to see if I'm close, I'm trying to retrieve the data with
this code:

Private Sub getAssignments()
Dim firstDay as String
Dim lastDay As String
firstDay = "#2008-09-08#"
lastDay = "#2008-09-11#"

What if you enable Option Strict and declare firstDay and lastDay As Date?
Then you are also able to use a DateTime literal:

Dim firstDay = #09-08-2008#
Dim lastDay = #09-11-2008#

This avoids unnecessary conversions. The table field type is Date anyway.

Back to your problem: Using the Jet OleDB Provider, the parameters are not
determined by name but by position. So it must be "...WHERE StartDate < ?"

The parameters must be added at the same position as they occur in the SQL.

' Dim reader As SqlDataReader = command.ExecuteReader()

Does this imply I could skip the DataSets, TableAdapters,
DataViews, etc., altogether and do something similar to what I used
to do with VB6?

You can use the DataReader if you have to process the data sequentially
without storing it in memory or if you intend to store it somewhere else
than in a DataSet. Otherwise, a TableAdapter makes this process easier to
use.

3. If the answer to question #2 is "yes," then *should* I go that
route, or should I persevere with learning about DataSets, etc.?

You will need DataSets earlier or later.


Armin

 

[Miro]

Are you sure your dataset actually has data in it?
I beleiev your issue might be is that your "StartDate" column is a DateField
in the database, and you are sending a 'string' field in to compare it to.

(written in notepad )

'If its an access database, your table adapter uses parameters with a
question mark
SELECT ProjectNumber, Hours, StartDate, EndDate, StartTime,
FROM Assignments WHERE (StartDate = ?)


'In SQL you use the @Parameter. But in Access, the ? is used, and you must
supply the paramters in order.

Dim TempCurrentDate AS Date = NOW()

AssignmentsTableAdapter.FillByAssignments(DraftingScheduleDataSet.Assignments,
TempCurrentDate )

 

[Cor Ligthert[MVP]]

Patrick,

Maybe this will help you

http://www.vb-tips.com/OleDBParameters.aspx

Cor