ASP sql select statement

scorpio · July 22nd, 2003, 01:19 PM

Can anybody help !

:(
I'm getting this error
        Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'questionNo ='.
/mmtec/mm32/quiz3.asp, line 12

when I do this
%
Set MyConn = Server.CreateObject("ADODB.Connection")
Set Rs=Server.CreateObject("ADODB.RecordSet")
MyConn.Open("mm32")
'now start the main loop to find all the questions
Rs.Open "SELECT * FROM Question WHERE questionNo=" & Request.QueryString("questionNo") , MyConn, 1, 2
if not Rs.EOF then
    Rs.movefirst
    do
        response.write "<b>" & Rs("questionNo") & ". " & Rs("questionText") & "</b><p>"
        'now display the available options

        Rs.Open "SELECT * FROM PossibleAnswer WHERE questionNo=" & Rs("questionNo"),MyConn, 1, 2
        if not Rs.EOF then
            Rs.movefirst
            do
                response.write Rs("answerText") & "<br>"
                Rs.movenext
            loop until Rs.EOF
        end if
        response.write "<p>"
        Rs.movenext
    loop until Rs.EOF
end if
Rs.Close
Myconn.close
%>
Any ideas gratefully received:)

:D

andrew

Imar · July 22nd, 2003, 02:09 PM

Hi Andrew,

Are you sure that your QueryString contains a decent value? From the error message, it appears that Request.QueryString("questionNo") is empty.

To find out, make a change to your code like this: (it's generally a good idea to create a separate variable for your SQL statement, so it's easier to debug):

Code:

Dim SQL
SQL = "SELECT * FROM Question WHERE questionNo=" & Request.QueryString("questionNo")

Response.Write("SQL is " & SQL)
Response.End

Rs.Open SQL , MyConn, 1, 2

Once you have written the SQL statement to the browser, I am almost sure you can see the problem. If you have fixed it, you can remove the .Write and .End lines again.

If the problem cannot be fixed, post the outcome of the .Write statement to this list so we can take a look at it.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

scorpio · July 29th, 2003, 01:37 PM

Thanks Imar , you were absolutely right, there was nothing in the query string.Sorry for taking so long to reply.
By the way, I'm trying to synchronise 2 combo boxes.the first combo box has a list of laws from a table in a database called Law and the second combo box has a list of lawSubsections.When a user clicks , say, Law#1 , I want only those subsections that go with law#! to appear in the second combo box.There are already some pieces of info on this in the farest reaches of the web but unfortuneately theye are very fragmantary.Do I need 2 select statements in the same .asp file?
Actually the way I am doing it I am getting the same mistake I got previosly which is Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'lawNo ='.

here is my code :
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
  Option Explicit
  Dim MyConn, Rs, MyLaw , Rs2 , MyComponent
  Set MyConn=Server.CreateObject("ADODB.Connection")
  Set Rs=Server.CreateObject("ADODB.RecordSet")
  MyConn.Open("mm32")
  Rs.Open "SELECT * From Law", MyConn, 1, 2
  Set Rs2=Server.CreateObject("ADODB.RecordSet")
  Rs2.Open "SELECT * From Component where lawNo=" & Request.QueryString("lawNo"), MyConn, 1, 2
%>

<html>
<head>
<title>Inputting a new Question</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<SCRIPT Language="JavaScript">
function mySubmit()
  {

  }

</SCRIPT>
</head>

<body>
<div align="center">
  <p><strong>Please use this form to input a new question </strong></p>

  <form name="form1" method="post" action="http://pertinax.cms.shu.ac.uk/mmtec/mm32/HandleInputQ.asp"">
    <p><strong>Choose a Law</strong>
      <select name="select">
        <%
  While NOT Rs.EOF
   MyLaw=Rs("lawNo") & " " & Rs("lawText")
%>
        <option value=<%=MyLaw%>><%=MyLaw%></option>
        <%
   Rs.MoveNext
  WEND
%>
      </select>
    </p>

    <p><strong>Which component of this law does your question most go with?</strong>
      <select name="select">
        <%
  While NOT Rs2.EOF
   MyComponent=Rs2("componentNo")
%>
        <option value=<%=MyComponent%>><%=MyComponent%></option>
        <%
   Rs2.MoveNext
  WEND
%>
      </select>
    </p>

    <p>
      <input type="submit" name="Submit" value="Submit" onClick="mySubmit()">
    </p>
  </form>
  <p><strong></strong> </p>
</div>
</body>
</html>

I hope this is not too irritatingly simple to bother with ,
I am a beginner and the ASP books I've got , which are all half a meter thick are totally rubbish

:(:(

:(:(:(:(:(

andrew

wolverinero79 · July 29th, 2003, 01:53 PM

Not only would i say you need 2 select statments, but you need to use Javascript to change the second combo box dynamically. There's a few places to get good code for this, but the best example i've found is here:

http://www.learnasp.com/learn/listdynamicdb.asp

The basic concept is that you build a 2-d array in Javascript to hold ALL record values and these values are ordered by the list of items in your first combo box. When the first list is changed, the db isn't requery-ed, but in fact the Javascript just uses a different part of the prebuilt array in order to display a new select list.

scorpio · July 30th, 2003, 05:34 AM

Thanks for the link,I'll give it a whirl.:D:D:D:D:D:D

andrew

scorpio · July 30th, 2003, 06:48 AM

By the way , I've just found something very similar somewhere else but the function call I'm using to get from the first combo box to the second is wrong.Can anybody spot what it is ?

It is giving the folowing error
line 19 object doesn't support this property or method
But I still think it's something to do with the onchange handler in the first combo box because line 19 is just Set catRs=Server.CreateObject("ADODB.RecordSet")

Anyway this is the code which is tantalisingly close to being correct , I think
<html>
<head>
<title>Inputting a new Question</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<SCRIPT Language="JavaScript">
function mySubmit()
  {
  }
  function submit1()
  {
  sel.form.submit();
  }
</SCRIPT>
</head>
<body>
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
  Set catConn=Server.CreateObject("ADODB.Connection")
  Set catRs=Server.CreateObject("ADODB.RecordSet")
  catConn.Open("mm32")
  catRs.Open "SELECT * From Law", catConn, 1, 2
  catRows = catRs.getRows
  catRs.Close
Set catRs = nothing
catChosen = Request.Form("CategorySelect")
showSubcat = False
If (("X" & catChosen) <> "X") AND (catChosen <> "NONE") Then
    catChosen = CInt(catChosen)
   showSubcat = True
     Set subRS = catConn.Execute("SELECT componentNo FROM Component" _
                            & " WHERE lawNo=" & catChosen _
                            & " ORDER BY componentNo")
    subRows = subRS.getRows
    subRS.Close
    Set subRS = nothing
End If
catConn.Close
Set catConn = nothing
%>

<P>
Which law does your question pertain to?<P>
First select a law and then, when the list of components
appears, select a component:<P>
<form name="form1" action="http://pertinax.cms.shu.ac.uk/mmtec/mm32/Inputaquestion4.asp" method="post" >
<SELECT Name="CategorySelect" MULTIPLE size=10
        onChange="document.form1.Submit1();">
    <OPTION Value="NONE">-- choose a category below--
<% For cnum = 0 To UBound(catRows,2) %>
    <OPTION Value="<%= catRows(0,cnum) %>"
            <% If catRows(0,cnum) = catChosen Then %>
            SELECTED
            <% End If %>
            ><%= catRows(1,cnum) %>
<% Next %>
</SELECT>
<SELECT Name="SubcategorySelect" MULTIPLE Size=10>
<% If showSubcat = False Then %>
    <OPTION Value="0">-- no subcategories yet --
<% Else %>
    <OPTION Value="0">-- choose a subcategory below--
    <% For cnum = 0 To UBound(subRows,2) %>
        <OPTION Value="<%= subRows(0,cnum) %>"><%= subRows(1,cnum) %>
    <% Next %>
<% End If %>
</SELECT>
  <p>
     <input type="submit" name="Submit" value="Submit" onClick="mySubmit()">
    </p>
  </form>
  <p><strong></strong> </p>
</body>
</html>

andrew