SQL problem

[sparkash]

Hi All,

I am using VB6 and connecting to my DB using DAO

I am trying to create a search wherby a user would input say 3 letters in to a textbox and the resulting matches would populate a msflexgrid.

For example, if he types in 'sic', then this would find 'basic force gauge' from the gauges table.

I have used the code below and although i have used the Like command, the search brings up 0 records. Yet i know that there several matches to the query.

Can you have a look at the query. I'm sure this is where the problem is, but not sure how to correct6

Many Thanks



strSQL = "Select Instrumentdescription, Instrumennumber From GAUGES where Instrumentdescription Like '" & Text1.Text & "'"
Data1.RecordSource = strSQL
Data1.Refresh
Data1.UpdateControls

[davebat]

You have't added wildcards, try:

strSQL = "Select Instrumentdescription, Instrumennumber From GAUGES where Instrumentdescription Like '%" & Text1.Text & "%'"

[sparkash]

Hi Davebat,

That was my mistake. In my project i have got the wildcards. I quickly typed in the code here and ommited the wildcards by mistake.

Even with the wildcards i get no results back.

[ganeshmoorthy]

try this...

strSQL = "Select Instrumentdescription, Instrumennumber From GAUGES where Instrumentdescription Like '*" & Text1.Text & "*'"

[FunkyDexter]

What DB are you using? Different DBs use different wildcards. If it's access you want the * that Ganeshmoorthy suggested. For SQL Server you use %. Other DB's use different wild cards again but most use one of these two characters.

[GaryMazzone]

All DB use the % sign for wildcards. Even Access if you are preforming the seach though ADO from VB.

Please post the exact SQL string that is being passed from the application to the DB.

[sparkash]

Thanks Ganeshmoorthy,

The code you suggested has fixed the problem.

Many Thanks

[ganeshmoorthy]

sparkash: You are most welcome and thanks for your rep points...