how do i base a sql query on a value of a variable???

[johnnyboy23]

SQL = "select * from clients where clientID =" myvalue

how do i base my sql query to pull all the records out according to what the myvalue variable is???

any ideas??
thanks

[Guinness]

It seems like you have the query built correcly, what
seems to be the problem?

Originally posted by johnnyboy23

SQL = "select * from clients where clientID = " & myvalue

[iring]

Make sure myvalue is a string.
If myvalue is numeric, use Str(myvalue)

[Berthil]

Be sure to use the quotes if you search for string. When I started programmin in VB3 (there was no Internet yet commonly spread) it took me 3 days to figure it out. Wish someone has told me that then:

SQL = "Select * FROM tblClients WHERE clientID = '" & str(myValue) & "'"

Originally posted by Berthil
Be sure to use the quotes if you search for string. When I started programmin in VB3 (there was no Internet yet commonly spread) it took me 3 days to figure it out. Wish someone has told me that then:

SQL = "Select * FROM tblClients WHERE clientID = '" & str(myValue) & "'"

Exactly right. Forgot about the quotes thing for strings, another one of the crazy things you have to do with vb, other languages don't require them.

[JazzBass]

Hi Jethro,
About your comment:

"another one of the crazy things you have to do with vb, other languages don't require them"

Are you sure about this? I thought it's standard SQL to include the single quotes for strings, like Berthil explained.

I use them in Access and Oracle.

Not trying to sound like a smarty, just wondering.

Thanks,
JazzBass

We use both Pick and Universe, under these languages you can do something like.

txtName = "Kerry O'Brian"
select customer where name = txtname

name is of course a field in the file customer, and as such the relation is implied and doesn't have to be stated. Any variable can be thrown into a statement without need of quotation marks. Even better it doesn't matter if the variable is null...

[mstrealy]

I have a problem i want to pull data from Oracle.

This code works well.

sql_command = "select * from COMPANY where CO_NM1 = '" & c_name3 & "'"

But when c_name3 = "Arthurs's Books"

Then i am out of luck and get an error because of the single quote in Arthur's.

I have numerous ways around this problem but i do fin until i get to a variable with a single quote in it.

sql_command = "select * from COMPANY where CO_NM1 = """ & c_name3 & """"




Please help me.


Mark S

Being a notation for a comment line in vb.

Single quotes are a curse to all VB database programmers! If there is one in your data, as Jethro said, the rest of the string is interpreted as a comment, not as SQL like you intended. Where I work we have a function that checks all text before it is put into a database so there are no hassles when it comes to retrieving the data. I think it was from a book(all due credit to its author!). This guarantees the data in your db is clean. If you really like single quotes in text try replacing them with ` before adding to the database(the function is called replace). You can see everything the function cleans out in the illegals string, I added a few charcters for the sake of the demo, you can alter this as you please.

usage

cleanstring(text1.text)
'then perform the insert of text1.text into database

Private Function cleanstring(str As String) As String
'clean illegal characters out of data

Dim i As Integer
Dim illegals As String

illegals = "~@^#&()`:;{}<>_?$'*%"""

For i = 1 To Len(illegals)
    str = Replace(str, Mid(illegals, i, 1), " ")
Next i
cleanstring = str

End Function

[paulw]

In SQL Server, there is an option quoted identifier which allows the replacement of ' with '' in string literals. There must be an equivalent in Oracle. Have you tried ''?

P.

[JazzBass]

Thanks!

That sounds pretty cool. I don't mind the single quotes that much, since that's what I started with. Try asking me that 2 days ago when I was working on my own program trying to do a INSERT INTO state with about 8 variables. Then I would have been singing a different tune.

Thanks again for the reply.

JazzBass

But our customers needed the single quote in names like O'Connor etc, couldn't train them into not using it....got the code from http://www.planetsourcecode.com to over-come this, but caren't quite find the actual source at the moment...must be in one of our dlls somewhere, though l would have thought our standards would have forced use of a function.

Will post up when l find it.