Retrieve data from access database to data grid view in vb.net

Question

I try to read a data from access database with specific date to datagrid view in VB.NET. I use a datetime picker for that. Below code i used for retreive data. But i press the find button. nothing display in datagrid view. this is the code

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")
    'MessageBox.Show(DTPEDITAT.Value)
    'editdate = DTPEDITAT.Value
    Try
        Conn.Open()
        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = " & DTPEDITAT.Value & " ORDER BY EMP_NAME ASC"
        Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(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
            'MessageBox.Show(row("EMP_ID"))
            'MessageBox.Show(row("EMP_NAME"))
            'MessageBox.Show(atstat)
            'MessageBox.Show(row("AT_REMARK"))
            Me.ATCEDITGRID.Rows.Add(row("EMP_ID"))
            Me.ATCEDITGRID.Rows.Add(row("EMP_NAME"))
            Me.ATCEDITGRID.Rows.Add(atstat)
            Me.ATCEDITGRID.Rows.Add(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 OleDb.OleDbException
        MsgBox(ex.Message, MsgBoxStyle.Critical, "DB Error")
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
    End Try
End Sub

please check the code. and give me the solution

Steve · Accepted Answer · 2013-05-01 07:23:37Z

When you build a sql command concatenating strings you expose your code to two big problems.

First, you could write the wrong syntax for particular datatype on a particular database
Second, you expose your code to Sql Injection attacks from a malicious user (well, here should not be a concern, but....)

Instead build a parametrized query and let the framework code interact with the database engine in a secure and correct way

So you should write:

    Conn.Open()
    Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK " & _ 
                           "FROM ATTENDANCE WHERE AT_DATE = ? ORDER BY EMP_NAME ASC"
    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(strSQL, Conn)
    da.SelectCommand.Parameters.AddWithValue("@p1", DTPEDITAT.Value)
    Dim ds As DataSet = New DataSet
    da.Fill(ds, "ATTENDANCE")

Now looking at your code you could have another potential error in the way you treat the value of the field AT_STATUS. From your code it seems that the field is of type decimal or double. If your code compile then you have probably set the OPTION STRICT to OFF. It is a better practice to keep this option ON and convert appropriately the datatypes

    Dim dt As DataTable = ds.Tables("ATTENDANCE")
    Dim row As DataRow
    Dim atstat As String
    Dim status as Decimal

    For Each row In dt.Rows
        status = Convert.ToDecimal(row("AT_STATUS"))
        If status = 1 Then
            atstat = "Present"
        ElseIf status  = 0 Then
            atstat = "Absent"
        ElseIf status  = 0.5 Then
            atstat = "Halfday"
        Else
            atstat = "Error"
        End If
        ......
    Next row

Collectives™ on Stack Overflow

Retrieve data from access database to data grid view in vb.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related