vb.net - making combobox a search engine with mysql database

[kumsay]

hi there,

I've been working on a movie library. I put a combobox on my form as a search engine. If user wants to search for a movie then type the title of the movie, all possible movie having that title will appear within the dropdown of the combobox. For example, if user typed letter m, all movies stored in the database having a title with m should appear automatically in the dropdown of combobox. If user wants to continue typing, ex. user typed ma, all movie title starting with ma will appear. It's already working. However, I'm getting error messages saying "Not allowed to chage the 'ConnectionStri ng' property while the connection (state = Open)." This error message appears everytime I'm typing more than one character on the combobox. It also appears when I deleted the string that I entered then typed a new one, but then, after displaying the error message, it still displays the result of what I typed before the error appeared. Here's the modified code:

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
                Exit Sub
            Else
                If cmbMovies.SelectionLength = 0 Then
                    strEntry = UCase(cmbMovies.Text & 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)
        Call Connect()
        With Me
 
            Dim dt As DataTable
            STRSQL = "SELECT idMovie, movieName From tblMovies"
            Try
                cmbMovies.DataSource = Nothing
 
                STRSQL = "Select idMovie, movieName FROM tblMovies Where movieName Like '" & currentEntry & "%'"
 
                Dim myCmd As New MySqlCommand
                With myCmd
                    .Connection = myConn
                    .CommandText = STRSQL
                    .CommandType = CommandType.Text
                End With
                Dim myAdptr As New MySqlDataAdapter(myCmd)
                dt = New DataTable
                myAdptr.Fill(dt)
                cmbMovies.DataSource = Nothing
                If (dt IsNot Nothing) Then
                    If (dt.Rows.Count = 0) Then
                        Me.Text = currentEntry
                    End If
 
                    cmbMovies.ValueMember = "idMovie"
                    cmbMovies.DisplayMember = "movieName"
                    cmbMovies.DataSource = dt
 
                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
 
        End With
    End Sub
 

I'm using mysql-connector-net-6.5.4 to connect the whole project to the database so I just called the Connect(). Any ideas would be greatly appreciated. I'm quite new to this as you can probably tell.


ps. sorry, I've already posted this question as a reply to the "
vb.net -- Enabling "Search" within a textbox..help me plzz" discussion. I've already posted my question before I noticed that the discussion was discussed last April 2009, I just thought that I should post my question as a new discussion. ^_^v

[PsychoCoder]

You're trying to share the connection, then change it's properties while it's open. What does your Connect method look like?

[kumsay]

Imports MySql.Data.MySqlClient
 
Module Connection
    Public myConnectionString As String
    Public STRSQL As String
    Public myConn As New MySqlConnection
    Public Sub Connect()
        With myConn
            Try
                If .State = ConnectionState.Open Then
 
                End If
                myConnectionString = "Database=movie_lib;Server=localhost;Uid=root;Password="
                .ConnectionString = myConnectionString
                .Open()
                'MsgBox("Successful Connection")
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Error")
                .Close()
            End Try
        End With
    End Sub
    Public Sub Disconnect()
        With myConn
            .Close()
            .Dispose()
        End With
    End Sub
 
End Module
 

that's my connection module look like sir

[PsychoCoder]

When you check if the connection is open you do nothing in this If statement

If .State = ConnectionState.Open Then
 
End If

So just close your connection

If .State = ConnectionState.Open Then
   .Close()
End If

[kumsay]

oh yeah!! it worked!!..why didn't I noticed that?? haha,thanks for the help sir :)