how to search a record in database through textbox and display it in a listview

Question

so i have this simple system that adds update's and delete records from the database.. i want to search data in database.. I've used some codes that i know but it doesn't work.. is there any other codes for searching a data in the database and display it in the list view//??. (sorry for the bad English) enter code here

this is the query that i used for search

Dim SqlQuery As String = "SELECT * FROM sample WHERE FirstName like ' % " & TextBox4.Text & " % '

and this is the code i used to display it in listview"

    ListView1.Items.Clear()
    For i = 0 To TABLE.Rows.Count - 1
       Dim li As new ListViewItem
            li=ListView1.Items.Add(TABLE.Rows(i)("ID").ToString())
            li.SubItems.Add(TABLE.Rows(i)("FirstName").ToString())
            li.SubItems.Add(TABLE.Rows(i)("MiddleName").ToString())
            li.SubItems.Add(TABLE.Rows(i)("LastName").ToString())
            li.SubItems.Add(TABLE.Rows(i)("Gender").ToString())
    Next

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

The first thing to do is to remove that string concatenation that leads to Sql Injections

Dim SqlQuery As String = "SELECT * FROM sample WHERE FirstName like @fName"
Dim cmd = new SqlCommand(SqlQuery, connection)
cmd.Parameters.AddWithValue("@fName", "%" & TextBox4.text & "%"

Then notice how the wildcards for the LIKE clause are concatenated to the TextBox value without spaces before the value (and this is the original problem in your query)

Using a parameterized query, as above, will avoid Sql Injections. Sql Injection is a major problem and with no end in sight because the programs that use this naive method to build strings are innumerable.

But a part from that, string concat is a curse also for another problem. What if the textbox contains a single quote? The result will be a Syntax Error because the single quote inside the textbox will be interpreted as the closing quote of the LIKE value, leaving the remainder of the textbox as an invalid sql statement. Of course you could apply a Replace("'", "''") to every string and multiply that code for 10 or 20 string parameters and then look at the wonderful mess that arises.

Give me a parameterized SQL or give me death

score 0 · Answer 2 · answered Jan 30 '14 at 16:13

If you are not interesed about Sql Injections you can use this

Imports System.Windows.Forms

Public Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load


    Dim ds As New DataSet
    Dim sqladap As New SqlClient.SqlDataAdapter(" SELECT * FROM sample WHERE FirstName like '%" + TextBox4.text + "%'", <yourconnection>)
    sqladap.Fill(ds)

    For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
        ListView1.Columns.Add(ds.Tables(0).Columns(i).ToString())
    Next
    For i As Integer = 0 To ds.Tables(0).Rows.Count - 1

        Dim listRow As New ListViewItem
        listRow.Text = ds.Tables(0).Rows(i)(0).ToString()
        For j As Integer = 1 To ds.Tables(0).Columns.Count - 1
            listRow.SubItems.Add(ds.Tables(0).Rows(i)(j).ToString())
        Next
        ListView1.Items.Add(listRow)
    Next
End Sub

how to search a record in database through textbox and display it in a listview

2 Answers2