Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 22nd, 2003, 01:19 PM
Registered User
 
Join Date: Jul 2003
Location: sheffield, yorkshire, United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default ASP sql select statement


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
Reply With Quote
  #2 (permalink)  
Old July 22nd, 2003, 02:09 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old July 29th, 2003, 01:37 PM
Registered User
 
Join Date: Jul 2003
Location: sheffield, yorkshire, United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old July 29th, 2003, 01:53 PM
Registered User
 
Join Date: Jul 2003
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #5 (permalink)  
Old July 30th, 2003, 05:34 AM
Registered User
 
Join Date: Jul 2003
Location: sheffield, yorkshire, United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

andrew
Reply With Quote
  #6 (permalink)  
Old July 30th, 2003, 06:48 AM
Registered User
 
Join Date: Jul 2003
Location: sheffield, yorkshire, United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SELECT statement in ASP please help jennypretty Classic ASP Basics 15 December 3rd, 2008 01:03 AM
Construct a SQL Select Statement ricespn Beginning VB 6 1 May 28th, 2007 07:57 AM
SQL select statement problem shrisangeeta Classic ASP Databases 3 June 15th, 2006 10:28 AM
YYYY and MM in access' SQL select statement Condor76 Access VBA 1 February 15th, 2006 02:47 PM
help needed with a SQL select statement problem wslyhbb Java Databases 1 August 14th, 2003 07:30 AM



All times are GMT -4. The time now is 04:56 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.