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>

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.

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?

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

<<
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

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.