Problem with SELECT statement

[mrbungle]

Hey,

I need to pick from two date ranges from a couple of datetimepickers in the form. Then when I run the query, it should clear the current data in the listview, run the query, then re-populate the listview.

I use the same code to simply load the listview, and similar code to delete from the MySQL and reload the listview and everything works great. But this one isn't working- it doesn't generate any errors, but also doesn't sort either.

Can someone take a look and double check it for me? I suspect something is wrong.

Public Sub SortByDateTraining()

        MySqlConnection.ConnectionString = x.urlMySQLDatabase1
        Try
            MySqlConnection.Open()
        Catch ex As Exception
            MsgBox("It looks like your internet is taking a second to pull the required data... ")
        End Try

        ' Clear the items in the listview to reload them from the query below
        frmMain.lvTraining.Items.Clear()

        Dim sqlQuery As String = "SELECT * FROM Training WHERE Training.startdate =  '" & frmMain.dtpTrainingStart.Text & "' AND Training.enddate = '" & frmMain.dtpTrainingEnd.Text & "'"

        Dim sqlAdapter As New MySqlDataAdapter
        Dim sqlCommand As New MySqlCommand
        Dim TABLE As New DataTable

        With sqlCommand
            .CommandText = sqlQuery
            .Connection = MySqlConnection
        End With

        With sqlAdapter
            .SelectCommand = sqlCommand
            .Fill(TABLE)
        End With

        For i = 0 To TABLE.Rows.Count - 1
            With frmMain.lvTraining
                .Items.Add(TABLE.Rows(i)("number"))
                With .Items(.Items.Count - 1).SubItems
                    .Add(TABLE.Rows(i)("startdate"))
                    .Add(TABLE.Rows(i)("enddate"))
                    .Add(TABLE.Rows(i)("location"))
                    .Add(TABLE.Rows(i)("traininghours"))
                    .Add(TABLE.Rows(i)("trainingtype"))
                    .Add(TABLE.Rows(i)("trainingname"))
                    .Add(TABLE.Rows(i)("certification"))
                    .Add(TABLE.Rows(i)("expiration"))
                End With

            End With
        Next
        CalculateTrainingHours()
        MySqlConnection.Close()

    End Sub

[lolafuertes]

Please, try to add

frmMain.lvTraining.Refresh()

just before the End Sub.

Hope this helps

[mrbungle]

Thanks- I just tried it and while it refreshes, the selected date range isn't coming through.

[adam_k]

Is your intention to select only the records where the training start date is EQUAL to the date in your relative datepicker and enddate is EQUAL to the second datepicker?
If you are looking for records with start and end date between the 2 datepickers then you need to change your SQL statement.

PS: You might want to validate the dates that you are reading from the form. You could end up running a query with end date smaller than the start date or with 1/1/1900 as date.

[mrbungle]

My intention is to pick any records that fall within (anything in between) the start date and end date, not specifically EQUAL to the two dates.

As for validation, you are correct. That will be my second hurdle to get over. I'll probably ask about that later.

You may just need to change your = sign to >= startdate and <= enddate

Heres an sqlce example. should work exactly the same with mysql.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim Connection As New SqlCeConnection("TheConnectionString")
        Dim Command As SqlCeCommand = Connection.CreateCommand
        Dim Reader As SqlCeDataReader
        Dim ListViewItem As ListViewItem

        Command.Parameters.AddWithValue("@StartDate", DateTimePickerTrainingStart.Value.ToShortDateString)
        Command.Parameters.AddWithValue("@EndDate", DateTimePickerTrainingEnd.Value.ToShortDateString)

        Command.CommandText = "Select *" & vbCrLf & _
                              "From Training" & vbCrLf & _
                              "Where (Training.startdate >= @StartDate)" & vbCrLf & _
                                "And (Training.enddate <= @EndDate)"

        ListViewTraining.BeginUpdate()
        ListViewTraining.Items.Clear()

        Try
            Connection.Open()
            Reader = Command.ExecuteReader
            Do While Reader.Read
                ListViewItem = ListViewTraining.Items.Add(CStr(Reader("Number")))
                ListViewItem.SubItems.Add(CStr(Reader("StartDate")))
                ListViewItem.SubItems.Add(CStr(Reader("EndDate")))
            Loop
            Reader.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Connection.Dispose()
            Command.Dispose()
        End Try

        ListViewTraining.EndUpdate()

    End Sub

I added the parameters with the .ToShortDateString so it would remove the time portion of the value. If your table entries contain the time then you would need to adjust the EndDate part to < the endate plus one day. If your table enddate is 5/1/2011 1:35 pm then <= 5/1/2011 12:00 am. would not work because its greater than the enddate. You would need to make it < 5/2/2011. If that makes any sense.

[mrbungle]

Absoultly brilliant! You solution worked exactly as needed.

The suggestion to "You may just need to change your = sign to >= startdate and <= enddate" is what it took. Here is the working statement-

Dim sqlQuery As String = "SELECT * FROM Training WHERE Training.startdate >=  '" & frmMain.dtpTrainingStart.Text.ToString & "' AND Training.enddate <= '" & frmMain.dtpTrainingEnd.Text.ToString & "'"

Thanks everyone for their time!