ASP dynamic SQL query with for loop?

hman · April 3rd, 2006, 12:53 PM

Hi,

I am trying to build a dynamic SQL query in asp using a "for loop".
I can get the "for loop" results to display fine in browser using "response.write"
BUT I want to put the "for loop" results into an SQL query.

Here is the code which genrates syntax errors:

Code:

Recordset2.Source = "SELECT * FROM Products WHERE " & _
For fnum2 = 4 To Recordset1.Fields.Count-1
    If Recordset1.Fields(fnum2).Value = 1 then
       Product_Code = '" & _Recordset1.Fields(fnum2).Name & "' AND"
    End if
Next

Please help!!

hman · April 4th, 2006, 12:37 PM

Nobody on this forum provided any help :(

Anyway a friend managed to help me and solved it.

rstelma · April 4th, 2006, 01:19 PM

Can you do a Response.Write on the page so I can see what query got built?

One thing that I do when building dynamic queries is to append the following code at the end of the SQL statement since you never know how many "ANDs" you will need.

Product_Code = Product_Code & " 1 = 1 ;"

Its hard to tell from what you posted exactly what the problem is.

Thanks,
Richard

rodmcleay · December 13th, 2006, 08:56 PM

I think for starters the product code needs to be in the string.
Product_Code = '" & _Recordset1.Fields(fnum2).Name & "' AND" <--the product code does not appear to be part of the string.
"Product_Code = '" & _Recordset1.Fields(fnum2).Name & "' AND"
Not tested could be wrong.
But rstelma is correct, print the sql query with
response.write(Recordset2.Source )

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================