vb.net -- Enabling "Search" within a textbox

[angelabi264]

I am workin on a project of cd library...if we hav to search for a movie...whn we type the name of movie in a textbox...all (possibilities) movies havin tat name shud appear...
for eg : in database thr are movies such as
Hum Dil de chuke sanam
Dil tho pagal hai
Dilagi
Dil hai tumhara

now if i type in textbox,the word "DIL"...all the 4 movie names shud appear within the dropdown of the texbox...

if i add a word such as "HAI" in textbox...(ie"D IL HAI")...movie names containing those 2 words shud b displayed within dropdown of textbox...(in this case Dil tho pagal hai & Dil hai tumhara)

Can somebody plzzz help me out in writtin a code for this in vb.net....

[ i use visual studio 2005 and sql server 2005]

[OuTCasT]

select movie from movies where movie = '" & txtMovies.text & "' LIKE '%%'

[Aads]

Hi there,

(1) Please run the below script in your database which creates a table & inserts the movie names

Create Table tblMovies
(
idMovie INT Identity Primary Key,
movieName Varchar(100)
)
 
Insert Into tblMovies Values ('Hum Dil de chuke sanam')
Insert Into tblMovies Values ('Dil tho pagal hai')
Insert Into tblMovies Values ('Dilagi')
Insert Into tblMovies Values ('Dil hai tumhara')
 

(2) Now paste the below event handler in your project renaming to the textbox which you have specified

  Private Sub txtMovieName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtMovieName.TextChanged
        Dim DtMovies As DataTable
        Dim Query As String = "SELECT idMovie, movieName From tblMovies "
        Try
            If (txtMovieName.Text.Trim.Length = 0) Then
                cmbMovies.DataSource = Nothing
                Return
            End If
            cmbMovies.DataSource = Nothing
 
            For len As Int32 = 0 To txtMovieName.Text.Split(Space(1)).Length
                If (len = 0) Then
                    Query &= "Where movieName Like '%" & txtMovieName.Text.Split(Space(1))(len) & "%'"
                Else
                    If (len = txtMovieName.Text.Split(Space(1)).Length) Then
                        Exit For
                    End If
                    Query &= vbCrLf & " AND " & vbCrLf & "movieName Like '%" & txtMovieName.Text.Split(Space(1))(len) & "%'"
                End If
            Next
 
 
            Dim Conn As SqlConnection = New SqlConnection(CONN_STRING)
            Conn.Open()
            Dim sqlCmd As SqlCommand = New SqlCommand
            With sqlCmd
                .Connection = Conn
                .CommandText = Query
                .CommandType = CommandType.Text
            End With
            Dim sqlDa As SqlDataAdapter = New SqlDataAdapter(sqlCmd)
            DtMovies = New DataTable
            sqlDa.Fill(DtMovies)
            cmbMovies.DataSource = Nothing
            If (DtMovies IsNot Nothing) Then
                With cmbMovies
                    .ValueMember = "idMovie"
                    .DisplayMember = "movieName"
                    .DataSource = DtMovies
                End With
            End If
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
 

(3) Finally run your project & you are all done!

Regards,
Aads

[Tyecom]

Hi Aads,

I'm trying to do the same thing and I'm trying to use the code you provided. However, I'm getting errors messages. The error I'm getting is "MovieSampl e not declared" and "Conn_Strin g not declared". Here is my modified code:

Imports System
Imports System.Configur ation
Imports System.Data.Sql Client

Public Class Form1

Private Sub txtMovieName_Te xtChanged(ByVal sender As Object, ByVal e As System.EventArg s) Handles txtMovieName.Te xtChanged

Dim DtMovies As DataTable
Dim Query As String = "SELECT idMovie, movieName From tblMovies "

Try
If (txtMovieName.T ext.Trim.Length = 0) Then
MovieSample.Dat aSource = Nothing
Return
End If
MovieSample.Dat aSource = Nothing

For len As Int32 = 0 To txtMovieName.Te xt.Split(Space( 1)).Length
If (len = 0) Then
Query &= "Where movieName Like '%" & txtMovieName.Te xt.Split(Space( 1))(len) & "%'"
Else
If (len = txtMovieName.Te xt.Split(Space( 1)).Length) Then
Exit For
End If
Query &= vbCrLf & " AND " & vbCrLf & "movieName Like '%" & txtMovieName.Te xt.Split(Space( 1))(len) & "%'"
End If
Next

Dim Conn As SqlConnection = New SqlConnection(C ONN_STRING)
Conn.Open()
Dim sqlCmd As SqlCommand = New SqlCommand
With sqlCmd
.Connection = Conn
.CommandText = Query
.CommandType = CommandType.Tex t
End With
Dim sqlDa As SqlDataAdapter = New SqlDataAdapter( sqlCmd)
DtMovies = New DataTable
sqlDa.Fill(DtMo vies)
MovieSample.Dat aSource = Nothing
If (DtMovies IsNot Nothing) Then
With MovieSample
.ValueMember = "idMovie"
.DisplayMember = "movieName"
.DataSource = DtMovies
End With
End If

Catch ex As Exception
MessageBox.Show (ex.Message)
End Try
End Sub
End Class

[Aads]

Hi there,

• MovieSample is the name of the combobox where it displays all the matched movie names and
• CONN_STRING is the connection string to connect to the database - you need to declare this variable at the top like this

Private Const CONN_STRING As String = "Server = localhost;Database = someDbName; Integrated Security = SSPI"
 

[Tyecom]

Hi Aads,

Thank you for your assistance. I'm trying to do something very similiar to what you described above. I also want to be able to search a combobox, but using the combobox "only". For instance, I want to type in a combobox titles of movies stored in my database. As I type, the movies will appear in automatically in the dropdown menu in alphabetical order. The filter in the above code does not filter the way I would like it to. Example, any title with the letter "T" will appear when you type a "T". Even if the "T" is the last letter of the title. I would like the title to appear in sequence. If I type a "T" all titles that begins with "T" will appear. If I type a "TH" all titles what begins with "TH" will appear and so on.

I tried to modified this code to reflect these changes, but have so far failed to make it work. Any assistance would be greatly appreciated.

[Aads]

Hi there,

• First of all add a new form to your project.
• Drag one Combobox from the toolbox & add it to you form; rename the combobox to cmbMovies.
• Paste the below code in your form.
• You will have to change the connection string to point to your database.

    Private mResetOnClear As Boolean = False
    Public Property ResetOnClear() As Boolean
        Get
            Return mResetOnClear
        End Get
        Set(ByVal Value As Boolean)
            mResetOnClear = Value
        End Set
    End Property
 
    Private Sub cmbMovies_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmbMovies.KeyPress
        Try
            Dim intIndex As Integer
            Dim strEntry As String
 
            If Char.IsControl(e.KeyChar) Then
                If cmbMovies.SelectionStart <= 1 Then
                    If mResetOnClear Then
                        cmbMovies.SelectedIndex = 0
                        cmbMovies.SelectAll()
                    Else
                        cmbMovies.Text = String.Empty
                        cmbMovies.SelectedIndex = -1
                    End If
                    e.Handled = True
                    Exit Sub
                End If
                If cmbMovies.SelectionLength = 0 Then
                    strEntry = cmbMovies.Text.Substring(0, cmbMovies.Text.Length - 1)
                Else
                    strEntry = cmbMovies.Text.Substring(0, cmbMovies.SelectionStart - 1)
                End If
                fetchData(strEntry)
            ElseIf (Not Char.IsLetterOrDigit(e.KeyChar)) And (Not Char.IsWhiteSpace(e.KeyChar)) Then  '< 32 Or KeyAscii > 127 Then
                Exit Sub
            Else
                If cmbMovies.SelectionLength = 0 Then
                    strEntry = UCase(cmbMovies.Text & e.KeyChar)
                Else
                    strEntry = cmbMovies.Text.Substring(0, cmbMovies.SelectionStart) & e.KeyChar
                End If
                fetchData(strEntry)
            End If
 
            intIndex = cmbMovies.FindString(strEntry)
 
            If intIndex <> -1 Then
                cmbMovies.SelectedIndex = intIndex
                cmbMovies.SelectionStart = strEntry.Length
                cmbMovies.SelectionLength = cmbMovies.Text.Length - cmbMovies.SelectionStart
            Else
                cmbMovies.Focus()
                cmbMovies.Text = strEntry
            End If
            e.Handled = True
            Exit Sub
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
 
    Private Sub fetchData(ByVal currentEntry As String)
        Dim DtMovies As DataTable
        Dim Query As String = "SELECT idMovie, movieName From tblMovies "
        Try
            cmbMovies.DataSource = Nothing
 
            Query &= "Where movieName Like '" & currentEntry & "%'"
 
            Dim Conn As SqlConnection = New SqlConnection("Server=in-bl-akash-lap\DSI;Database=MyDb;Integrated Security = SSPI")
            Conn.Open()
            Dim sqlCmd As SqlCommand = New SqlCommand
            With sqlCmd
                .Connection = Conn
                .CommandText = Query
                .CommandType = CommandType.Text
            End With
            Dim sqlDa As SqlDataAdapter = New SqlDataAdapter(sqlCmd)
            DtMovies = New DataTable
            sqlDa.Fill(DtMovies)
            cmbMovies.DataSource = Nothing
            If (DtMovies IsNot Nothing) Then
                If (DtMovies.Rows.Count = 0) Then
                    'MessageBox.Show("Could not find title '" & e)                  
                    Me.Text = currentEntry
                End If
 
                cmbMovies.ValueMember = "idMovie"
                cmbMovies.DisplayMember = "movieName"
                cmbMovies.DataSource = DtMovies
 
            End If
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
 

• Finally you can change the code as you like for any custom functionality required - this code "ONLY" demonstrates the required functionality.

Thanks,
Aads