Closed Thread Icon

Topic awaiting preservation: SQL problem (ASP) (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=12982" title="Pages that link to Topic awaiting preservation: SQL problem (ASP) (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: SQL problem (ASP) <span class="small">(Page 1 of 1)</span>\

 
Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 11-15-2003 06:13

Hi, guys. How are you? Please help me.
I am having a problem to make WHERE AND SQL statement using ASP.
Here is my code:

code:
sql = "SELECT WineryName, Name, Region, Price, Star, Vintage, Classic, Screw, AirNZ, Cuisine FROM Winery INNER JOIN" 
sql = sql & "(Wine_Winery INNER JOIN Wine ON Wine_Winery.WineID = Wine.WineID )"
sql = sql & "ON Winery.WineryID = Wine_Winery.WineryID WHERE Wine_Winery.Price < 30"
If Not IsEmpty(Request("winery")) Then
sql = sql & " AND Winery.WineryName = '" & Request("winery") & "'"

End If
If Not IsEmpty(Request("star")) Then
sql = sql & " AND Wine_Winery.Star= '" & Request("star") & "'"
End If



In my asp file, there are two drop down boxes, which is about Winery and Star. I want to query data according to drop down boxes. As you can see, there are two if statements above. If I take out one of them, works fine! But some reason, they don't live togther. Do you know why???
Please help.

Hiroki Kozai

poi
Paranoid (IV) Inmate

From: France
Insane since: Jun 2002

posted posted 11-15-2003 12:41

Hiroki: have you response.write( ... ) the sql query in the 3 or 4 cases to see if you generate it well ?
I'm pretty sure you didn't. If you had done half of the basic debbuging job you'd have fixed that by yourself.

Sorry if it sound harsh, but I really have the feeling that you make no effort to try to debug your code or understand what you actually write. No surprise if your code doesn't work then. I hope to be wrong, but you remind me a "coder" I knew who did no effort neither to learn nor to remember the things he asked me 3 times a day. This guy is a barman now.

Mathieu "POÏ" HENRI

Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 11-16-2003 02:27

Hi, Poi. Thank you for your reply.
I did something like:
Response.write(sql)
Then I saw that sql statemen was working fine.
And I guessed my problem was.....Hmmm...hard to explain.
In error message:

quote:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.



I seem to be able to choose only existing data. When I query unexisting data, I always get that message.
so I did something like:

code:
<%Do Until Not rs.EOF AND recct >= maxrecs%>
<tr><td><%If rs.EOF Then%>No product found <%End IF%></td></tr>
<%
Response.Write("<tr>")
Response.Write("<td><b>")
Response.Write(rs("WineryName") & "&nbsp;" &rs("Name"))
Response.Write("</b></td>")
Response.Write("</tr>")
Response.Write("<tr>")
Response.Write("<td>")
Response.Write("$" &rs("Price") &"&nbsp;&nbsp;&nbsp;")
%>



I thought if data is not existing, I could show No product found in the table. But doesn't work well.
Do you know why???

Hiroki Kozai

BikerSoft
Obsessive-Compulsive (I) Inmate

From: Frederick, MD, USA
Insane since: Nov 2003

posted posted 11-16-2003 08:53

The code that checks whether the recordset is empty is fine, but the rest of it gets executed either way. You have to put the actual display in an else section. If not it's going to display that nothing was found then try to access the recordset anyway.

Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 11-17-2003 00:41

Hi, BikerSoft. Welcome to Ozoneasylum and many many thanks for your reply.
I was trying to fix that problem but I realized my SQL problem.
I am afraid I have to go back to that point.
I did response.write(sql) then I realized it did wrong thing.
Now those things are gone. But one last thing I don't like is impossible to figure out how to solve.
When I open file, I saw my SQL was printed like:

code:
SELECT *  FROM Winery  WHERE Wine_Winery.Price < 30 AND Winery.WineryName = ''



I wanted like:

code:
SELECT *  FROM Winery  WHERE Wine_Winery.Price < 30



It is because user hasnot chosen any dropdown box value yet. I don't know why it happened like that.
My asp file is:

code:
<!-- This is main sql statement -->
sql = "SELECT * FROM Winery WHERE Wine_Winery.Price < 30"

<!-- This IF statement is additional sql statement if user choose any winery otherwise sql will be itself-->

If Request("winery")= "1" then
sql = sql & ""
else
sql = sql & " AND Winery.WineryName = '" & Request("winery") & "' "
End IF

<!-- This is drop down box where each value for sql come from -->
<select name="winery">
<option value="1"></option>
<%
Dim rs_Winery, sql_Winery
sql_Winery = "SELECT WineryName FROM Winery"
Set rs_Winery = conn.execute(sql_Winery)
While NOT rs_Winery.EOF%>
<option value="<%=rs_Winery("WineryName") %>" <% If Request("winery") = rs_Winery("WineryName") Then %>Selected<% End If %>><%= rs_Winery("WineryName") %></option>
<% rs_Winery.MoveNext
Wend
rs_Winery.Close
Set rs_Winery = Nothing
%>
</select>



Do you see something???
Please help.




Hiroki Kozai

Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 11-17-2003 01:56

Hi, guys.
Many thanks.
I figure it out.
I did something like:

code:
If  Request("winery")= "1" then 
sql = sql & ""
elseif Not IsEmpty(Request("winery")) Then
sql = sql & " AND Winery.WineryName = '" & Request("winery") & "' "
end if



Then it worked fine.
Cheers.

Hiroki Kozai

Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 11-17-2003 02:39

Hi, Poi. Really thanks for telling me that.
Now my sql does the job the way I want.
And BikerSoft.
Now I am back to that problem which is about display things according to the SQL
Cheers.

Hiroki Kozai

« BackwardsOnwards »

Show Forum Drop Down Menu