Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: help with query

  1. #1
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Question Unanswered: help with query

    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

  2. #2
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    you've got to be kidding...

    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
    Tim

  3. #3
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Re: you've got to be kidding...

    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?

  4. #4
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    Re: you've got to be kidding...

    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
    Tim

  5. #5
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68
    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?

  6. #6
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Cool

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

  7. #7
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Unhappy

    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

  8. #8
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    line 38

    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
    Tim

  9. #9
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Post Re: line 38

    theres nothing special on line 38, its just pulling fields from the database:
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("cour se code").Value)%></font><br>

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

    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("code ").Value)%></font><br>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("cour se name").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><img src="http://hfi-tour.co.jp/imgs/<% Response.write(rs("picture")) %>"><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("shir ase").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("info ").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("note ").Value)%><br></font>

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

    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("day" ).Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("sche dule").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("loca tion").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("meal ").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("poin t").Value)%></font> </p>
    <%
    rs.MoveNext
    loop%>
    <%
    On Error Resume Next
    rs.MoveFirst
    do while Not rs.eof
    %>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("date ").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("pric e").Value)%><br></font>
    <%
    rs.MoveNext
    loop%>

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

  10. #10
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    just a guess...

    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
    Tim

  11. #11
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Re: just a guess...

    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)

  12. #12
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    a quick way to check out my idea...

    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
    Tim

  13. #13
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68
    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?

  14. #14
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    homepage

    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.

  15. #15
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    Re: homepage

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

    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("code ").Value)%></font><br>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("cour se name").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><img src="http://hfi-tour.co.jp/imgs/<% Response.write(rs("picture")) %>"><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("shir ase").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("info ").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("note ").Value)%><br></font>

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

    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("day" ).Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("sche dule").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("loca tion").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("meal ").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("poin t").Value)%></font> </p>
    <%
    rs.MoveNext
    loop%>
    <%
    On Error Resume Next
    rs.MoveFirst
    do while Not rs.eof
    %>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("date ").Value)%><br></font>
    <font size=1 face="lr oSVbN" color=#000000><%=Server.HTMLEncode(rs.Fields("pric e").Value)%><br></font>
    <%
    rs.MoveNext
    loop%>

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •