?
Solved

SQL problem in ASP page

Posted on 2003-03-27
12
Medium Priority
?
285 Views
Last Modified: 2008-02-26
Hi all,

I am working on a WAP project and am experiencing problems passing a variable to an SQL statement. The reason why I say this is that the variable appears to be storing the information as I can print this variable onto the screen, the staement is OK when I pass the information directly to the statement but this is not flexible for me. Below is the code:

<%@ Language = "JScript"; %>
<% Response.ContentType = "text/vnd.wap.wml" %>
<?xml version="1.0"?>
<!-- #include file="adojavas.inc" -->
<!DOCTYPE wml PUBLIC "-//OPENWAVE.COM//DTD WML 1.3//EN" "http://www.openwave.com/dtd/wml13.dtd">

<!-- London Attractions ASP file -->

<wml>

     <head>
          <meta forua="true" http-equiv="Pragma" content="no-cache"/>
          <meta forua="true" http-equiv="Cache-Control" content="no-cache, must-revalidate"/>
     </head>

           <%
               conn = Server.CreateObject("ADODB.Connection");
               rs = Server.CreateObject("ADODB.Recordset");
               lbrRs = Server.CreateObject("ADODB.Recordset");


               conn.Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" +
                              Server.MapPath("city.mdb") + ";");

               //SQL Query
               initQuery = "SELECT LocationID, Name FROM Area";
                         
               rs.Open(initQuery, conn, adOpenForwardOnly, adLockReadOnly, adCmdText);
               
          %>

     <card id="area" title = "London Areas">
      <p>
        <b>The Attractions Page</b>
      </p>
      <p>
          <b>Please select the area you are visiting:</b><br/>

          <select name="area" title="Select Area">

          <%
               
               while (!rs.EOF)
               {
         
                    Response.Write("<option value=\" ");
                    Response.Write(rs("LocationID"));
                    //Response.Write(title=\" + rs("LocationID") + "\");
                    Response.Write("\">");
                    Response.Write(rs("Name"));
                    Response.Write("</option>");
                    rs.MoveNext();
               }
               
          %>

          </select>    
      </p>

       <do type="accept" label="OK">
          <go href="#venues" method="get">
               <postfield name="area" value="$(area)"/>
          </go>
       </do>

     </card>

     <card id="venues" title="Area Venues">
          <p>
               <b>List of venues:</b>
          </p>
          <p>

          <%

//Below is where Im experiencing problems. Please help thanks.

          place = Request.QueryString( "area");
          lbrQuery = "SELECT * FROM Attraction WHERE LocationID=' &place& ' ";
          lbrRs.Open(lbrQuery, conn, adOpenForwardOnly, adLockReadOnly, adCmdText);


               Response.Write( place);

               while( !lbrRs.EOF) {
                    Response.Write( lbrRs( "Name") + "<br/>");
                    lbrRs.MoveNext();
               }

               conn.Close();
          %>
          </p>

     </card>

</wml>
0
Comment
Question by:jimbo1278
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8217245
without knowning ANYTHING about yout table, I would suspect that LocationID is a NUMBER field, not a string, in which case, these lines:

 lbrQuery = "SELECT * FROM Attraction WHERE LocationID=' &place& ' ";
         lbrRs.Open(lbrQuery, conn, adOpenForwardOnly, adLockReadOnly, adCmdText);

will not work, as Access will try to find a LOCATIONID as a

 lbrQuery = "SELECT * FROM Attraction WHERE LocationID = " & place
         lbrRs.Open(lbrQuery, conn, adOpenForwardOnly, adLockReadOnly, adCmdText);

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8217253
or possibly,

lbrQuery = "SELECT * FROM Attraction WHERE LocationID = " + place;
lbrRs.Open(lbrQuery, conn, adOpenForwardOnly, adLockReadOnly, adCmdText);

0
 

Author Comment

by:jimbo1278
ID: 8219575
The LocationID is set as a text field, in the database its a 3 letter field to identify the area the attractions are in. ie LBR is London Bridge. Will the query work as it is ?
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 23

Expert Comment

by:adathelad
ID: 8224308
Hi,
Try this:

lbrQuery = "SELECT * FROM Attraction WHERE LocationID='"  & place & "'"

lbrRs.Open(lbrQuery, conn, adOpenForwardOnly, adLockReadOnly, adCmdText)

Cheers
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8224800
what do you mean by:

"the staement is OK when I pass the information directly to the statement but this is not flexible for me"

what is not FLEXIBLE?

what do you need?

AW

0
 

Author Comment

by:jimbo1278
ID: 8238216
If I said

lbrQuery = "SELECT * FROM Attraction WHERE LocationID='LBR'"

then this works but I have other 3 letter codes that I am passing the hope was that place would accept any 3 letter area code.




0
 
LVL 23

Expert Comment

by:adathelad
ID: 8238241
jimbo,

does this not work?
lbrQuery = "SELECT * FROM Attraction WHERE LocationID='"  & place & "'"

Cheers
0
 

Author Comment

by:jimbo1278
ID: 8241225
I tired what you said but still the same problem its as if the quey doesnt read the variable
0
 
LVL 8

Accepted Solution

by:
spongie earned 300 total points
ID: 8328240
Change this:

lbrQuery = "SELECT * FROM Attraction WHERE LocationID=' &place& '"

to this:

lbrQuery = "SELECT * FROM Attraction WHERE LocationID='" & place & "'"

I think you have included the variable [place] within the quotes thus making it a literal
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10360622
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in approximately 4 days.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help.jsp#hs5

walterecook
EE Cleanup Volunteer
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10392328
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: spongie {http:#8328240}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question