The query, below runs but fails to yield results.
The table referenced is sitPlanMaster, which is part of the Access PlanMaster database. The field colTask is structured as a text field, colDueDate a date field, and colTaskID an integer field.
The table has 5 records. All records have the field colMeet set to True, all records have a colDueDate value, as well as a unique colTaskID value.
For connecting and manipulating the database, I use the connection methods shown below. For this application, the connection method is used for numerous other queries, to the same table and database. None have had any issues.
This code comes from a calendar application that I found and have used for several applications. Although much of the code for the calendar application is above my head, I generally understand how it works, and what it does. The original application was built to use with an Access database.
I have run this query method many times (both leading in and out) and followed it through without being able to find why no records are found. In every instance, the RecordCount = 0 and none of the 5 records that meet the criteria are found.
I use this query method with other applications and databases without issue. I copied this query method from another application, where the query runs and yields the expected results. The only difference between this application and the other is that the database used in the application I copied this from, is an SQL Express database. I don't believe that should be an issue, but it is a difference.
At this point, it seems to me that the only cause is that the query is wrong, but I just can't see where it is. When I run the query, I know that startDate and endDate both have the expected values and I get no errors, with regard to the query executing.
Attachment 188801Code:Private Sub AddAppointmentToFlDay(ByVal startDayAtFlNumber As Integer)
Dim startDate As DateTime = New Date(currentDate.Year, currentDate.Month, 1)
Dim endDate As DateTime = startDate.AddMonths(1).AddDays(-1)
PlanMaster.AddParam("@meet", 1)
PlanMaster.PlanMasterQuery("SELECT colTask,colDueDate,colTaskID " &
"FROM sitPlanMaster " &
"WHERE colMeet=@meet AND colDueDate BETWEEN '" & startDate.ToShortDateString &
"' AND '" & endDate.ToShortDateString & "'")
For Each Row As DataRow In PlanMaster.ListTable.Rows
Dim appDay As DateTime = DateTime.Parse(CStr(Row("colDueDate")))
Dim link As New LinkLabel
link.Tag = Row("colTaskID")
link.Name = $"link{Row("colTaskID")}"
link.Text = CStr(Row("colTask"))
AddHandler link.Click, AddressOf ShowAppointmentDetail
listFlDay((appDay.Day - 1) + (startDayAtFlNumber - 1)).Controls.Add(link)
Next
End Sub
Code:#Region "Connection Parameters"
Private PlanMasterConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PlanMaster.accdb;")
Public ListCommand As OleDbCommand
Public ListAdapter As OleDbDataAdapter
Public ListDataSet As DataSet
Public ListTable As DataTable
Public Params As New List(Of OleDbParameter)
Public Exception As String
#End Region
#Region "Data Connection"
Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New OleDbParameter(Name, Value)
Params.Add(NewParam)
End Sub
Public Sub PlanMasterQuery(SelectQuery As String)
RecordCount = 0
Exception = ""
#Region "Connection"
Try
PlanMasterConnection.Open()
ListCommand = New OleDbCommand(SelectQuery, PlanMasterConnection)
Params.ForEach(Sub(p) ListCommand.Parameters.Add(p))
Params.Clear()
ListTable = New DataTable
ListDataSet = New DataSet
ListAdapter = New OleDbDataAdapter(ListCommand)
RecordCount = ListAdapter.Fill(ListTable)
ListDataSet.Tables.Add(ListTable)
Catch ex As Exception
Exception = ex.Message
MsgBox(ex.Message + vbLf + vbCrLf + MyError)
Finally
MyError = ""
If PlanMasterConnection.State = ConnectionState.Open Then PlanMasterConnection.Close()
End Try
#End Region
End Sub
