By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,234 Members | 1,869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,234 IT Pros & Developers. It's quick & easy.

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

P: 25
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 'ConnectionString' 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:
Expand|Select|Wrap|Line Numbers
  1. Private mResetOnClear As Boolean = False
  2.     Public Property ResetOnClear() As Boolean
  3.         Get
  4.             Return mResetOnClear
  5.         End Get
  6.         Set(ByVal value As Boolean)
  7.             mResetOnClear = value
  8.         End Set
  9.     End Property
  10.  
  11.     Private Sub cmbMovies_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmbMovies.KeyPress
  12.  
  13.         Try
  14.             Dim intIndex As Integer
  15.             Dim strEntry As String
  16.  
  17.             If Char.IsControl(e.KeyChar) Then
  18.                 If cmbMovies.SelectionStart <= 1 Then
  19.                     If mResetOnClear Then
  20.                         cmbMovies.SelectedIndex = 0
  21.                         cmbMovies.SelectAll()
  22.                     Else
  23.                         cmbMovies.Text = String.Empty
  24.                         cmbMovies.SelectedIndex = -1
  25.                     End If
  26.                     e.Handled = True
  27.                     Exit Sub
  28.  
  29.                 End If
  30.                 If cmbMovies.SelectionLength = 0 Then
  31.                     strEntry = cmbMovies.Text.Substring(0, cmbMovies.Text.Length - 1)
  32.                 Else
  33.                     strEntry = cmbMovies.Text.Substring(0, cmbMovies.SelectionStart - 1)
  34.  
  35.                 End If
  36.                 fetchData(strEntry)
  37.             ElseIf (Not Char.IsLetterOrDigit(e.KeyChar)) And (Not Char.IsWhiteSpace(e.KeyChar)) Then
  38.                 Exit Sub
  39.             Else
  40.                 If cmbMovies.SelectionLength = 0 Then
  41.                     strEntry = UCase(cmbMovies.Text & e.KeyChar)
  42.                 End If
  43.                 fetchData(strEntry)
  44.             End If
  45.  
  46.             intIndex = cmbMovies.FindString(strEntry)
  47.  
  48.             If intIndex <> -1 Then
  49.                 cmbMovies.SelectedIndex = intIndex
  50.                 cmbMovies.SelectionStart = strEntry.Length
  51.                 cmbMovies.SelectionLength = cmbMovies.Text.Length - cmbMovies.SelectionStart
  52.             Else
  53.                 cmbMovies.Focus()
  54.                 cmbMovies.Text = strEntry
  55.             End If
  56.             e.Handled = True
  57.             Exit Sub
  58.  
  59.         Catch ex As Exception
  60.             MessageBox.Show(ex.Message)
  61.         End Try
  62.     End Sub
  63.  
  64.     Private Sub fetchData(ByVal currentEntry As String)
  65.         Call Connect()
  66.         With Me
  67.  
  68.             Dim dt As DataTable
  69.             STRSQL = "SELECT idMovie, movieName From tblMovies"
  70.             Try
  71.                 cmbMovies.DataSource = Nothing
  72.  
  73.                 STRSQL = "Select idMovie, movieName FROM tblMovies Where movieName Like '" & currentEntry & "%'"
  74.  
  75.                 Dim myCmd As New MySqlCommand
  76.                 With myCmd
  77.                     .Connection = myConn
  78.                     .CommandText = STRSQL
  79.                     .CommandType = CommandType.Text
  80.                 End With
  81.                 Dim myAdptr As New MySqlDataAdapter(myCmd)
  82.                 dt = New DataTable
  83.                 myAdptr.Fill(dt)
  84.                 cmbMovies.DataSource = Nothing
  85.                 If (dt IsNot Nothing) Then
  86.                     If (dt.Rows.Count = 0) Then
  87.                         Me.Text = currentEntry
  88.                     End If
  89.  
  90.                     cmbMovies.ValueMember = "idMovie"
  91.                     cmbMovies.DisplayMember = "movieName"
  92.                     cmbMovies.DataSource = dt
  93.  
  94.                 End If
  95.             Catch ex As Exception
  96.                 MessageBox.Show(ex.Message)
  97.             End Try
  98.  
  99.         End With
  100.     End Sub
  101.  
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
Jan 6 '13 #1
Share this Question
Share on Google+
4 Replies


PsychoCoder
Expert Mod 100+
P: 465
You're trying to share the connection, then change it's properties while it's open. What does your Connect method look like?
Jan 7 '13 #2

P: 25
Expand|Select|Wrap|Line Numbers
  1. Imports MySql.Data.MySqlClient
  2.  
  3. Module Connection
  4.     Public myConnectionString As String
  5.     Public STRSQL As String
  6.     Public myConn As New MySqlConnection
  7.     Public Sub Connect()
  8.         With myConn
  9.             Try
  10.                 If .State = ConnectionState.Open Then
  11.  
  12.                 End If
  13.                 myConnectionString = "Database=movie_lib;Server=localhost;Uid=root;Password="
  14.                 .ConnectionString = myConnectionString
  15.                 .Open()
  16.                 'MsgBox("Successful Connection")
  17.             Catch ex As Exception
  18.                 MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Error")
  19.                 .Close()
  20.             End Try
  21.         End With
  22.     End Sub
  23.     Public Sub Disconnect()
  24.         With myConn
  25.             .Close()
  26.             .Dispose()
  27.         End With
  28.     End Sub
  29.  
  30. End Module
  31.  
that's my connection module look like sir
Jan 7 '13 #3

PsychoCoder
Expert Mod 100+
P: 465
When you check if the connection is open you do nothing in this If statement

Expand|Select|Wrap|Line Numbers
  1. If .State = ConnectionState.Open Then
  2.  
  3. End If
So just close your connection

Expand|Select|Wrap|Line Numbers
  1. If .State = ConnectionState.Open Then
  2.    .Close()
  3. End If
Jan 7 '13 #4

P: 25
oh yeah!! it worked!!..why didn't I noticed that?? haha,thanks for the help sir :)
Jan 8 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.