Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jan 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy using the SELECT command help

    I used the select command to display certain parts of info for my page from a database. Up until now, i've used an autonumber column, but now, i would like to use an autonumber column or another so there are two ways to select it. Im not sure how to do this, i got the first one to work with the 2nd one in there, but only the first one does anything, here is the code i have, please help



    <% 'specify the provider
    strProvider="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("data.mdb")

    'create and open a connection
    set dbConn = Server.CreateObject("ADODB.Connection")
    dbConn.Open strProvider
    set dbConn2 = Server.CreateObject("ADODB.Connection")
    dbConn2.Open strProvider %>
    </head>

    <body>

    <div class="header"><h3></span></h3></div>

    <hr size=1 width=90%>

    <div align="center">
    <form method="post">
    <input type="text" size="20" name="idform"></input>
    <input type="submit" value="Submit"></input>
    </form>
    <% rowCount=0
    'define the options query
    strSQL="SELECT DISTINCT app_name_version FROM tbl1"

    'create and open the options recordset
    set rsType=Server.CreateObject("ADODB.Recordset")
    rsType.Open strSQL, dbConn, , , adCmdText %>

    <form method="post">
    Choose Control Number

    <select name="prodType">
    <% do until rsType.EOF %>
    <option><%= rsType("app_name_version")%>
    <% rsType.MoveNext
    loop
    'close options recset and flush from memory
    rsType.Close
    set rsType = Nothing %>
    </select> <input type="submit" value="Submit">
    </form>

    <% if not isEmpty(Request("prodType")) OR not isEmpty(Request("idform")) then
    strType=Request("prodType")
    strType2=Request("idform")
    %>
    <h5>. . . retrieved <%= strType %> <%= strType2 %>records from the tbl1 Table</h5>

    <% 'define the tbl1 query
    strQuery2 = "SELECT * FROM tbl1 WHERE app_name_version = '" & strType & "'"
    strQuery = "SELECT * FROM tbl1 WHERE id = " & strType2 &""
    'create and open the selected recordset
    set rsProds=Server.CreateObject("ADODB.Recordset")
    set rsProds2=Server.CreateObject("ADODB.Recordset")
    rsProds.Open strQuery, dbConn, , , adCmdText
    rsProds2.Open strQuery2, dbConn2, , , adCmdText %>


    When i select the data from the ID column it works fine, but when i try to use the app_name_version column, it fails, i get this error message, what am i doing wrong?

    Error Message:

    Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression 'id ='.

  • #2
    New Coder
    Join Date
    Jun 2002
    Location
    London & Oxford
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's very difficult to diagnose this without seeing the whole SQL statement that is causing the error. If I were you, I'd comment out the line which executes the SQL statement and replace it with:

    response.write "<h3>" & strQuery2 & "</h3>"

    or similar and then post the whole SQL statement back here.
    As easy as 3.1415926535897932384626433832795028841

  • #3
    New to the CF scene
    Join Date
    Jan 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    what do you mean by the whole sql statement?

  • #4
    New Coder
    Join Date
    Jun 2002
    Location
    London & Oxford
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I mean the whole string as passed to the connection object. In this case, strQuery2 in it's final form with the variable strType included in full.
    As easy as 3.1415926535897932384626433832795028841

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I already suggested this to psyc635 with regards to SQL Statements... if you recall I said something like:

    "When you're having problems with a SQL Statement, the first step in debugging is to do this:"

    MYSQLSTATEMENT = "WHATEVER"
    Response.Write(MYSQLSTATEMENT) : Response.End

    That way, you can see EXACTLY what the database is trying to execute. Usually it's much easier to spot syntax/datatype errors this way, which is always the first debugging step.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

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