Member Avatar for bogeybrown

I get the result I want in a Datagrid by using Recordset Sql:

Select * from MYQUERY Where (name Like 'WHATEVER*')

I change the name by retyping the whole line, substituting the new name,

typed into a long textBox named txt.Select, like this

Private Sub cmdSelect_Click()

Data1.RecordSource = txtSelect.Text
Data1.Refresh

End Sub

When try to use Select * From MYQuery Where ('name Like ' *')
and leave it in the TextBox, putting the next name in the space, nothing happens.

I want to put the names only in the textbox without retyping the whole line of sql to bring up the fields in my query.

Any offers of a few lines of helpful code would be much appreciated.

Bogeybrown

  • 2 Contributors
  • 2 Replies
  • 116 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by bogeybrown

Dani AI

Generated

Good first steps from and well done for spotting the identifier quoting issue. Two practical improvements that make the solution robust and safer:

Use a parameterized query instead of concatenating user text into SQL. That prevents quoting problems and SQL injection, and keeps your code working regardless of embedded quotes in the name. Example (VB6 + ADO — bind the returned recordset to your grid/control):

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open "your-connection-string"

Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = cn
  .CommandText = "SELECT * FROM MYQUERY WHERE name LIKE ?"
  .CommandType = adCmdText
  .Parameters.Append .CreateParameter("pName", adVarChar, adParamInput, 200, "%" & txtSearch.Text & "%")
End With

Set rs = cmd.Execute
Set DataGrid1.DataSource = rs

Notes and troubleshooting tips:

  • Wildcards differ by provider: use % for MySQL/ANSI SQL and * for classic Jet/Access wildcard mode (see MySQL docs for LIKE). If you must concatenate, escape single quotes by doubling them.
  • If a column name is reserved or contains spaces, wrap it: MySQL uses backticks (`name`), Access uses square brackets ([name]). That explains why adding brackets fixed your case.
  • For quick debugging, print the final parameter/value or SQL to the Immediate window and test it directly in the DB client.
  • Prefer prepared/parameterized commands or stored/prepared statements in the DB for repeat searches (see OWASP guidance on SQL injection prevention).

References: MySQL LIKE behavior MySQL LIKE, general SQL injection prevention OWASP SQL Injection Prevention Cheat Sheet.

Recommended Answers

All 2 Replies

Member Avatar for QVeen72

Hi,

Just type the name in TextBox.
and alter the code like this :

Dim TStr as String
TStr = "Select * from MYQUERY Where name Like '*" _
& txtSelect.Text & "*'"
Data1.RecordSource = TStr
Data1.Refresh

Regards
Veena

Member Avatar for bogeybrown

Hi,

Just type the name in TextBox.
and alter the code like this :

Dim TStr as String
TStr = "Select * from MYQUERY Where name Like '*" _
& txtSelect.Text & "*'"
Data1.RecordSource = TStr
Data1.Refresh

Regards
Veena

Veena,
Many thanks.
With the addition of a pair of brackets, one before name and the second before the last pair of quote marks, it worked like a charm.

Yours,

Bogeybrown.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.