[thanzeem7]

How to read data from database using datetimepicker with Textchange event. I have a datetime picker 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

Dim editdate As Date
editdate = DTPEDITAT.Value     
Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = (%" & editdate & "%)ORDER BY EMP_NAME ASC"

But it shows error 'Incorrect Syntax near ""'

[<aris>]

You need to use the LIKE operator in the WHERE clause to search for a specified pattern in a column.

E.g.

Dim editdate As Date
editdate = DTPEDITAT.Value    
Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE LIKE %" & editdate.value & "% ORDER BY EMP_NAME ASC"

This post has been edited by <aris>: 02 February 2013 - 03:49 AM

[thanzeem7]

<aris>, on 02 February 2013 - 03:35 AM, said:

You need to use the LIKE operator in the WHERE clause to search for a specified pattern in a column.

E.g.

Dim editdate As Date
editdate = DTPEDITAT.Value    
Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE LIKE %" & editdate.value & "% ORDER BY EMP_NAME ASC"

It shows Error, Incorrect Syntax near '12'

[thanzeem7]

I give my full code... Check this code and give a solution

Private Sub BTNFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNFIND.Click
    getConnect()
    Dim editdate As Date
    editdate = DTPEDITAT.Value.Date
    MessageBox.Show(editdate)
    Try
        Conn.Open()
        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE LIKE %" & 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

I need to read data from database with the value of datetimepicker. And display these data in a dtagridview.

[CharlieMay]

... WHERE AT_Date = '3/12/2013' ORDER BY ...

[thanzeem7]

CharlieMay, on 12 March 2013 - 04:06 AM, said:

... WHERE AT_Date = '3/12/2013' ORDER BY ...

Actually i am using a datetime picker...

[CharlieMay]

Yea, I get that, but that is how the query should look

so if you were to include your datetimepicker's date as a placeholder in the query, that is the format it would need to be submitted as.

what you currently have is
...WHERE AT_DATE LIKE %3/12/2013% ORDER BY...

We're looking at the result of the string being built, not the code to build it.

Also, you're storing that date as a Date datatype in your database right?

[harrybow5]

WHERE AT_Date = '3/12/2013' ORDER BY ...

WHERE AT_Date = '" & datetimepicker.value & "' ORDER BY

i believe however i have had alot of problem using datetimepickers with a SQL database

i recommend not running your query to begin with and just constructing the command string and bring the command string up in a messagebox to check that the command string is correct.

Private Sub BTNFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNFIND.Click  

    getConnect()  

    Dim editdate As Date 

    editdate = DTPEDITAT.Value.Date 

     MessageBox.Show(editdate)  

    Try 

         Conn.Open()  

        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE LIKE %" & editdate & "% ORDER BY EMP_NAME ASC" 

           messagebox.show(strSQL)

     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 

i recommend not running your query to begin with and just constructing the command string and bring the command string up in a messagebox to check that the command string is correct.

Private Sub BTNFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNFIND.Click  

    getConnect()  

    Dim editdate As Date 

    editdate = DTPEDITAT.Value.Date 

     MessageBox.Show(editdate)  

    Try 

         Conn.Open()  

        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_Date = '" & datetimepicker.value & "' ORDER BY EMP_NAME ASC" 

           messagebox.show(strSQL)

     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 

[CharlieMay]

I can assure you using a DateTimePicker has nothing to do with Querying an SQL database. The only problem would be how you use it and how you have your table set up.

[harrybow5]

the table was not constructed by myself so i fear you might be right with the delemia i was having however i did find a work around that worked quiet well if anything it worked better =]

[thanzeem7]

Friends..
I change datatype date time to String. Now there is no error. But the new problem is nothing displayed in Datagridview. I give Messagebox after sql string, The variable part shows currect data from datetime picker..

harrybow5, on 12 March 2013 - 06:56 AM, said:

WHERE AT_Date = '3/12/2013' ORDER BY ...

WHERE AT_Date = '" & datetimepicker.value & "' ORDER BY

i believe however i have had alot of problem using datetimepickers with a SQL database

i recommend not running your query to begin with and just constructing the command string and bring the command string up in a messagebox to check that the command string is correct.

Private Sub BTNFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNFIND.Click  

    getConnect()  

    Dim editdate As Date 

    editdate = DTPEDITAT.Value.Date 

     MessageBox.Show(editdate)  

    Try 

         Conn.Open()  

        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE LIKE %" & editdate & "% ORDER BY EMP_NAME ASC" 

           messagebox.show(strSQL)

     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 

i recommend not running your query to begin with and just constructing the command string and bring the command string up in a messagebox to check that the command string is correct.

Private Sub BTNFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNFIND.Click  

    getConnect()  

    Dim editdate As Date 

    editdate = DTPEDITAT.Value.Date 

     MessageBox.Show(editdate)  

    Try 

         Conn.Open()  

        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_Date = '" & datetimepicker.value & "' ORDER BY EMP_NAME ASC" 

           messagebox.show(strSQL)

     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 

I give Messagebox. It shows correctly... But now the problem is There is nothing displayed in the Datagridview...

[CharlieMay]

Look at what your datetimepicker value shows at the time of the query. The ENTIRE value it shows.

Look at the record you expect it to pull and look at what AT_Date holds The ENTIRE field.

is the ENTIRE value displayed in the dtp she exact same as the ENTIRE value in the field?

[thanzeem7]

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....