Read data from sql database using datetime picker

Question

How to read data from database using datetimepicker value. I have a datetimepicker and a datagridview in my form. I want to get data from Sql databse table with the selected datetimepicker value. I try with this code

Private Sub BTNFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNFIND.Click
    getConnect()
    Dim editdate As String
    DTPEDITAT.Value= Format(DTPEDITAT.Value, "dd/MM/yyyy")
    editdate = DTPEDITAT.Value
    MessageBox.Show(editdate)
    Try
        Conn.Open()
        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = '" & editdate & "' ORDER BY EMP_NAME ASC"
        Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL, Conn)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "ATTENDANCE")
        Dim dt As DataTable = ds.Tables("ATTENDANCE")
        Dim row As DataRow
        Dim atstat As String
        For Each row In dt.Rows
            If row("AT_STATUS") = 1 Then
                atstat = "Present"
            ElseIf row("AT_STATUS") = 0 Then
                atstat = "Absent"
            ElseIf row("AT_STATUS") = 0.5 Then
                atstat = "Halfday"
            Else
                atstat = "Error"
            End If
            For x As Integer = 0 To ATCEDITGRID.Rows.Count - 1
                ATCEDITGRID.Rows(x).Cells(2).Value = row("EMP_ID")
                ATCEDITGRID.Rows(x).Cells(3).Value = row("EMP_NAME")
                ATCEDITGRID.Rows(x).Cells(0).Value = atstat
                ATCEDITGRID.Rows(x).Cells(1).Value = row("AT_REMARK")
            Next x
        Next row
    Catch ex As SqlException
        MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
    End Try
End Sub

Datagridview not display anything and there is no error...

Answer 1

I change my code like this

Private Sub BTNFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNFIND.Click
    ATCEDITGRID.Rows.Clear()
    getConnect()
    Dim editdate As String
    DTPEDITAT.Value = Format(DTPEDITAT.Value, "dd/MM/yyyy")
    editdate = DTPEDITAT.Value
    Try
        Conn.Open()
        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = '" & editdate & "' ORDER BY EMP_NAME ASC"
        Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL, Conn)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "ATTENDANCE")
        Dim dt As DataTable = ds.Tables("ATTENDANCE")
        Dim row As DataRow
        Dim atstat As String
        For Each row In dt.Rows
            If row("AT_STATUS") = 1 Then
                atstat = "Present"
            ElseIf row("AT_STATUS") = 0 Then
                atstat = "Absent"
            ElseIf row("AT_STATUS") = 0.5 Then
                atstat = "Halfday"
            Else
                atstat = "Error"
            End If
            Me.ATCEDITGRID.Rows.Add(row("EMP_ID"), row("EMP_NAME"), atstat, row("AT_REMARK"))
        Next row
        ATCEDITGRID.TopLeftHeaderCell.Value = "Sr.No."
        Me.ATCEDITGRID.RowHeadersDefaultCellStyle.Padding = New Padding(3)
        ATCEDITGRID.AllowUserToAddRows = False
        AddRowHeadersEdit()
        Conn.Close()
    Catch ex As SqlException
        MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
    End Try
End Sub

Itz working Good....

Answer 2

We see these questions here every day. They stems from the same problem.

NEVER USE STRING CONCATENATION TO BUILD SQL QUERIES.

It's a big problem. Of course you have already met the first effect. How to convert strings in an acceptable way to the effective data type? You need to solve parsing problems with embedded quotes, correct representation of dates and decimal numbers for the underlying database system. But the second and more subtle side effect of string concatenation is SQL Injection (This is just an instructive link because SQL Injection is a very large topic)

To solve this kind of problems the only accepted way is to use PARAMETERS.
This means that it is the database engine that solves in an efficient way the question, you need a string with parameter placeholders (the @something) and a collection of Parameters with the exact data type for the value of the parameter.

So your code should change in this way

Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK " + 
                       "FROM ATTENDANCE WHERE AT_DATE = @editdate " + 
                       "ORDER BY EMP_NAME ASC"
Using con = new SqlConnection("constring_here")
    con.Open()
    Using cmd = new SqlCommand(strSQL, con)
        cmd.Parameters.AddWithValue("@editdate", DTPEDITAT.Value)
        ' do whatever you want with the command '
        ' like ExecuteReader or use a DataAdapter to fill datasets'
    End Using
End Using

Answer 3

this is my code its working

oldbc.CommandText = "select * from recette where " & ComboBox1.Text & " between #" & DateTimePicker1.Text & "# and #" & DateTimePicker2.Text & "#"

Answer 4

My NOT RECOMMENDED solution:

Remove the parenthesis and % characters. Put a space just before the ORDER (though it is not the cause of your syntax error).

Fix the select in this way:

"SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = '" & _
editDate.ToString("yyyy-MM-dd hh:mm:ss") & _
"' ORDER BY EMP_NAME ASC"

---

What I do recommmend you is to learn to use SQL Parameters.

The query should looks then like this (note the @editDate parameter place-holder inside):

"SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = @editDate ORDER BY EMP_NAME ASC".

Then you need just to add the parameters to the SqlCommand. The easiest way is to do that is to use SqlParameterCollection.AddWithValue.

yourSqlCommand.Parameters.AddWithValue("@editDate", editDate)

A complete sample:

    Dim editDate As Date = DTPEDITAT.Value

    Using conn As New SqlConnection(YOUR_CONNECTION_STRING_HERE)
        Using cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = @editDate ORDER BY EMP_NAME ASC"
            cmd.Parameters.AddWithValue("@editDate", editDate)

            adapter.SelectCommand = cmd
            adapter.Fill(ds)

            For Each row As DataRow In ds.Tables(0).Rows 
                [do whatever with the result]
            Next

        End Using
        conn.Close()
    End Using

From MSDN about Sql Parameters

Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code. This helps guard against "SQL injection" attacks, in which an attacker inserts a command into an SQL statement that compromises security on the server. In addition to the security benefits, parameterized commands provide a convenient method for organizing values passed to a data source.

Answer 5

Try move a one space near at your "%) and I corrected the query. Try to figure out.

Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS, " + _
     "AT_REMARK FROM ATTENDANCE " + _ 
     "WHERE Convert(Varchar(10), AT_DATE, 23) = " + _
     "('" & editdate.ToString("yyyy-MM-dd") & "') ORDER BY EMP_NAME ASC"