Displaying SQL to Data grid Access

Question

Hi guys i'm new to visual basic.net and i'm trying to display result of a query into a data grid view, i have the code below but it's giving me an error and highliting .FillTable below on the code, please guide me on how to print query into data grid. Thanks

Imports System.Data.OleDb

Public Class SearchForm
    Dim con As New OleDbConnection




    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Statd.SelectedIndexChanged

    End Sub


    Private Sub SearchButton_Click(sender As Object, e As EventArgs) Handles SearchButton.Click

        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= c:\Databse\Company_db.accdb"
        con.Open()


        Dim sqlQuery As String
        Dim sqlCommand As New OleDbCommand
        Dim sqlAdapter As New OleDbDataAdapter
        Dim Table As New DataTable
        Dim empNum As String
        Dim empLname As String
        Dim empDept As String
        Dim empStat As String


        empNum = eNumText.Text
        empLname = empLnameText.Text
        empDept = Deptd.Text
        empStat = Statd.Text

        'sqlQuery = "SELECT * FROM tbl_empinfo WHERE LastName like '+ empLnameText.Text +' "
        sqlQuery = "SELECT * FROM tbl_empinfo WHERE LastName like '+ empLnameText.Text +"

        ' MsgBox("Employee Number " + empNum + empLname + empDept + empStat) 'test statement 


        With sqlCommand
            .CommandText = sqlQuery
            .Connection = con

            With sqlAdapter
                .SelectCommand = sqlCommand
                .Fill(Table)

            End With

            For i = 0 To Table.Rows.Count - 1
                With DataGridView1
                    .Rows.Add(Table.Rows(i)("EmpID"), Table.Rows(i)("FirstName"), Table.Rows(i)("LastName"), Table.Rows(i)("Department"), Table.Rows(i)("Position"), Table.Rows(i)("Status"), Table.Rows(i)("Years"))

                End With
            Next


        End With






        con.Close()
    End Sub

Well, what is the error?? – OldProgrammer Mar 30 '14 at 21:47 — OldProgrammer, Mar 30 '14 at 21:47

score 2 · Answer 1 · edited May 23 '17 at 12:11

You have a a couple of errors in your string concatenation that forms the select statement

sqlQuery = "SELECT * FROM tbl_empinfo WHERE LastName like '" + empLnameText.Text + "'"

but this is not the correct way to query a database taking the user input. You need to use a parameterized query

sqlQuery = "SELECT * FROM tbl_empinfo WHERE LastName like ?"
With sqlCommand
    .CommandText = sqlQuery
    .Connection = con
    .Parameters.AddWithValue("@name", empLnameText.Text)
    With sqlAdapter
        .SelectCommand = sqlCommand
        .Fill(Table)
    End With
    With DataGridView1
        .DataSource = Table             
    End With
End With

Then you simply set the DataGridView1.DataSource to your table and you don't any loop to fill the grid

Using string concatenation is a bad practice because your code is an easy target for a Sql Injection attack (a very serious wvulnerability), but, also if your enpLName.Text contains a single quote, the string concatenation used will yeld an invalid sql statement.

Displaying SQL to Data grid Access

1 Answers1