Objectmix
Tags Register Mark Forums Read

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%''. : Inetserver

This is a discussion on 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%''. within the Inetserver forums in Microsoft Tools category; 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: <html> <head> <title>Untitled ...


Object Mix > Microsoft Tools > Inetserver > 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%''.

Inetserver Microsoft Inet server asp, iis, ftp, smtp and security related discussions

Reply

 

LinkBack Thread Tools
  #1  
Old 08-01-2006, 10:13 AM
Junior Member
  
Join Date: Nov 2009
Posts: 0
Application Development is on a distinguished road
Default 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%''.
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:


<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>

<body>

<%
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 <b>" & TheWordsArray(i) & "</b> is
very sort, so it has not been used<HR>")
TheWordsArray(i)=""
end if
next
end if


' First we will get "StrOpen", wich is latter use to filter the
database

If MorethanOneKeywords="yes" then

if TheMode="OR" then
For i=0 to Wmax
if TheWordsArray(i)<>"" then
For j=1 to 3
StrOpen= StrOpen & " OR " & ColunmNameIntable(j) & " LIKE
'%" & TheWordsArray(i) & "%'"
next
end if
next
end if

if TheMode="AND" then
For j=1 to 3
StrOpenInContruction=""
For i=0 to Wmax
if TheWordsArray(i)<>"" then
StrOpenInContruction= StrOpenInContruction & " AND " &
ColunmNameIntable(j) & " LIKE '%" & TheWordsArray(i) & "%'"
end if
next
StrOpen=StrOpen & " OR (" & Right
(StrOpenInContruction,Len(StrOpenInContruction)-5) & ")"
next
end if

else

For j=1 to 3
StrOpen= StrOpen & " OR " & ColunmNameIntable(j) & " LIKE '%" &
TheWords & "%'"
next

end if

StrOpen= "SELECT * FROM " & NameOfTableInDB & " WHERE" & Right
(StrOpen,Len(StrOpen)-3)


' 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)
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open StrOpen, DB

' Finally we will show the maching records

If RS.EOF And RS.BOF Then
'If we get here it means we have selected no rows from the table in
our database
Response.write ("There are 0 records.")
else
'If we get here it means we have selected one or more rows from the
table in our database
RS.MoveFirst
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)) & "<BR>")
next
Response.write ("<HR>")
RS.MoveNext
Wend
End If

else
Response.write ("Very sort search name. Please try again.")
end if

end if %>



</body>
</html>

Reply With Quote
  #2  
Old 08-01-2006, 11:02 AM
Junior Member
  
Join Date: Nov 2009
Posts: 0
Application Development is on a distinguished road
Default Re: 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%''.

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

<snipped>

>
>
> 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)) & "<BR>")
> next
> Response.write ("<HR>")
> RS.MoveNext
> Wend

While... Wend has been deprecated. Change to Do Until... Loop.

Is it a very old book you are using? Or is that what they are still
teaching in class these days?

--
Mike Brind

Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools


Similar Threads

Thread Thread Starter Forum Replies Last Post
Microsoft OLE DB Provider for ODBC Drivers error '80004005' usenet Inetserver 0 05-30-2007 08:27 AM
ERROR [07002] [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect usenet DOTNET 3 05-05-2006 07:11 AM
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. usenet Inetserver 2 03-09-2005 05:15 AM
[Microsoft][ODBC Microsoft Access Driver] Disk or network error usenet Inetserver 1 02-16-2005 08:12 AM
ERROR [07002] [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect usenet DOTNET 0 07-29-2004 02:46 AM


All times are GMT -5. The time now is 12:18 PM.