Microsoft OLE DB Provider for ODBC Drivers error "80040e14" [Microsoft][ODBC Microsoft Access Drive

Hi I am using asp to connect to an access database, do a simple search
query and return the results to the user, the database i am using is
very simple and has only text datatypes and one autonumber datatype -
yet I am getting the folowing error about a date syntax error->
any suggestions much appreciated!!


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query
expression 'MANUAL # LIKE '%Plant%' OR TITLE LIKE '%Plant%' OR VENDOR
LIKE '%Plant%''.
/searchdb.asp, line 141



My Code is as follows:










<%
If request.querystring<>"" then
If len(request.querystring("q"))>=3 then

' Those variable are needed to work with the database
DIM DB
DIM RS
DIM StrOpen
DIM StrOpenInContruction ' This variable is used to create variable
"StrOpen"

DIM PathToDatabase ' The location of our database within
our server


DIM NameOfTableInDB ' The name of the table in our Access
database. In our case
NameOfTableInDB="tblManuals"

DIM ColunmNameIntable(14)


ColunmNameIntable(0)="ID"
ColunmNameIntable(1)="MANUAL #"
ColunmNameIntable(2)="TITLE"
ColunmNameIntable(3)="VENDOR"
ColunmNameIntable(4)="CONTENT"
ColunmNameIntable(5)="KEY WORDS"
ColunmNameIntable(6)="YEAR"
ColunmNameIntable(7)="LOCATION - SECTION"
ColunmNameIntable(8)="LOCATION - SHELF"
ColunmNameIntable(9)="DOCUMENT LINK"
ColunmNameIntable(10)="EXTERNAL LINK"
ColunmNameIntable(11)="Retention Time"
ColunmNameIntable(12)="Reviewed By"
ColunmNameIntable(13)="Disposition Method"
ColunmNameIntable(14)="Responsibility"

' When displaying data we will use this values
DIM DataName(14)

DataName(0)="Id"
DataName(1)="Name"
DataName(2)="Email"
DataName(3)="Message"
DataName(4)="Content"
DataName(5)="Key Words"
DataName(6)="Year"
DataName(7)="Location - Section"
DataName(8)="Location on Shelf"
DataName(9)="Document Link"
DataName(10)="External Link"
DataName(11)="Retention Time"
DataName(12)="Reviewed By"
DataName(13)="Disposition Method"
DataName(14)="Responsibility"


' Keywords to search will be saved to variable "TheWords"
DIM TheWords
TheWords = request.querystring("q")

' Mode will be saved to this variable. Value will be "OR" or "AND"
DIM TheMode
TheMode = request.querystring("mode")


' When TheWords contains more than one keyword,
' keywords will be save to array "TheWordsArray"
DIM TheWordsArray
DIM MorethanOneKeywords ' If more than one keywords are introduced,
then we will set up MorethanOneKeywords value to "yes"
DIM Wmax ' Will be used to calculate maximum
number of keywords
DIM i,j ' Just counters

If instr(1,request.querystring("q")," ",1) >0 then
MorethanOneKeywords="yes"
TheWordsArray=Split(TheWords," ")
Wmax=ubound(TheWordsArray)
For i=0 to Wmax
if len(TheWordsArray(i))<3 then
Response.write ("Search Term " & TheWordsArray(i) & " is
very sort, so it has not been used

Re: Microsoft OLE DB Provider for ODBC Drivers error "80040e14" [Microsoft][ODBC Microsoft Access Dr

lolly wrote:
> Hi I am using asp to connect to an access database, do a simple search
> query and return the results to the user, the database i am using is
> very simple and has only text datatypes and one autonumber datatype -
> yet I am getting the folowing error about a date syntax error->
> any suggestions much appreciated!!
>
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query
> expression 'MANUAL # LIKE '%Plant%' OR TITLE LIKE '%Plant%' OR VENDOR
> LIKE '%Plant%''.
> /searchdb.asp, line 141
>
>



>
>
> DIM NameOfTableInDB ' The name of the table in our Access
> database. In our case
> NameOfTableInDB="tblManuals"
>
> DIM ColunmNameIntable(14)
>
>
> ColunmNameIntable(0)="ID"
> ColunmNameIntable(1)="MANUAL #"

You have a space in the above field name. Therefore you need to put it
in square brackets:

ColunmNameIntable(1)="[MANUAL #]"

The hash symbol tells Access to expect a date value to be passed. The
brackets prevent that problem. Ideally, you wouldn't have spaces,
symbols or reserved words in your column names. They always casue
problems at some stage...

Other things for your attention:

>
> ' Now, we will open the data base and perform the search
> Set DB = Server.CreateObject("ADODB.Connection")
> DB.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("Registry.mdb") '("DRIVER={Microsoft Access Driver
> (*.mdb)}; DBQ=" & PathToDatabase)

ODBC drivers have been deprecated. Change to using the OLEDB provider:
http://www.aspfaq.com/show.asp?id=2126

> RS.MoveFirst

You don't need to movefirst. A forward only cursor such as the default
one you are using opens at the first record.

> While Not RS.EOF
> 'Next 3 lines will display search results. We may change the way we
> are displaying the results
> For j=1 to 3
> Response.write (DataName(j) & ": " & RS.Fields
> (ColunmNameIntable(j)) & "
")
> next
> Response.write ("