Screwy SQL Statement

[trc]

Hey Kids.

I am having a problem with a dynamically generated SQL statement. I have narrowed the problem down to the second 'if' statement not being run. Or at least not having an impct on the SQL.

Here is the SQL

'This part I think is Fine
strSQLQuery = "SELECT tableProducts.UID AS PUID, tableProducts.DEALERUID AS DUID, tableProducts.TANKUID AS TKUID, tableProducts.TRUCKUID AS TRUID, tableProducts.QUANTITY, tableProducts.AVAILABLE, tableProducts.WEBPAGEURL AS PRODURL, tableDealers.NAME AS DEALERNAME, tableDealers.ADDRESS AS DEALERADDR, tableDealers.CITY AS DEALERCITY, tableDealers.PROVINCE AS DEALERPROV, tableDealers.WEBSITEURL AS DEALERURL, tableTanks.ALTERNATE_URL AS ALTURL, tableTanks.NAME AS TANKNAME, tableTrucks.NAME AS TRUCKNAME, tableTrucks.LOGOIMG AS TRUCKLOGO FROM tableTrucks INNER JOIN (tableTanks INNER JOIN (tableDealers INNER JOIN tableProducts ON tableDealers.UID = tableProducts.DEALERUID) ON tableTanks.UID = tableProducts.TANKUID) ON tableTrucks.UID = tableProducts.TRUCKUID"

' Begin the mess
strSQLWhere = ""
if (dbTruckID > 0) then strSQLWhere = strSQLWhere & " tableProducts.TRUCKUID = " & dbTruckID & " AND"
if (dbTankID > 0) then strSQLWhere = strSQLWhere & " tableProducts.TANKUID = " & dbTankID & "AND"

'The above if statemnt is the statement not being run. A valid value is -1071034568. The number is screwy because it is set to random. (not my choice and the table has dependent tables and is replicated)
if not strSQLWhere = "" then strSQLQuery = strSQLQuery & " WHERE" & left(strSQLWhere, Len(strSQLWhere) - 4)
strSQLQuery = strSQLQuery & " ORDER BY "
select case request("sortby&quot
case "1": strSQLQuery = strSQLQuery & "tableTrucks.NAME"
case "2": strSQLQuery = strSQLQuery & "tableDealers.NAME"
case "3": strSQLQuery = strSQLQuery & "tableProducts.QUANTITY DESC"
case else: strSQLQuery = strSQLQuery & "tableProducts.UID"
end select ************

Caution, dates on calendar are closer then they appear.

 

[vasah20]

where are you getting dbTankID from? if that's not coming out, then your problem is a lot earlier than that.

Print out the strSQLQuery and make sure that it is in fact the dbTankID that's screwing up on you.

post back for more help
leo

 

[trc]

I just figured it out. The values in the database were negative because the person who designed it made the primary key field random. So I changed the if statements to read:
if (dbTruckID <> 0) then strSQLWhere = strSQLWhere & &quot; tableProducts.TRUCKUID = &quot; & dbTruckID & &quot; AND&quot;
if (dbTankID <> 0) then strSQLWhere = strSQLWhere & &quot; tableProducts.TANKUID = &quot; & dbTankID & &quot;AND&quot;

This fixed it.
Thanks vasah20 for the help. ************

Caution, dates on calendar are closer then they appear.