[RESOLVED] SQL Statement for newbie !

[DubweiserTM]

Hi everybody ! I'm a new one in ADO and SQL Statement...

Two questions for you:

1) Is it normal that my statement got many (") ??? if answer it's no, is there a simple way to write this ???

strSQL = "SELECT * FROM " & adoTable & " WHERE " & adoField & " = " & "'" & adoCombo.Text & "'"

2) Finally, sometime I got an error because the value of adoCombo.Text = Column 6" x 8'

Error: Syntax error (missing operator) in query expression...

I thought it's because the (') after 8 and I tried to put 96" and I a got the same error ???

Thanks in advance for your help !

[GaryMazzone]

The " and ' are marking the end of the string statements (the SQL) which happens in the middle of your statement. You need to use parameters or do a preplace on the 6" x 8' like this:

dim strTest as string 
 strTest = Replace(adoCombo.Text,"'","''")
 strTest = Replace (strTest.Text """","""""")

Look at the qoute marks really carefully. The are some single qoutes that are doubled up and some double qoutes to double doubles and quad doubles.

[Hack]

And

strSQL = "SELECT * FROM " & adoTable & " WHERE " & adoField & " = " & "'" & adoCombo.Text & "'"

I see nothing wrong with this statement.

[techgnome]

1) You really shouldn't be using generic sql like that.... you should use specific SQL, where the Table and field(s) are known.

strSQL = "SELECT * FROM MyTable WHERE myField = " & "'" & adoCombo.Text & "'"

2) Now is a good time to learn about parameterized queries. It's the use of " AND ' that is your problem. They are used by SQL as text indicators.... more on the solution in a sec

3) Another reason fo parameterized queries is security. LEt's use your query as an example:

strSQL = "SELECT * FROM MyTable WHERE myField = " & "'" & adoCombo.Text & "'"

What would happen if adboCombo contained this: '; DELETE * FROM someTable; SELECT * FROM Users WHERE Uname = '

Your resulting SQL would look like this:
SELECT * FROM MyTable WHERE myField = ''; DELETE * FROM someTable; SELECT * FROM Users WHERE Uname = ''

As you can see.... that would be very bad, now wouldn't it?

But if you use a parameterized query:

strSQL = "SELECT * FROM MyTable WHERE myField = @FldParam"

Then use the .CreateParameter (of the command object) to create a paremeter and it's value to pass in, then add it to the parameters collection using .Parameters.Add (again, in the command object).

Hope this is enough info to get you started looking in the right places

-tg


edit: ugh... Gary - geezes... what's with the grep solution? Why does eveyone think that solves things? IMHO that just makes it worse since you're modifying data. Data should never be modified just to store it in the DB. Hack - this trout's for you while syntaticaly correct, there's everything wrong with it.

[techgnome]

See also this thread for a similar notes about sql concatenation.
http://vbforums.com/showthread.php?t=426312

-tg

[DubweiserTM]

The " and ' are marking the end of the string statements (the SQL) which happens in the middle of your statement. You need to use parameters or do a preplace on the 6" x 8' like this:

dim strTest as string 
 strTest = Replace(adoCombo.Text,"'","''")
 strTest = Replace (strTest.Text """","""""")

Look at the qoute marks really carefully. The are some single qoutes that are doubled up and some double qoutes to double doubles and quad doubles.

Thanks again !

And

strSQL = "SELECT * FROM " & adoTable & " WHERE " & adoField & " = " & "'" & adoCombo.Text & "'"

I see nothing wrong with this statement.

Cool ! It's complicated to read because there are many (")

[DubweiserTM]

2) Now is a good time to learn about parameterized queries. It's the use of " AND ' that is your problem. They are used by SQL as text indicators.... more on the solution in a sec

Hope this is enough info to get you started looking in the right places...

Thanks you too ! I will check for this !