help with query

jonah9 · #1 (**permalink**) 07-08-03, 04:40

please help. im not a programmer, i just pretended to be so i could get this job. anyway, heres my error message:

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

and heres my code:

<%
If IsObject(Session("detailpage_rs")) Then
Set rs = Session("detailpage_rs")
Else
sql = "SELECT [codename].[course code], [codename].[course name], [codename].[picture], [details].[shirase], [details].[info], [details].[note], [prices].[date], [prices].[price], [schedule].[day], [schedule].[schedule], [schedule].[location], [schedule].[meal], [schedule].[point] FROM (codename INNER JOIN prices ON [codename].[course code] =[prices].[course code]) INNER JOIN (details INNER JOIN schedule ON [details].[course code] =[schedule].[course code]) ON [codename].[course code] =[schedule].[course code] WHERE [codename].[course code] = " & Request.QueryString("code")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("detailpage_rs") = rs
End If
%>

thanks in advance,
Jonah

MrWizard · #2 (**permalink**) 07-08-03, 17:06

This error is often caused by including a column in your SQL statement that doesn't actually exist in the database. My suggestion would be to double check your SQL for typos...

When this happens to me, I take out portions of the SQL query until the error goes away... to help determine where the error might be.

Tim

jonah9 · #3 (**permalink**) 07-08-03, 21:55

hey MrWizard, thanks for the reply.
i did what you said and found that it all works fine without the WHERE...
so i doublechecked that there were no typos but it still didnt work. then, i went ahead and put a typo into the WHERE on purpose, just to see if it would give me the same message, and this time it said Expected 2 instead of 1.
so im still lost,
any ideas?

MrWizard · #4 (**permalink**) 07-09-03, 00:49

Quote:

Originally posted by MrWizard
This error is often caused by including a column in your SQL statement that doesn't actually exist in the database. My suggestion would be to double check your SQL for typos...

When this happens to me, I take out portions of the SQL query until the error goes away... to help determine where the error might be.

Tim

Are you sure that Request.QueryString("code") is returning a vaue?

You might try a response.write Request.QueryString("code") just befor the query, to see what the value is. Then, you might also want to ensure that [codename].[course code] is a numeric field... and not string data... (If it were string, you would want the end of the WHERE clause to be:

= '" & Request.QueryString("code") & "'" (added single quotes around the value)

Tim

jonah9 · #5 (**permalink**) 07-09-03, 01:22

ok, so you were right, [codename].[course code] is not solely numerical, its format is LETTERLETTERNUMBERNUMBER,
but, i tried adding = '" & Request.QueryString("code") & "'"
to the end of WHERE like so:

sql = "SELECT * FROM [codename] WHERE [codename].[course code] = '" & Request.QueryString("code") & "'"

but its still giving me the same error.
i also tried adding response.write Request.QueryString("code") before the query but only the error came up again.
i do see http://users2.ihost.biz/jonah/detail.asp?code=AV01
as the url though.
what do ya think?

jonah9 · #6 (**permalink**) 07-09-03, 01:27

hey, jusk kidding, what you said worked. i just tried it again and it worked! your cool man, real cool.

jonah9 · #7 (**permalink**) 07-09-03, 01:52

just when i thought it was safe...
i can get it to show up the first time i click the link, but if i go back and try again, or if i refresh, i get this error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

i did put in the response.write to check if the query was being sent, and it is because this error comes up just under where my query is displayed.

if youve got time, the file is here http://users2.ihost.biz/jonah/detailpage.asp?code=EA01

thanks again,
jonah

MrWizard · #8 (**permalink**) 07-09-03, 03:01

The error tells you to look at line 38. Since I do not have your source code, I have no idea what line 38 is...

However, since the page works the first time through, this leads me to believe that something is not getting properly set/cleared if the page is refreshed.

you also might want to check out:
http://support.microsoft.com/default...b;en-us;253627

It may or may not have anything to do with your situation.

Tim

jonah9 · #9 (**permalink**) 07-09-03, 04:00

theres nothing special on line 38, its just pulling fields from the database:
<%=Server.HTMLEncode(rs.Fields("cour se code").Value)%> 

i followed the link you gave me, and checked the error on many other web sites, but had no luck. they cant seem to explain why it would come up once, but not twice. heres my entire page if it helps.
thanks again,
Jonah

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>detailpage</title>
<meta http-equiv="Content-Type" content="text/html; charset=Shift_JIS">
</head>

<body>

<%
Session.timeout = 5
If IsObject(Session("detailpage_conn")) Then
Set conn = Session("detailpage_conn")
Else
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\jonah\db\db.mdb")
Set Session("detailpage_conn") = conn
End If
%>

<%response.write Request.QueryString("code")%>

<%
If IsObject(Session("detailpage_rs")) Then
Set rs = Session("detailpage_rs")
Else
sql = "SELECT [codename].[course code] AS code, [codename].[course name], [codename].[picture], [details].[shirase], [details].[info], [details].[note], [schedule].[day], [schedule].[schedule], [schedule].[location], [schedule].[meal], [schedule].[point], [prices].[date], [prices].[price] FROM (codename INNER JOIN prices ON [codename].[course code] =[prices].[course code]) INNER JOIN (details INNER JOIN schedule ON [details].[course code] =[schedule].[course code]) ON [codename].[course code] =[schedule].[course code] WHERE [codename].[course code] = '" & Request.QueryString("code") & "'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("detailpage_rs") = rs
End If
%>



<%=Server.HTMLEncode(rs.Fields("code ").Value)%> 
<%=Server.HTMLEncode(rs.Fields("cour se name").Value)%> 
<img src="http://hfi-tour.co.jp/imgs/<% Response.write(rs("picture")) %>"> 
<%=Server.HTMLEncode(rs.Fields("shir ase").Value)%> 
<%=Server.HTMLEncode(rs.Fields("info ").Value)%> 
<%=Server.HTMLEncode(rs.Fields("note ").Value)%> 

<%
On Error Resume Next
rs.MoveFirst
do while NOT rs.eof
%>

<%=Server.HTMLEncode(rs.Fields("day" ).Value)%> 
<%=Server.HTMLEncode(rs.Fields("sche dule").Value)%> 
<%=Server.HTMLEncode(rs.Fields("loca tion").Value)%> 
<%=Server.HTMLEncode(rs.Fields("meal ").Value)%> 
<%=Server.HTMLEncode(rs.Fields("poin t").Value)%> 
<%
rs.MoveNext
loop%>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<%=Server.HTMLEncode(rs.Fields("date ").Value)%> 
<%=Server.HTMLEncode(rs.Fields("pric e").Value)%> 
<%
rs.MoveNext
loop%>

</body>
</html>
<%
rs.close
set rs = nothing
conn.close
set conn=nothing
%>

MrWizard · #10 (**permalink**) 07-09-03, 04:14

I'm not sure about this, but at the bottom of the page, you close the rs object.

When the page is refreshed you're not redoing the query, but instead you call a session variable that was supposed to be the rs object.

But the rs object is closed at this point.

You may need to either, redo the entire query each time the page is accessed.... or, use the rs.getrows method to put the recordset into an array scoped for the session.

Tim

jonah9 · #11 (**permalink**) 07-09-03, 04:33

ok, thanks, ill look into it some more.
the other think i noticed is when i go back to the page where the query is coming from, and click on the same or any other link (essentially retrying the query) it doesnt work either.
i can only get the query to run once per each time i open my browser (closing the browser and reopening seems to reset everything and allow me to run any query on the page)

MrWizard · #12 (**permalink**) 07-09-03, 13:18

If you just remove the rs close and conn.close statements at the very bottom of the page, and try it out, you'll be able to see if that's the cause of the error.

Mind you, what I'm suggesting is a test, not a fix

Tim

jonah9 · #13 (**permalink**) 07-09-03, 21:16

after taking out the close recordset and conn as you suggested, i am still able to see the resulting page ONCE, but if i try again, i get the following error:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/jonah/detailpage.asp, line 39

i searched for this error, but could not find any explanation as to why it is able to produce the page once.
what do you think?

jonah9 · #14 (**permalink**) 07-09-03, 21:20

oh yeah, and you can see what i mean by going here:

http://www.jonah9.com/

its in japanese, but click a link on the left to see what i mean.

MrWizard · #15 (**permalink**) 07-09-03, 22:17

Quote:

Originally posted by jonah9
oh yeah, and you can see what i mean by going here:

http://www.jonah9.com/

its in japanese, but click a link on the left to see what i mean.

I've taken the liberty of changing your code a bit, as below...

I really don't think you gain much by assigning the rs object to a session variable... and it's probably not good programming practice anyway.

Try it out and let me know what you think.

Tim

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>detailpage</title>
<meta http-equiv="Content-Type" content="text/html; charset=Shift_JIS">
</head>

<body>

<%
Session.timeout = 5
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\jonah\db\db.mdb")
sql = "SELECT [codename].[course code] AS code, [codename].[course name], [codename].[picture], [details].[shirase], [details].[info], [details].[note], [schedule].[day], [schedule].[schedule], [schedule].[location], [schedule].[meal], [schedule].[point], [prices].[date], [prices].[price] FROM (codename INNER JOIN prices ON [codename].[course code] =[prices].[course code]) INNER JOIN (details INNER JOIN schedule ON [details].[course code] =[schedule].[course code]) ON [codename].[course code] =[schedule].[course code] WHERE [codename].[course code] = '" & Request.QueryString("code") & "'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
%>



<%=Server.HTMLEncode(rs.Fields("code ").Value)%> 
<%=Server.HTMLEncode(rs.Fields("cour se name").Value)%> 
<img src="http://hfi-tour.co.jp/imgs/<% Response.write(rs("picture")) %>"> 
<%=Server.HTMLEncode(rs.Fields("shir ase").Value)%> 
<%=Server.HTMLEncode(rs.Fields("info ").Value)%> 
<%=Server.HTMLEncode(rs.Fields("note ").Value)%> 

<%
On Error Resume Next
rs.MoveFirst
do while NOT rs.eof
%>

<%=Server.HTMLEncode(rs.Fields("day" ).Value)%> 
<%=Server.HTMLEncode(rs.Fields("sche dule").Value)%> 
<%=Server.HTMLEncode(rs.Fields("loca tion").Value)%> 
<%=Server.HTMLEncode(rs.Fields("meal ").Value)%> 
<%=Server.HTMLEncode(rs.Fields("poin t").Value)%> 
<%
rs.MoveNext
loop%>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<%=Server.HTMLEncode(rs.Fields("date ").Value)%> 
<%=Server.HTMLEncode(rs.Fields("pric e").Value)%> 
<%
rs.MoveNext
loop%>

</body>
</html>
<%
rs.close
set rs = nothing
conn.close
set conn=nothing
%>

Internet Services

Web Development

Digital Marketing

Consumer Tech