CODEWITHSUNDEEP
sqlstringvb6
Garrettchap1
Garrettchap1

Reputation: 79

Query string syntax error in vb6 & SQL

I can't figure out which part of this code is written incorrectly.

In my query string, i'm trying to collect a range of records between the values of sFrom and sTo. Basically, I want to make it so that out of all the control numbers that I collect from my database, the only ones that get displayed will be whatever the user inputs for the starting range (sFrom) and the ending range (sTo).

sQuery = "SELECT " & sFields & " FROM " & sTable &
         " Where store_id Like 'XXX'" & sFilter &
         " Where control_no > sFrom and < sTo " &
           order by " & sOrderBy

Upvotes: 0

Views: 1296

Answers (4)

Deanna
Deanna

Reputation: 24253

The best way to debug this is to see what the resultant SQL statement is and work from there.

Yours currently evaluates to something like

SELECT field, field2 FROM table Where store_id Like 'XXX'FilterString Where control_no > sFrom and < sTo order by field1
  1. You can't have multiple WHERE clauses.
  2. I expect your sFilter inclusion is very wrong, but it depends in what the exact value is.
  3. sFrom and sTo are included as literals, not evaluated (See tcarvin's answer)
  4. Each part of the WHERE clause needs to be full and complete. You can't omit the field to check (See Darrel's answer)

Upvotes: 1

Mark Bertenshaw
Mark Bertenshaw

Reputation: 5689

  1. In Like 'XXX'" & sFilter, you have no space between X' and the next string.
  2. You have no line continuation characters at all.
  3. You have TWO Where clauses.
  4. sFrom and sTo are literal strings. You probably meant to use them as variables.
  5. You have no comparison between control_no and sTo.
  6. You have no statement between sFilter and the surrounding SQL.
  7. You have no double quotes before order by.
  8. Just as a style choice, you are not consistent in your SQL keyword capitalisation.

Now, put it all together:

sQuery = "SELECT " & sFields & " FROM " & sTable & _
         " WHERE store_id LIKE 'XXX'" & _
         " AND " & sFilter & _
         " AND control_no > " & sFrom & _
         " AND control_no < " & sTo & _
         " ORDER BY " & sOrderBy

Upvotes: 1

tcarvin
tcarvin

Reputation: 10855

This:

" Where control_no > sFrom and < sTo "

should probably be this:

" Where control_no >= " & sFrom & " and control_no <= " & sTo

(Assuming of course that sFrom and sTo are string variables populated with the numeric input from the user)

Upvotes: 2

Darrel Miller
Darrel Miller

Reputation: 142014

You can't drop the control_no before the <

sQuery = "SELECT " & sFields & " FROM " & sTable & " Where store_id Like 'XXX'" & sFilter & " Where control_no > sFrom and control_no < sTo " & order by " & sOrderBy

Upvotes: 1

Related Questions