Results 1 to 6 of 6

Thread: [RESOLVED] SQL Search

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    19

    Resolved [RESOLVED] SQL Search

    I need so when someone enters a search term into a text box it will return the items from the sql server, I am using vb 2005. I can do it if they have the full word, but I need it so they can type part of a word(example...to search for the last name of miller, they can enter "miller", or "mil", etc..)

    they code below is from my search buton click event



    Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click
    ListView1.Items.Clear()
    Dim x As Integer = 0
    Dim searchtype As String = ""
    If rdoFirstName.Checked = True Then
    searchtype = "First_Name"
    ElseIf rdoLastName.Checked = True Then
    searchtype = "Last_Name"
    ElseIf rdoID.Checked = True Then
    searchtype = "id"
    End If
    Dim sqlcon As SqlConnection = New SqlConnection("Data Source=HOMER\SQLEXPRESS;Initial Catalog=theaterdb;Integrated Security=True")
    Dim sqlcomm As SqlCommand = New SqlCommand("SELECT id, first_name, last_name FROM employees WHERE " & searchtype & "= '" & txtSearch.Text & "'", sqlcon)
    sqlcon.Open()
    Dim sqlreader As SqlDataReader = sqlcomm.ExecuteReader
    While sqlreader.Read()
    id = CInt(sqlreader("id"))
    firstName = sqlreader("first_name").ToString
    LastName = sqlreader("last_name").ToString
    ListView1.Items.Add(id)
    ListView1.Items(x).SubItems.Add(firstName)
    ListView1.Items(x).SubItems.Add(LastName)
    x = x + 1
    End While
    sqlreader.Close()
    sqlcon.Close()
    End Sub
    Last edited by moviejunkie; Apr 11th, 2006 at 01:18 PM.

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: SQL Search

    You will need to use wildcard LIKE searches to perform that:

    VB Code:
    1. Dim strSQL as String = "SELECT id, first_name, last_name FROM employees WHERE " & searchtype & "LIKE '" & txtSearch.Text & "%'"
    The wildcard to match anything is the percent sign (%) to match single character is underscore (_). To match last_last name starting with mil the search would be Where Last_Name Like 'mil%'
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    19

    Re: SQL Search

    Ok here is my new code, but I am getting a Incorect syntex error when I try to execute the dataReader.

    VB Code:
    1. Dim sqlcomm As SqlCommand = New SqlCommand("SELECT id, first_name, last_name FROM employees WHERE " & searchtype & "LIKE '" & txtSearch.Text & "%'", sqlcon)

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: SQL Search

    Can you post the SQL that is being generated?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    19

    Re: SQL Search

    ok, got it, there needed to be a space between the " and the word LIKE

  6. #6
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: SQL Search

    OK. If that resolved you problem please mark this thread as resolved using the Thread Tools Menu
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width