Filter MySQL query on a ListBox by a TextField

Databases
1

Hi there!

I have a listbox that gets populated my a general MySQL query. What I’m trying to achieve is to be able to filter that list using a LIKE% or REGEXP.

My problem is that I get a NIL when trying to add the % to the textfield.text data to pass on to the query.

Basically, it would be like and address book where I can type “J” and will display John 1, John 2, Jonas, Jonathan, or if I type “hn” will also display John 1, John 2, but NOT Jonas or Jonathan.

Anyone can give me a hand please?

Dim db As New MySQLCommunityServer
  db.Host = "127.0.0.1"
  //db.Port = 3306
  db.DatabaseName = "test"
  db.UserName = "root"
  db.Password = ""
  If db.Connect Then
    // Use the database
  Else
    // Connection error
    MsgBox(db.ErrorMessage)
  End If
  
  Dim rs as RecordSet
  Dim i as Integer
  
  If IsNumeric(BuscarCli.Text)=True Then // El valor es un numero entonces mostrar el registro directamente
    rs = db.SQLSelect("SELECT Codigo, Nombre FROM Clientes WHERE Codigo LIKE " + me.Text + "'%'")
    
    Listbox1.DeleteAllRows
    
    If rs <> Nil then
      while not rs.eof
        Listbox1.AddRow ""
        for i = 1 to rs.FieldCount
          Listbox1.Cell( Listbox1.LastIndex, i-1 ) = rs.IdxField(i).Value
        next
        rs.MoveNext
      wend
      
      rs.close
    end if
    
    
  Else // El valor es texto entonces abrir la ventana para buscar
    rs = db.SQLSelect("SELECT Nombre FROM Clientes WHERE Nombre REGEXP " + me.Text)
    
    Listbox1.DeleteAllRows
    
    If rs <> Nil then
      while not rs.eof
        Listbox1.AddRow ""
        for i = 1 to rs.FieldCount
          Listbox1.Cell( Listbox1.LastIndex, i-1 ) = rs.IdxField(i).Value
        next
        rs.MoveNext
      wend
      
      rs.close
    end if
    
  End If
2

You should use
db.SQLSelect(“SELECT Codigo, Nombre FROM Clientes WHERE Codigo LIKE '” + me.Text + “%’”)
Here is a better view:
…Codigo LIKE ’ " + me.Text + "% ’ ")

3

Put percent signs on both sides of the literal. Otherwise it will only look for your string at the beginning of each field. Eg., ‘j%’ matches ‘john’ but not ‘xojo’. You need ‘%j%’.

4

Thank you both for your great imput !!! Now it’s working!

5

Tim, how do you do it with the me.text field there? It gives me a NIL error.

rs = db.SQLSelect("SELECT Codigo, Nombre FROM Clientes WHERE Codigo LIKE %'" + me.Text + "%'")
6

[quote=43513:@Alex Danilan]Tim, how do you do it with the me.text field there? It gives me a NIL error.

rs = db.SQLSelect("SELECT Codigo, Nombre FROM Clientes WHERE Codigo LIKE %'" + me.Text + "%'")
rs = db.SQLSelect("SELECT Codigo, Nombre FROM Clientes WHERE Codigo LIKE '%" + me.Text + "%'")

You need to end up with ‘%%’
Your placement of the first single quote is incorrect