Topic awaiting preservation: Dynamic SQL Problem

Hi, guys. How are you?
May I ask this please?
I am trying to make dynamic sql statement.
In my html file, there are two dropdown box and product list in table.
User can pick their choice such as title and category from dropdown box.

Some reason, When I make just one dropdown box first, it worked all right. I mean product was shown according to the choice of dropdown box. But When I made one more dropdown box, it doesn't work. I mean first dropdown still work but when I chose one choice from both of them, second box doesn't work.

Here is my code:

sql = "SELECT Title, Colcategory, Price, Colstate FROM Tblcategory INNER JOIN (Tbladd INNER JOIN Tblstate ON Tbladd.State = Tblstate.ID ) ON Tbladd.Category = Tblcategory.ID "

If Not IsEmpty(Request("state")) Then 

     sql = sql & " Where Tblstate.Colstate = '" & Request("state") & "'"	

End If 

If Not IsEmpty(Request("title")) Then

    ' Response.Write(Request("title"))

	sql = sql & " AND Tbladd.Title = '" & Request("title") & "'"

End If

First if statement adding sql statement to existing sql statemet. But not second if statement one. I don't see why.
We can do something like:

where tblName.FiledName = 'Hiroki' AND tblName2.FieldName2 = 'Tom'

Hmm....
Believe or not, it seems to be working until an hour ago. I came back from dinner then did it again. Then I realized it not working anymore..

Aha......Help.

Hiroki Kozai

[This message has been edited by Hiroki (edited 10-30-2003).]

Hiroki: double check what you write. If the "state" field is empty the sql string has no clause Where. Well, I'm reluctant to ASP, but here comes a ( try of ) bug fix

sql = "SELECT Title, Colcategory, Price, Colstate FROM Tblcategory INNER JOIN (Tbladd INNER JOIN Tblstate ON Tbladd.State = Tblstate.ID ) ON Tbladd.Category = Tblcategory.ID Where"

If Not IsEmpty(Request("state")) Then

    sql = sql & " Tblstate.Colstate = '" & Request("state") & "'"

    If Not IsEmpty(Request("title")) Then

        sql = sql & " AND Tbladd.Title = '" & Request("title") & "'"

    End If

End If

If IsEmpty(Request("state")) Then

    If Not IsEmpty(Request("title")) Then

        sql = sql & " Tbladd.Title = '" & Request("title") & "'"

    End If

End If

 

If Not IsEmpty(Request("title")) Then

    Response.Write(Request("title"))

End If

Sorry, I have no ASP server, nor do I remember the syntax of the if then else statement in ASP. Whatever, it's ugly, but it should work.

On the other hand, I'm not familiar with ASP's SQL syntax to make inner joins, but to me your where tblName.FiledName = 'Hiroki' AND tblName2.FieldName2 = 'Tom' query would in return every or none of the records of the tables if the join doesn't work.
What kind of messages do you get with your queries ?

I've tried to simplify the query and got :

sql = "SELECT Title, Colcategory, Price, Colstate FROM Tblcategory, Tblstate, Tbladd WHERE Tbladd.Category = Tblcategory.ID AND Tbladd.State = Tblstate.ID"

If Not IsEmpty(Request("state")) Then

    sql = sql & " AND Tblstate.Colstate = '" & Request("state") & "'"

End If

If Not IsEmpty(Request("title")) Then

    sql = sql & " AND Tbladd.Title = '" & Request("title") & "'"

End If

I may have forgotten some things but you got the idea.

Hope that helps,
Mathieu "POÏ" HENRI

[This message has been edited by poi (edited 10-30-2003).]

Hi, Poi. Many many thanks for pointing my mistakes.
I am just thinking of what you said and your code.

sql = "SELECT Title, Colcategory, Price, Colstate FROM Tblcategory INNER JOIN (Tbladd INNER JOIN Tblstate ON Tbladd.State = Tblstate.ID ) ON Tbladd.Category = Tblcategory.ID Where"

I wondered what would happen if I do like above. I mean if user doesn't choose none from boxes, there will be no point to have where in parents sql. I re-consider then I code like belowe:

sql = "SELECT Title, Colcategory, Price, Colstate FROM Tblcategory INNER JOIN (Tbladd INNER JOIN Tblstate ON Tbladd.State = Tblstate.ID ) ON Tbladd.Category = Tblcategory.ID "

If Not IsEmpty(Request("state")) Then 

     sql = sql & " Where Tblstate.Colstate = '" & Request("state") & "'"	

		 If Not IsEmpty(Request("title")) Then

		 		sql = sql & " AND Tbladd.Title = '"& Request("title") & "'"

		 End If

Else

		sql

		If Not IsEmpty(Request("title")) Then

			 sql = sql & " WHERE Tbladd.Title = '" & Request("title") & "'"

		End IF

End If

quote:

---

If the "state" field is empty the sql string has no clause Where.

---

I was trying to fix that point. Then I decided to nest if statement cuz it is easy to understand to my brain.
But doesn't work yet. Some reason, When I choose one from both dropdown, some products are shown. But never come up anything when I choose just one of them.

Would you see anything logical error???
Help~.



Hiroki Kozai

[quick reply]

To answer your question about what would happen if no value is selected in the dropdown with the 1st piece of code, it would certainly bug. As I said I'm not familiar with with inner join( ... ) syntax, but I guess you could simply do this :

sql = "SELECT Title, Colcategory, Price, Colstate FROM Tblcategory INNER JOIN (Tbladd INNER JOIN Tblstate ON Tbladd.State = Tblstate.ID ) ON Tbladd.Category = Tblcategory.ID Where [b]true[/b]"

If Not IsEmpty(Request("state")) Then

    sql = sql & " [b]AND[/b] Tblstate.Colstate = '" & Request("state") & "'"

        If Not IsEmpty(Request("title")) Then

            sql = sql & " AND Tbladd.Title = '" & Request("title") & "'"

    End If

End If

If IsEmpty(Request("state")) Then

    If Not IsEmpty(Request("title")) Then

        sql = sql & " [b]AND[/b] Tbladd.Title = '" & Request("title") & "'"

    End IfEnd If If Not IsEmpty(Request("title")) Then

    Response.Write(Request("title"))

End If

But that solution is *ugly*

If ASP accept the syntax of the simplified piece of code I wrote in my first post, I higly recommend you to use it. It's quite easy. I do the inner join by hand with 2 tests in the where.

Now, regarding the latset code snippet you wrote, at first sight the only thing puzzling me is the lonesome sql statement right after the Else. On the other side, if you still have some troubles with your queries, you should display them and try ( and fix ) them directly in your database. You've got 2 dropdown, so you have a maximum of 4 sql queries possible.

[/quick reply]
Cheers,

Mathieu "POÏ" HENRI