Topic awaiting preservation: SQL problem (ASP)

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:

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

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

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:

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

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.

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:

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

I wanted like:

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:

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

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

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

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