12 Replies - 5865 Views - Last Post: 16 March 2013 - 02:43 AM Rate Topic: -----

#1 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Read data from sql database using datetime picker

Posted 02 February 2013 - 02:02 AM

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 ""'

Is This A Good Question/Topic? 0
  • +

Replies To: Read data from sql database using datetime picker

#2 <aris>  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 24-August 11

Re: Read data from sql database using datetime picker

Posted 02 February 2013 - 03:35 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Read data from sql database using datetime picker

Posted 12 March 2013 - 02:22 AM

View Post<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'
Was This Post Helpful? 0
  • +
  • -

#4 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Read data from sql database using datetime picker

Posted 12 March 2013 - 03:22 AM

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.
Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Read data from sql database using datetime picker

Posted 12 March 2013 - 04:06 AM

... WHERE AT_Date = '3/12/2013' ORDER BY ...
Was This Post Helpful? 0
  • +
  • -

#6 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Read data from sql database using datetime picker

Posted 12 March 2013 - 06:43 AM

View PostCharlieMay, on 12 March 2013 - 04:06 AM, said:

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

Actually i am using a datetime picker...
Was This Post Helpful? 0
  • +
  • -

#7 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Read data from sql database using datetime picker

Posted 12 March 2013 - 06:49 AM

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?
Was This Post Helpful? 0
  • +
  • -

#8 harrybow5  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 03-November 08

Re: Read data from sql database using datetime picker

Posted 12 March 2013 - 06:56 AM

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 



Was This Post Helpful? 0
  • +
  • -

#9 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Read data from sql database using datetime picker

Posted 12 March 2013 - 06:58 AM

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.
Was This Post Helpful? 0
  • +
  • -

#10 harrybow5  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 03-November 08

Re: Read data from sql database using datetime picker

Posted 12 March 2013 - 08:47 AM

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 =]
Was This Post Helpful? 0
  • +
  • -

#11 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Read data from sql database using datetime picker

Posted 14 March 2013 - 01:49 AM

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

View Postharrybow5, 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...
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Read data from sql database using datetime picker

Posted 14 March 2013 - 04:30 AM

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?
Was This Post Helpful? 0
  • +
  • -

#13 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Read data from sql database using datetime picker

Posted 16 March 2013 - 02:43 AM

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....
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1