Why SQL Server datetime datatype and datetimepicker search no result on VB.NET?

Question

I am doing a function to search for records between 2 date on VB.NET. But I'm not sure is the conversion error or what the result is not showing. I store the date and time in database with DATETIME datatype, and I compare it with the datetimepicker of VB.NET. I guess it is because the datetime store in database include the date and the datatimepicker do not have time in it so the comparison to search the records will never be appear since the comparison is wrong because the datetimepicker do not have time in it.

Here's the code. The dateSelectFrom.value and dateSelectTo.value is the value of the datetimepicker

        If rbFirstDate.Checked Then
            Dim ds1 As String = Format(dateSelectFrom.Value, "YYYY-MM-DD")
            cmd.CommandText = "SELECT OrderList.Id, OrderList.timeOrder,OrderDetail.foodID, Food.foodName,OrderDetail.qty, Food.price, OrderDetail.subtotal FROM OrderDetail INNER JOIN Food on OrderDetail.foodID = Food.Id INNER JOIN OrderList on OrderDetail.orderID = OrderList.Id WHERE (timeOrder = CONVERT(DATETIME, '" & ds1 & "', 102)) order by timeOrder"
        End If

        If rbBetweenDate.Checked Then
            Dim ds1 As String = Format(dateSelectFrom.Value, "YYYY-MM-DD")
            Dim ds2 As String = Format(dateSelectTo.Value, "YYYY-MM-DD")
            cmd.CommandText = "SELECT OrderList.Id, OrderList.timeOrder,OrderDetail.foodID, Food.foodName,OrderDetail.qty, Food.price, OrderDetail.subtotal FROM OrderDetail INNER JOIN Food on OrderDetail.foodID = Food.Id INNER JOIN OrderList on OrderDetail.orderID = OrderList.Id WHERE (timeOrder BETWEEN CONVERT(DATETIME, '" & ds1 & "', 102) AND CONVERT(DATETIME, '" & ds2 & "', 102)) order by timeOrder"
        End If

I had try to put the time format behind the date but it shows error conversion of datetime from string. Which the code looks like this :

Dim ds1 As String = Format(dateSelectFrom.Value, "YYYY-MM-DD HH:MI:SS")

Am I doing the conversion the wrong way or how do I assign the time to the datetimepicker.value? I'm kinda confused by this datetimepicker and DATETIME datatype.

Don't **inject** the values, parametrise your query and pass actual `datetime` values not strings. Fix the injection issue, fix the resulting behaviour. — Thom A, Dec 01 '20 at 14:31
"YYYY-MM-DD" is not a valid format string in C#. It should be "yyyy-MM-dd". However the comment above is the right way to use a value in a query. Use a parameter and pass it in your SqlCommand Parameters collection. — Steve, Dec 01 '20 at 14:36
The OP is using VB.Net, not C# @Steve, though pretty sure that the point about the format is still correct. — Thom A, Dec 01 '20 at 14:39
@Steve Ya Steve, I am sure that the format is still correct. But how do I put the datetimepicker value into a parameterize? — Marcus Neo, Dec 01 '20 at 14:42
[How do I create a parameterized SQL query? Why Should I?](https://stackoverflow.com/q/542510/2029983) — Thom A, Dec 01 '20 at 14:43
@MarcusNeo there are millions examples about _parameterized queries in VB.NET_ No need to add another. Look around and, if you have problems, post again — Steve, Dec 01 '20 at 14:43
cmd.Parameters.Add("@fromTime", sqlDbType.DateTime).Value = dateSelectFrom.Text. I am not sure if this is correct because but probably works. Let me try ya. Thanks Steve and Larnu. Will update here if still got any problem. — Marcus Neo, Dec 01 '20 at 14:46
Not Text, Value. Text is a string and it is subject again to a conversion without you having any say how the conversion is done. Instead you should pass directly a DateTime and the server will know how to read and handle that value — Steve, Dec 01 '20 at 14:47
@Steve Same, nothing changes I am using the parameterized and still couldn't match the date. — Marcus Neo, Dec 01 '20 at 14:53
Run [SQL Server Profiler](https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/start-sql-server-profiler?view=sql-server-ver15) to check what exact sql is executed on your server and with what parameters. — Alex B., Dec 01 '20 at 14:56
Please update the question (do not replace the original text) adding your current attempt. — Steve, Dec 01 '20 at 15:28

score 0 · Answer 1 · answered Dec 01 '20 at 15:33

You should parameterize your query. Not only would this fix potential SQL injection vulnerabilities, but it will likely fix your underlying issue too.

Take a look at this example:

Dim commandString As String = "
    SELECT
        OrderList.Id,
        OrderList.timeOrder,
        OrderDetail.foodID,
        Food.foodName,
        OrderDetail.qty,
        Food.price,
        OrderDetail.subtotal
    FROM
        OrderDetail
    INNER JOIN Food ON
        OrderDetail.foodID = Food.Id
    INNER JOIN OrderList ON
        OrderDetail.orderID = OrderList.Id
    WHERE
        {{where}}
    ORDER BY
        OrderList.timeOrder;"
If (rbFirstDate.Checked) Then
    commandString = commandString.replace("{{where}}", "OrderList.timeOrder = CONVERT(DATETIME, @0, 102)")
ElseIf rbBetweenDate.Checked Then
    commandString = commandString.replace("{{where}}", "OrderList.timeOrder BETWEEN CONVERT(DATETIME, @0, 102) AND CONVERT(DATETIME, @1, 102)")
End If

Dim con As OleDbConnection
Try
    con = New OleDbConnection("My Connection String Here")

    Using cmd As OleDbCommand = New OleDbCommand(commandString, con)
        ' parameterize the query here
        cmd.Parameters.Add("@0", OleDbType.Date).Value = dateSelectFrom.Value
        If rbBetweenDate.Checked Then
            cmd.Parameters.Add("@1", OleDbType.Date).Value = dateSelectTo.Value
        End If

        con.Open()

        ' do something with cmd here

        con.Close()
    End Using
Catch ex As Exception
    Console.WriteLine(ex.Message)
Finally
    If con IsNot Nothing Then
        If con.State = ConnectionState.Open Then
            ' Close the connection if it was left open(exception thrown)
            con.Close()
        End If
        con.Dispose()
    End If
End Try

Obviously if you're using SQL Server then swap out the OLEDB namespace, but this is the basic gist.

Tag says sql-server not Access. – Mary Dec 01 '20 at 19:53 — Mary, Dec 01 '20 at 19:53
I missed the tag, but the principles apply nonetheless. – David Dec 01 '20 at 19:54 — David, Dec 01 '20 at 19:54
@Mary is @0 not a named parameter? – David Dec 01 '20 at 20:12 — David, Dec 01 '20 at 20:12
Very sorry, didn't catch it in the Select. – Mary Dec 01 '20 at 20:13 — Mary, Dec 01 '20 at 20:13

score 0 · Answer 2 · answered Dec 01 '20 at 20:37

The .Value property of a DateTimePicker returns a DataTime. Don't change it to a string and then have the database change it back to a date. The use of parameters should line up your .net DateTime with the Sql Server DateTime.

Private Function GetOrders() As DataTable
    Dim dt As New DataTable
    Dim sb As New StringBuilder

    sb.Append("SELECT 
                        OrderList.Id, 
                        OrderList.timeOrder,
                        OrderDetail.foodID, 
                        Food.foodName,
                        OrderDetail.qty, 
                        Food.price, 
                        OrderDetail.subtotal 
                FROM OrderDetail 
                INNER JOIN Food on OrderDetail.foodID = Food.Id 
                INNER JOIN OrderList on OrderDetail.orderID = OrderList.Id 
                WHERE 1 = 1")
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand()
        cmd.Connection = cn
        'this parameter will be used if either radio button is checked
        cmd.Parameters.Add("@FirstDate", SqlDbType.DateTime).Value = dateSelectFrom.Value
        If rbFirstDate.Checked Then
            sb.AppendLine(" And timeOrder = @FirstDate")
        ElseIf rbBetweenDate.Checked Then
            sb.AppendLine(" And timeOrder BETWEEN @FirstDate AND @SecondDate")
            cmd.Parameters.Add("@SecondDate", SqlDbType.DateTime).Value = dateSelectTo.Value
        End If

        sb.AppendLine(" Order By timeOrder;")
        Debug.Print(sb.ToString) 'Just to check if everthing was added properly
        cmd.CommandText = sb.ToString
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    Return dt
End Function

Why SQL Server datetime datatype and datetimepicker search no result on VB.NET?

2 Answers2