Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: Need Help Using ASP to Access Data

    Hi Folks,

    I'm pretty green when it comes to ASP, so I'm hoping someone can help me out.

    I'm putting archived articles from a sustainable agriculture magazine onto a website and I want folks to be able to search the articles by various criteria.

    You can try it at:
    http://www.tilthproducers.org/journalportalNEWasp.htm

    The articles are stored in an Access database. So far it works if the search is for the year of the the article, the year being stored in the table in a column formatted as "number".

    I'm working on the "search by subject" part now and it's not working. It's virtually the same code as for the "search by year", the only difference being the subject is stored in the table in a column formatted as "text". Some of the subjects are one word such as "crops" or two or three words such as "Farm Labor" or "Tools and Methods". I'm wondering if it's something to do with the spaces or just something stupid I'm not seeing.

    HELP!

    The code below generates the following error messages, either:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Subject = Farm Labor'.

    /tilthartsbysub.asp, line 38

    or

    Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

    /tilthartsbysub.asp, line 38

    <%
    'Create a variable to hold the region from previous page
    dim holder
    holder = Request ("artsub")

    dim frmname 'Create a variable to hold the name of each form starting with 0
    frmname = 0

    'Create a connection to the database and a recordset to hold the data returned
    Set articleConn = Server.CreateObject("ADODB.Connection")
    articleFilePath = Server.MapPath("\_private\tiltharts.mdb")
    articleConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & articleFilePath & ";"
    Set rs=Server.CreateObject("ADODB.Recordset")

    sql = "SELECT * FROM Articles WHERE Subject = " & holder

    'Open the recordset using the SQL statement and connection object above
    rs.open sql, articleConn

    'Write out the recordset returned until end of file reached
    Response.write "<table border=1 BGCOLOR='#FFFFCC'>"
    'Set up the table from here starting with R.W "<TR>...

    'Write out the recordset returned until end of file reached

    Response.write "<tr><td><h1>Articles with the Subject: " & holder & ":</h1>"

    Response.write "<table border='1' width='488'><tr><th width='89'>READ<th width='143'>Date</td><th width='105'>Title</td><th width='123'>Author</td></tr>"

    Do while not rs.eof
    frmname = frmname+1
    Response.write "<tr><td><form name = " & frmname & " action='tilthartsASP.asp'><input type='hidden' name='id' value=" & rs("Reference") & "><input type='submit' value='Read it!'></form></td>"
    Response.write "<td>" & rs("Date") & "</td>"
    Response.write "<td>" & rs("Title") & "</td>"
    Response.write "<td>" & rs("Author") & "</td></tr>"
    rs.MoveNext
    Loop


    'Finishes the script and closes the table
    Response.write "</td></tr>"

    'We are done, close and destroy objects
    rs.Close
    Set rs=Nothing
    Set articleConn=Nothing


    %> </table>

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    In SQL where you have text strings, the text must be surrounded by single quotes:

    SELECT * FROM myTable WHERE Column1 = 'Blah Blah Blah'

    Now, that will do an exact match for 'Blah Blah Blah', but if you want to find 'blah blah blah' (lower case), replace the = sign with the keyword LIKE:

    SELECT * FROM myTable WHERE Column1 LIKE 'Blah Blah Blah'

    That will find mixed cases, but if you want to find the text within a larger block of text, you need to use the percent sign to designate wildcards (at least, that's what it is in MS SQL Server, maybe someone with more experience with Access can validate that). So if you just wanted to find "lah" in the string "blah" it would be:

    SELECT * FROM myTable WHERE Column1 LIKE '%lah'

    Those percents can go anywhere in the string to represent one or more characters including spaces.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Aug 2004
    Posts
    3
    Thanks, Seppuku.

    That helped a little. I think my issue is with the concatenation.

    How should this SQL statement be written?

    Below is the code that returns the error message:"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression 'Subject = 'Crops'. "

    sql = "SELECT * FROM Articles WHERE Subject = '"& holder' ORDER BY Year"

    In the above code, Articles, Subject and Year are column headings. There is a variable named "holder" that holds the word "Crops" from the form on the previous page. It could also be "Fertility" or "Weed Control" etc.

    The server is recognizing the variable that is concatenated using the "&" symbol.

    I think I'm just not getting the single quotes and the double quotes in the right places.

    Any suggestions?

  4. #4
    Join Date
    Aug 2004
    Posts
    3
    Hey,Seppuku!

    I got it (using the trial and error message method)!

    It's:

    sql = "SELECT * FROM Articles WHERE Subject = '"& holder &"' ORDER BY Year"

    Your help with the single quotes did the trick, I just needed to find out where to put them.

    Thanks again,

    Chrys

  5. #5
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    <<
    Some of the subjects are one word such as "crops" or two or three words such as "Farm Labor" or "Tools and Methods".
    >>

    From what you wrote it seems like you'd still want to use Like somewhere in your SQL statement instead of just =.

    And maybe this will give you some ideas:

    Classic ASP Design Tips - Search For Keywords on Multiple Fields
    http://www.bullschmidt.com/devtip-se...iplefields.asp
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  6. #6
    Join Date
    Aug 2004
    Posts
    13
    The easiest resource I found on accessing a database is www.asp101.com, click on samples, then on simple database connection. Just read the comments on Show The Asp Version, I think that there is even a Asp.Net sample there. Very cool. Just take your time and you'll be up and running in no time. Just remember to close your recordsets and set the server variables to nothing before you end your script.

Posting Permissions

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