By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

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

P: n/a
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(Drafting ScheduleDataSet.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.
Sep 12 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Patrick Pirtle mka >" <PPirtle <atdotcomschrieb
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

Sep 12 '08 #2

P: n/a
Patrick,

Maybe this will help you

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

Cor
"Patrick Pirtle mka >" <PPirtle <atdotcomschreef in bericht
news:e4********************************@4ax.com...
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(Drafting ScheduleDataSet.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.
Sep 13 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.