How Can I Concatnate ?

[Zelalem]

Hi,
I am using VB.6 for developing an application program and come across with the folloing SQL statement that works properly .

rs.open"SELECT Count(tblcontro l.id) As [CounR] FROM tblcontrol WHERE
tblcontrol.id = ' 0012 ' AND tblcontrol.cdna me = ' A' ;", cn,adOpenDynami c,adLockOptimis tic.

But, I cannot make, any values for the criteria, is given from the user . As in

Suppose:
X= txt1.Text
Y=txt2.Text

How can I concatnate the two variables with in the sql statement.


Example:

rs.open"SELECT Count(tblcontro l.id) As [CounR] FROM tblcontrol WHERE
tblcontrol.id = ' X ' AND tblcontrol.cdna me = 'Y' ;", cn,adOpenDynami c,adLockOptimis tic.

[sashi]

Hi,
I am using VB.6 for developing an application program and come across with the folloing SQL statement that works properly .

rs.open"SELECT Count(tblcontro l.id) As [CounR] FROM tblcontrol WHERE
tblcontrol.id = ' 0012 ' AND tblcontrol.cdna me = ' A' ;", cn,adOpenDynami c,adLockOptimis tic.

But, I cannot make, any values for the criteria, is given from the user . As in

Suppose:
X= txt1.Text
Y=txt2.Text

How can I concatnate the two variables with in the sql statement.


Example:

rs.open"SELECT Count(tblcontro l.id) As [CounR] FROM tblcontrol WHERE
tblcontrol.id = ' X ' AND tblcontrol.cdna me = 'Y' ;", cn,adOpenDynami c,adLockOptimis tic.

Hi there,

Kindly refer to below modified code segment, hope it works. Good luck & Take care.

Dim TMPSQL as String 

TMPSQL = ""
TMPSQL = "SELECT Count(tblcontrol.id) As [CounR] FROM tblcontrol WHERE
tblcontrol.id = ' " & X & " ' AND tblcontrol.cdname  = '" & Y & "'"

rs.open TMPSQL, cn, adOpenDynamic, adLockOptimistic

[Killer42]

Dim TMPSQL as String 
TMPSQL = "SELECT Count(tblcontrol.id) As [CounR] FROM tblcontrol WHERE
tblcontrol.id = ' " & X & " ' AND tblcontrol.cdname  = '" & Y & "'"

If X is supposed to be a number as appears likely, you will want to leave out the single quotes around it. For example...

TMPSQL = "SELECT Count(tblcontrol.id) As [CounR] FROM tblcontrol
WHERE tblcontrol.id =[U] " & X & " [/U]AND tblcontrol.cdname  = '" & Y & "'"

Also, note that both sashi and I have wrapped these long lines for readability here. If you copy and paste them into VB, you will have problems unless you stick them back together.

[sashi]

If X is supposed to be a number as appears likely, you will want to leave out the single quotes around it. For example...

TMPSQL = "SELECT Count(tblcontrol.id) As [CounR] FROM tblcontrol
WHERE tblcontrol.id =[U] " & X & " [/U]AND tblcontrol.cdname  = '" & Y & "'"

Also, note that both sashi and I have wrapped these long lines for readability here. If you copy and paste them into VB, you will have problems unless you stick them back together.

Hi Killer,

Thanks for the additional info :) Take care.