0

I am working on user module in my vb.net Windows Form application. So I have a SQL table that has three columns with data type "Date". I want to extract records that fall in specified date range and display them in a listview. My form looks like this:

enter image description here

For the apply button, I have written following function: 

'FUNCTION: APPLY_DATE_FILTER(): FILTER THE LIST ACCORDING TO SELECTED DATE COLUMN 
Private Sub apply_date_filter(ByVal radio_create As RadioButton, ByVal radio_lastlogin As RadioButton, ByVal radio_lastedit As RadioButton, ByVal date1 As DateTimePicker, ByVal date2 As DateTimePicker, ByVal list As ListView)
    Dim sel As String
    If list.Items.Count = 0 Then
        If radio_create.Checked = True Then
            sel = "SELECT * FROM user_master WHERE created BETWEEN '" & date1.Text & "' AND '" & date2.Text & "'"
        ElseIf radio_lastedit.Checked = True Then
            sel = "SELECT * FROM user_master WHERE lastchange BETWEEN '" & date1.Text & "' AND '" & date2.Text & "'"
        ElseIf radio_lastlogin.Checked = True Then
            sel = "SELECT * FROM user_master WHERE lastlogin BETWEEN '" & date1.Text & "' AND '" & date2.Text & "'"
        End If
        Dim cnn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RSMS_DB.mdf;Integrated Security=True;User Instance=True")
        Dim da As New SqlDataAdapter(sel, cnn)
        Dim ds As New DataSet
        da.Fill(ds)
        If ds.Tables(0).Rows.Count = 0 Then
            MsgBox("There are no users found within the date-range you specified! Please try again with a valid entry!", MsgBoxStyle.OkOnly, "USER NOT FOUND")
        Else
            For m As Integer = 0 To ds.Tables(0).Rows.Count - 1
                Dim lvi As New ListViewItem
                lvi.Text = ds.Tables(0).Rows(m)(0).ToString
                lvi.SubItems.Add(ds.Tables(0).Rows(m)(1).ToString)
                lvi.SubItems.Add(ds.Tables(0).Rows(m)(2).ToString)
                lvi.SubItems.Add(ds.Tables(0).Rows(m)(5).ToString)
                lvi.SubItems.Add(ds.Tables(0).Rows(m)(7).ToString)
                lvi.SubItems.Add(ds.Tables(0).Rows(m)(8).ToString)
                lvi.SubItems.Add(ds.Tables(0).Rows(m)(9).ToString)
                If ds.Tables(0).Rows(m)(10).ToString = "1" Then
                    lvi.SubItems.Add("Administrator")
                ElseIf ds.Tables(0).Rows(m)(10).ToString = "0" Then
                    lvi.SubItems.Add("Ordiary")
                End If
                list.Items.Add(lvi)
            Next
        End If
    End If
End Sub

When I click apply, I get an error at da.Fill(ds) saying, "Conversion failed when converting date and/or time from character string." Thanks in advance. :)

Pratik Bhavsar
  • 1
  • 6
  • 1
    Use parameters in place of string concatenation and you should get a better result – Andrew Mortimer Feb 19 '18 at 17:34
  • 1
    If you have a look at the date formats for SQL queries, you'll immediately spot the problem. That said, if you were to use parameters in your query, this would not arise. – peterG Feb 19 '18 at 17:34
  • Yes, use parameters instead of string concatenation, string concatenation opens you up for Sql injection attacks. The data format for Sql Server is #mm/dd/yyyy# I believe, but as @peterG says, best to look that up. – Prescott Chartier Feb 19 '18 at 17:48
  • Alright, I solved the problem. All I had to do was to change the DateTimePicker format to "Short". The long format contained weekdays as well, that's why I was getting an error. Thanks anyways! :) – Pratik Bhavsar Feb 19 '18 at 17:57
  • Dates **Do Not** have a format - they are simply a value. Using parameters you should pass the `DateTimePicker.Value` - the NET DB providers know how to use a NET date. They'd be useless if they didnt – Ňɏssa Pøngjǣrdenlarp Feb 19 '18 at 18:45

0 Answers0