Closed Thread Icon

Topic awaiting preservation: Dynamic SQL Problem Pages that link to <a href="https://ozoneasylum.com/backlink?for=12961" title="Pages that link to Topic awaiting preservation: Dynamic SQL Problem" rel="nofollow" >Topic awaiting preservation: Dynamic SQL Problem\

 
Author Thread
Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 10-30-2003 09:18

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:

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).]

poi
Paranoid (IV) Inmate

From: France
Insane since: Jun 2002

posted posted 10-30-2003 09:25

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

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"
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 :

code:
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).]

Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 10-30-2003 22:00

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

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:

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") & "'"
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

poi
Paranoid (IV) Inmate

From: France
Insane since: Jun 2002

posted posted 10-31-2003 05:32

[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 :

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 [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

« BackwardsOnwards »

Show Forum Drop Down Menu