#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    5
    Rep Power
    0

    ASP Beginner - need some help


    Hi,

    I am an ASP newbie. I am attempting to design a webpage based on an Access database. I have figured how to display the contents of an entire database table on a page but am now trying to display just the results of an SQL statement. I am receiving the following error:

    Microsoft JET Database Engine error '80040e14'

    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    /working_sql.asp, line 19


    Here is the code for the page:

    <%@ Language=VBScript %>
    <% Option Explicit %>
    <!--#include virtual="/ASPSecured/adovbs.inc"-->
    <!--#include virtual="db_conn2.asp"-->
    <html>

    <head>
    <meta http-equiv="Content-Language" content="en-us">
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>New Page 2</title>
    </head>

    <body>
    <%
    Dim strSQL
    strSQL = "SELECT * FROM Vehicles WHERE Category = RV's"
    Dim objRS
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open "strSQL", objConn

    Do While Not objRS.EOF

    Response.Write "<br> "
    Response.Write "<table border='0' width=500 id='table1'> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Stock:</td> "
    Response.Write "<td> " & objRS("stock") & "</td> "
    Response.Write "</tr> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Category:</td> "
    Response.Write "<td> " & objRS("category") & "</td> "
    Response.Write "</tr> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Engine:</td> "
    Response.Write "<td> " & objRS("engine") & "</td> "
    Response.Write "</tr> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Miles:</td> "
    Response.Write "<td> " & objRS("miles") & "</td> "
    Response.Write "</tr> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Price:</td> "
    Response.Write "<td> " & objRS("price") & "</td> "
    Response.Write "</tr> "
    Response.Write "</table> "
    Response.Write "<br> "
    Response.Write "<hr> "
    objRS.MoveNext
    Loop

    'Clean up our ADO objects
    objRS.Close
    Set objRS = Nothing

    objConn.Close
    Set objConn = Nothing

    %>
    </body>

    </html>

    Here is the code for db_conn2.asp:

    <%
    'Open up a connection to our Access database
    'We will use a DSN-less connection.
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Provider="Microsoft.Jet.OLEDB.4.0"
    objConn.Open Server.MapPath("dealer.mdb")

    'objConn.Open
    Response.Write "I have established the link to the database<br>"

    'Create a recordset object instance and retrieve the information
    'from the Vehicles table.
    'Dim objRS
    'Set objRS = Server.CreateObject("ADODB.Recordset")
    'objRS.Open "Vehicles", objConn, , , adCmdTable
    'Response.Write "I have opened the table 'Vehicles' successfully.<br><br>"


    %>


    Any advice would be appreciated.

    Thanks.

    Tim
  2. #2
  3. Senior Sarcasm Wizardess
    ASP Mastermind (5000+ posts)

    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2466
    you need to surround values of text fields with single quotes. not to mention you need to escape single quotes in a string, by adding another
    Code:
    "SELECT * FROM Vehicles WHERE Category = 'RV''s'"
    and since strSQL is a variable that holds your query ... drop the quotes
    Code:
    objRS.Open strSQL, objConn
    Quote of the Month:
    Planning: Much work remains to be done before we can announce our total failure to make any progress.

    Questions to Ponder:
    If convenience stores are open 24 hours a day, 365 days a year, why are there locks on the doors?

    iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
    copyrightę 2008 sbenj69
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    5
    Rep Power
    0

    New error


    Thanks for your help. I made the changes you suggested so that my code now looks like this:

    Dim strSQL
    strSQL = "SELECT * FROM Vehicles WHERE Category = 'RV''s'"
    Dim objRS
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open strSQL, objConn


    Now I am receiving this error:

    Microsoft JET Database Engine error '80040e14'

    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    /working_sql.asp, line 19



    Tim
  6. #4
  7. Senior Sarcasm Wizardess
    ASP Mastermind (5000+ posts)

    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2466
    what is your connection string?
  8. #5
  9. Lost In Real World
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2006
    Location
    Bodrum
    Posts
    683
    Rep Power
    404
    Are you sure that your table name is Vehicles
    A wise man remembers his friends at all times; a fool, only when he has need of them
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    5
    Rep Power
    0
    I have the connection string in an include file. Here is the code for the include file:

    <%
    'Open up a connection to our Access database
    'We will use a DSN-less connection.
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Provider="Microsoft.Jet.OLEDB.4.0"
    objConn.Open Server.MapPath("dealer.mdb")

    'objConn.Open
    Response.Write "I have established the link to the database<br>"

    'Create a recordset object instance and retrieve the information
    'from the Vehicles table.
    'Dim objRS
    'Set objRS = Server.CreateObject("ADODB.Recordset")
    'objRS.Open "Vehicles", objConn, , , adCmdTable
    'Response.Write "I have opened the table 'Vehicles' successfully.<br><br>"


    %>


    And yes I am sure that the table is name is Vehicles

    Thanks.

    Tim
  12. #7
  13. Senior Sarcasm Wizardess
    ASP Mastermind (5000+ posts)

    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2466
    change your connection string to this:
    Code:
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    dsnName = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("dealer.mdb") & ";Persist Security Info=False"
    objConn.ConnectionString = dsnName
    objConn.Open
  14. #8
  15. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    5
    Rep Power
    0
    Still no luck. I am receiving the following error:

    Microsoft JET Database Engine error '80040e14'

    Syntax error in FROM clause.

    /working_sql.asp, line 19

    Here is how my codes read now:

    <%@ Language=VBScript %>
    <% Option Explicit %>
    <!--#include virtual="/ASPSecured/adovbs.inc"-->
    <!--#include virtual="db_conn2.asp"-->
    <html>

    <head>
    <meta http-equiv="Content-Language" content="en-us">
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>New Page 2</title>
    </head>

    <body>
    <%
    Dim strSQL
    strSQL = "SELECT * FROM Vehicles WHERE Category = 'RV''s'"
    Dim objRS
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open strSQL, objConn, , , adCmdTable

    Do While Not objRS.EOF

    Response.Write "<br> "
    Response.Write "<table border='0' width=500 id='table1'> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Stock:</td> "
    Response.Write "<td> " & objRS("stock") & "</td> "
    Response.Write "</tr> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Category:</td> "
    Response.Write "<td> " & objRS("category") & "</td> "
    Response.Write "</tr> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Engine:</td> "
    Response.Write "<td> " & objRS("engine") & "</td> "
    Response.Write "</tr> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Miles:</td> "
    Response.Write "<td> " & objRS("miles") & "</td> "
    Response.Write "</tr> "
    Response.Write "<tr>"
    Response.Write "<td width='69'>Price:</td> "
    Response.Write "<td> " & objRS("price") & "</td> "
    Response.Write "</tr> "
    Response.Write "</table> "
    Response.Write "<br> "
    Response.Write "<hr> "
    objRS.MoveNext
    Loop

    'Clean up our ADO objects
    objRS.Close
    Set objRS = Nothing

    objConn.Close
    Set objConn = Nothing

    %>
    </body>

    </html>

    db_conn2.asp:

    <%
    'Open up a connection to our Access database
    'We will use a DSN-less connection.
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    Dim dsnName
    dsnName = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("dealer.mdb") & ";Persist Security Info=False"
    objConn.ConnectionString = dsnName
    objConn.Open


    'objConn.Open
    Response.Write "I have established the link to the database<br>"

    'Create a recordset object instance and retrieve the information
    'from the Vehicles table.
    'Dim objRS
    'Set objRS = Server.CreateObject("ADODB.Recordset")
    'objRS.Open "Vehicles", objConn, , , adCmdTable
    'Response.Write "I have opened the table 'Vehicles' successfully.<br><br>"


    %>



    What the heck am I doing wrong?

    FYI: If I use the connection string you gave me and try to open and display the table without the query string it works fine. ie objRS.Open "Vehicles", objConn, , , adCmdTable. It is just when I attempt to open the recordset with the SQL string that it errors out.


    Tim
  16. #9
  17. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    5
    Rep Power
    0
    Does anybody have any idea what I am doing wrong here?
  18. #10
  19. Moderator From Beyond
    ASP Mastermind (5000+ posts)

    Join Date
    Sep 2004
    Location
    Israel
    Posts
    31,135
    Rep Power
    2926
    maybe you're trying to use reserved words.
    try this:
    Code:
    strSQL = "SELECT * FROM [Vehicles] WHERE [Category] = 'RV''s'"

Similar Threads

  1. I am a beginner for ASP programming
    By Thamizhinpan in forum ASP Development
    Replies: 1
    Last Post: January 10th, 2007, 07:15 AM
  2. Replies: 3
    Last Post: August 11th, 2005, 03:07 PM
  3. Beginner and uploading file to server coding
    By ukdave in forum .NET Development
    Replies: 2
    Last Post: February 26th, 2005, 01:39 PM
  4. Beginner DataBind() Problem...
    By Currie in forum .NET Development
    Replies: 1
    Last Post: November 24th, 2004, 03:45 PM
  5. beginner needs help
    By saylengdoi in forum .NET Development
    Replies: 8
    Last Post: November 26th, 2003, 11:40 AM

IMN logo majestic logo threadwatch logo seochat tools logo