like statements with variables

[SozeWun]

hi all,

I have been trying to use a sql like statement with a variable and it doesn't seem to be working...

.....fieldname LIKE 'variable%'
...... " " variable%
...... " " "'variable%'"

Does anybody know the proper syntax?...I know it's possible to do it in ASP so it has to be possible in VB

Thanx in advance

SozeWun

[Ecniv]

Try something like :


Dim strSql as string
Dim lngVar as Long


strSql="Select ... From....Where fieldname=" & lngVar & " Order By..."

Just add ' for text and # for dates around them so the resulting string of sql looks right (basic string manipulation - should be the same in ASP).


Vince

[Hack]

Here is an example from one of my projects

sqlstr = "SELECT pa_code, name, street, city, state "
sqlstr = sqlstr & "FROM qcap_dealer WHERE city LIKE '" & txtSrchCity.Text & "%' ORDER BY name"

Hope this helps.

[SozeWun]

here's my sql statement:
x=txtCriteria.text
.
.
.
.CommandText = "select CompanyName,PropertyRollNumber from tblMainContactClient CC join tblMainProperty MP on CC.ClientNumber=MP.ClientNumber where MP.PropertyRollNumber LIKE ' " & x & "%' "

Still doesnt work....i've even tried it in SQL Analyzer....wut am i doing wrong?

SozeWun

[PilgrimPete]

Just a wild shot in the dark, but PropertyRollNumber is a char/varchar field isn't it? If not, you're probably going to have to cast it before you can use LIKE.

[SozeWun]

yup its a varchar...

Sozewun