How do I reference to the value of a variable in a SQL query?

[ozymandiaz2]

I need an SQL query that is implemented at run-time to do this:

SELECT Soldiers.SoldierID
FROM D:\syrpad\syrpad.Soldiers
WHERE (((Soldiers.SoldierNameLast)=Label5.caption));

What I mean is that it will give me back only the SoldierID of soldiers whose last name (SoldierNameLast) is equal to whatever the current content of Label5.caption is. Label5 is a standard VB label and it's caption value changes during the execution of the program.

When I try to run the above code, VB gives me an error:
"No value given for one or more required parameters"

When I do the same sql with a fixed value, however, it works fine:

SELECT Soldiers.SoldierID
FROM D:\syrpad\syrpad.Soldiers
WHERE (((Soldiers.SoldierNameLast)="Smith"));

--> returns SoldierIDs of soldiers whose last name is Smith.

I guess then that my problem is with how I am referring to the Label.caption in my SQL query. It is probably trying to look up the info in the database instead of in the program itself.

PLEASE HELP ME!!

Urgent!


Thanks

[HunterMcCray]

Originally posted by ozymandiaz2
I need an SQL query that is implemented at run-time to do this:

SELECT Soldiers.SoldierID
FROM D:\syrpad\syrpad.Soldiers
WHERE (((Soldiers.SoldierNameLast)=Label5.caption));

What I mean is that it will give me back only the SoldierID of soldiers whose last name (SoldierNameLast) is equal to whatever the current content of Label5.caption is. Label5 is a standard VB label and it's caption value changes during the execution of the program.

When I try to run the above code, VB gives me an error:
"No value given for one or more required parameters"

When I do the same sql with a fixed value, however, it works fine:

SELECT Soldiers.SoldierID
FROM D:\syrpad\syrpad.Soldiers
WHERE (((Soldiers.SoldierNameLast)="Smith"));

--> returns SoldierIDs of soldiers whose last name is Smith.

I guess then that my problem is with how I am referring to the Label.caption in my SQL query. It is probably trying to look up the info in the database instead of in the program itself.

PLEASE HELP ME!!

Urgent!


Thanks

No Worrys, Very Easy!

txtSQL="SELECT Soldiers.SoldierID "
txtSQL=txtSQL & "FROM D:\syrpad\syrpad.Soldiers "
txtSQL=txtSQL & "WHERE (((Soldiers.SoldierNameLast)="
txtSql=txtSQL & chr(34) & Label5.caption & chr(34) & "));"

A note: If you go to ADO you will need to replace chr(34) with Chr(39). Chr(34) is a Double Quote Chr(39) is a Single Quote. If you will place a stop command after this code snipet and then run your program until it stops then go to your debug window and type print txtSQL you will see exactly the text that you had Hard Coded into your example. The reason that your code did not work was that from Visual Basic's point of view anything in quotes is exactly what is in the quotes, so the SQL statement that your code generated was looking for a string that should have been in quotes but instead was a variable that it did not recognize. You have to learn to think about the text that the SQL is actually seeing, not what you want it to see. The best way to do this is to view the text in either a debug window or post it to a temporary label on the form that you are working on.

Hope this made your life easier,

Hunter

[Asdf]

I'm reasonably sure you can avoid the Chr(34)'s by using the single quote => '

Example:

txtSQL="SELECT Soldiers.SoldierID "
txtSQL=txtSQL & "FROM D:\syrpad\syrpad.Soldiers "
txtSQL=txtSQL & "WHERE (((Soldiers.SoldierNameLast)="
txtSql=txtSQL & "'" & Label5.caption & "'));"

[HunterMcCray]

Originally posted by Asdf
I'm reasonably sure you can avoid the Chr(34)'s by using the single quote => '

Example:

txtSQL="SELECT Soldiers.SoldierID "
txtSQL=txtSQL & "FROM D:\syrpad\syrpad.Soldiers "
txtSQL=txtSQL & "WHERE (((Soldiers.SoldierNameLast)="
txtSql=txtSQL & "'" & Label5.caption & "'));"

Of course you can. I actually use a variable; I do this because I use routines that in one app use Chr(34) and in others use Chr(39) depending on the app. In DAO Chr(34) has advantages over chr(39), but in ADO you must use Chr(39). It is easier in SQL generators that are used across applications to leave that option open...ie Chr(cQt) where cQt is a global constant variable of type integer, and it works across ADO//DAO for SQL generation.....But for hardcode, you are certainly right. I also find that in some fonts it is difficult to distinguish between "'" and '"" which causes a lot of errors in SQL strings, because "you know what I mean", but the SQL compiler does not.

Hunter

[Stevie]

The way I usually do it is to use single quotes instead of chr(34)/chr(39).

Now before anybody jumps on me, if there search criteria is a string which may contain a single quote I do the following...

  strSQL = Replace(strSQL, "'", "''")

Just my way of doing it.

[HunterMcCray]

Originally posted by Stevie
The way I usually do it is to use single quotes instead of chr(34)/chr(39).

Now before anybody jumps on me, if there search criteria is a string which may contain a single quote I do the following...

  strSQL = Replace(strSQL, "'", "''")

Just my way of doing it.

That is very nice. I like it a lot.

Thank you,

Hunter