|
 |
asp_databases thread: Database syntax Error
Message #1 by tim.puffer@t... on Wed, 14 Nov 2001 21:21:57
|
|
in my search code i am asking the visitor to tell what text they are
searching for with the varfind and which field in the database to search
in with varin.
here is my code:
im varfind, varin, cnEmps, connstr, TheMessage, rsEmps
varFind = request.form("find")
varin = request.form("in")
response.write varfind
response.write varin
set cnEmps = server.createobject ("adodb.connection")
connstr = "driver={microsoft access driver (*.mdb)};" & _
"dbq=" & server.MapPath("thermo.mdb")
cnEmps.open connstr,"", ""
Select Case UCase (Request.QueryString("view"))
Case "ALL"
TheMessage = "All Employees"
sqltext = "SELECT * FROM empdir"
Case "DEPARTMENT"
TheMessage = "For Department " & Request.QueryString
("Department")
sqltext = "SELECT * FROM empdir WHERE department = '" &
Request.Querystring("Department") &"'"
Case Else
TheMessage = "Search Results"
sqltext = "SELECT * FROM empdir WHERE %" & varin & "%'
LIKE '%" & varfind & "%'"
End Select
set rsEmps = cnEmps.Execute(sqltext) <<<< this is line 24 >>>>>>
%>
this is the result that i get.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
expression '%name%' LIKE '%tim%''.
/phone/results 2.asp, line 24
any help would be appreciated
Message #2 by Imar Spaanjaars <Imar@S...> on Wed, 14 Nov 2001 22:35:19 +0100
|
|
You should do a Response.Write of sqltext right before you execute the
command and post the result of that to this list so we can take a look at
it. It's easier to debug a full SQL command than to browse through the code
and make some assumptions on how it will look.
I notice you are using a rather old way to connect to your database. Look
at the following address for a better way to connect:
http://www.able-consulting.com/ado_conn.htm#OLEDBProviderForMicrosoftJet
I wouldn't be surprised if that would solve your problem, as Access used
just the * as a wildcard in previous versions.
HtH
Imar
At 09:21 PM 11/14/2001 +0000, you wrote:
>in my search code i am asking the visitor to tell what text they are
>searching for with the varfind and which field in the database to search
>in with varin.
>
>here is my code:
>
>im varfind, varin, cnEmps, connstr, TheMessage, rsEmps
> varFind = request.form("find")
> varin = request.form("in")
> response.write varfind
> response.write varin
>
>set cnEmps = server.createobject ("adodb.connection")
>connstr = "driver={microsoft access driver (*.mdb)};" & _
> "dbq=" & server.MapPath("thermo.mdb")
>cnEmps.open connstr,"", ""
>Select Case UCase (Request.QueryString("view"))
> Case "ALL"
> TheMessage = "All Employees"
> sqltext = "SELECT * FROM empdir"
> Case "DEPARTMENT"
> TheMessage = "For Department " & Request.QueryString
>("Department")
> sqltext = "SELECT * FROM empdir WHERE department = '" &
>Request.Querystring("Department") &"'"
> Case Else
> TheMessage = "Search Results"
> sqltext = "SELECT * FROM empdir WHERE %" & varin & "%'
>LIKE '%" & varfind & "%'"
>End Select
>set rsEmps = cnEmps.Execute(sqltext) <<<< this is line 24 >>>>>>
>
>
>%>
>
>this is the result that i get.
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
>expression '%name%' LIKE '%tim%''.
>
>/phone/results 2.asp, line 24
>
>
>any help would be appreciated
>
>
Message #3 by "Drew, Ron" <RDrew@B...> on Wed, 14 Nov 2001 16:56:02 -0500
|
|
Not sure how this QueryString works with view and department...but I did see
a problem in the script with quotes..
I do not normally mix the 2 (querystring and request.form) ..have a radio
button ALL, DEPT or FIND and pass it along with the other request.form
stuff. Make sure you test for correct entries on varin so it matches the
column name on the table and varfind is not null.
Select Case UCase(Request.QueryString("view"))
Case "ALL"
TheMessage = "All Employees"
sqltext = "SELECT * FROM empdir"
Case "DEPARTMENT"
TheMessage = "For Department " & Request.QueryString("Department")
sqltext = "SELECT * FROM empdir WHERE department = '"
sqltext = sqltext & Request.Querystring("Department") &"'"
Case Else
TheMessage = "Search Results"
sqltext = "SELECT * FROM empdir "
sqltext = sqltext & "WHERE " & varin
sqltext = sqltext & "LIKE '%" & varfind & "%'"
End Select
-----Original Message-----
From: tim.puffer@t... [mailto:tim.puffer@t...]
Sent: Wednesday, November 14, 2001 4:22 PM
To: ASP Databases
Subject: [asp_databases] Database syntax Error
in my search code i am asking the visitor to tell what text they are
searching for with the varfind and which field in the database to search
in with varin.
here is my code:
im varfind, varin, cnEmps, connstr, TheMessage, rsEmps
varFind = request.form("find")
varin = request.form("in")
response.write varfind
response.write varin
set cnEmps = server.createobject ("adodb.connection")
connstr = "driver={microsoft access driver (*.mdb)};" & _
"dbq=" & server.MapPath("thermo.mdb")
cnEmps.open connstr,"", ""
Select Case UCase (Request.QueryString("view"))
Case "ALL"
TheMessage = "All Employees"
sqltext = "SELECT * FROM empdir"
Case "DEPARTMENT"
TheMessage = "For Department " & Request.QueryString
("Department")
sqltext = "SELECT * FROM empdir WHERE department = '" &
Request.Querystring("Department") &"'"
Case Else
TheMessage = "Search Results"
sqltext = "SELECT * FROM empdir WHERE %" & varin & "%'
LIKE '%" & varfind & "%'"
End Select
set rsEmps = cnEmps.Execute(sqltext) <<<< this is line 24 >>>>>>
%>
this is the result that i get.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
expression '%name%' LIKE '%tim%''.
/phone/results 2.asp, line 24
any help would be appreciated
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
Message #4 by "PUFFER, TIM" <TIM.PUFFER@T...> on Wed, 14 Nov 2001 16:07:14 -0600
|
|
here are the results of the Response.Write
SELECT * FROM empdir WHERE %name%' LIKE '%tim%'
-----Original Message-----
From: Imar Spaanjaars [mailto:Imar@S...]
Sent: Wednesday, November 14, 2001 3:35 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
You should do a Response.Write of sqltext right before you execute the
command and post the result of that to this list so we can take a look at
it. It's easier to debug a full SQL command than to browse through the code
and make some assumptions on how it will look.
I notice you are using a rather old way to connect to your database. Look
at the following address for a better way to connect:
http://www.able-consulting.com/ado_conn.htm#OLEDBProviderForMicrosoftJet
I wouldn't be surprised if that would solve your problem, as Access used
just the * as a wildcard in previous versions.
HtH
Imar
At 09:21 PM 11/14/2001 +0000, you wrote:
>in my search code i am asking the visitor to tell what text they are
>searching for with the varfind and which field in the database to search
>in with varin.
>
>here is my code:
>
>im varfind, varin, cnEmps, connstr, TheMessage, rsEmps
> varFind = request.form("find")
> varin = request.form("in")
> response.write varfind
> response.write varin
>
>set cnEmps = server.createobject ("adodb.connection")
>connstr = "driver={microsoft access driver (*.mdb)};" & _
> "dbq=" & server.MapPath("thermo.mdb")
>cnEmps.open connstr,"", ""
>Select Case UCase (Request.QueryString("view"))
> Case "ALL"
> TheMessage = "All Employees"
> sqltext = "SELECT * FROM empdir"
> Case "DEPARTMENT"
> TheMessage = "For Department " & Request.QueryString
>("Department")
> sqltext = "SELECT * FROM empdir WHERE department = '" &
>Request.Querystring("Department") &"'"
> Case Else
> TheMessage = "Search Results"
> sqltext = "SELECT * FROM empdir WHERE %" & varin & "%'
>LIKE '%" & varfind & "%'"
>End Select
>set rsEmps = cnEmps.Execute(sqltext) <<<< this is line 24 >>>>>>
>
>
>%>
>
>this is the result that i get.
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
>expression '%name%' LIKE '%tim%''.
>
>/phone/results 2.asp, line 24
>
>
>any help would be appreciated
>
>
$subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
Message #5 by "Drew, Ron" <RDrew@B...> on Wed, 14 Nov 2001 17:27:04 -0500
|
|
The %name% and quotes are no good. WHERE xxx xxx must be a column name
within the empdir table
i.e.
SELECT * FROM empdir WHERE empname LIKE '%tim%' ...is OK assuming empname
is the column name in empdir
-----Original Message-----
From: PUFFER, TIM [mailto:TIM.PUFFER@T...]
Sent: Wednesday, November 14, 2001 5:07 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
here are the results of the Response.Write
SELECT * FROM empdir WHERE %name%' LIKE '%tim%'
-----Original Message-----
From: Imar Spaanjaars [mailto:Imar@S...]
Sent: Wednesday, November 14, 2001 3:35 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
You should do a Response.Write of sqltext right before you execute the
command and post the result of that to this list so we can take a look at
it. It's easier to debug a full SQL command than to browse through the code
and make some assumptions on how it will look.
I notice you are using a rather old way to connect to your database. Look
at the following address for a better way to connect:
http://www.able-consulting.com/ado_conn.htm#OLEDBProviderForMicrosoftJet
I wouldn't be surprised if that would solve your problem, as Access used
just the * as a wildcard in previous versions.
HtH
Imar
At 09:21 PM 11/14/2001 +0000, you wrote:
>in my search code i am asking the visitor to tell what text they are
>searching for with the varfind and which field in the database to
>search in with varin.
>
>here is my code:
>
>im varfind, varin, cnEmps, connstr, TheMessage, rsEmps
> varFind = request.form("find")
> varin = request.form("in")
> response.write varfind
> response.write varin
>
>set cnEmps = server.createobject ("adodb.connection")
>connstr = "driver={microsoft access driver (*.mdb)};" & _
> "dbq=" & server.MapPath("thermo.mdb") cnEmps.open
>connstr,"", "" Select Case UCase (Request.QueryString("view"))
> Case "ALL"
> TheMessage = "All Employees"
> sqltext = "SELECT * FROM empdir"
> Case "DEPARTMENT"
> TheMessage = "For Department " & Request.QueryString
>("Department")
> sqltext = "SELECT * FROM empdir WHERE department = '" &
>Request.Querystring("Department") &"'"
> Case Else
> TheMessage = "Search Results"
> sqltext = "SELECT * FROM empdir WHERE %" & varin & "%'
>LIKE '%" & varfind & "%'"
>End Select
>set rsEmps = cnEmps.Execute(sqltext) <<<< this is line 24 >>>>>>
>
>
>%>
>
>this is the result that i get.
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
>expression '%name%' LIKE '%tim%''.
>
>/phone/results 2.asp, line 24
>
>
>any help would be appreciated
>
>
$subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
Message #6 by "PUFFER, TIM" <TIM.PUFFER@T...> on Wed, 14 Nov 2001 16:34:06 -0600
|
|
i am try to get the field that the user wants to search from (This is a drop
down list on the search page). Like the name field or the office location
field.
the way I have it written is xxx is a variable. if that is possible?
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: Wednesday, November 14, 2001 4:27 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
The %name% and quotes are no good. WHERE xxx xxx must be a column name
within the empdir table
i.e.
SELECT * FROM empdir WHERE empname LIKE '%tim%' ...is OK assuming empname
is the column name in empdir
-----Original Message-----
From: PUFFER, TIM [mailto:TIM.PUFFER@T...]
Sent: Wednesday, November 14, 2001 5:07 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
here are the results of the Response.Write
SELECT * FROM empdir WHERE %name%' LIKE '%tim%'
-----Original Message-----
From: Imar Spaanjaars [mailto:Imar@S...]
Sent: Wednesday, November 14, 2001 3:35 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
You should do a Response.Write of sqltext right before you execute the
command and post the result of that to this list so we can take a look at
it. It's easier to debug a full SQL command than to browse through the code
and make some assumptions on how it will look.
I notice you are using a rather old way to connect to your database. Look
at the following address for a better way to connect:
http://www.able-consulting.com/ado_conn.htm#OLEDBProviderForMicrosoftJet
I wouldn't be surprised if that would solve your problem, as Access used
just the * as a wildcard in previous versions.
HtH
Imar
At 09:21 PM 11/14/2001 +0000, you wrote:
>in my search code i am asking the visitor to tell what text they are
>searching for with the varfind and which field in the database to
>search in with varin.
>
>here is my code:
>
>im varfind, varin, cnEmps, connstr, TheMessage, rsEmps
> varFind = request.form("find")
> varin = request.form("in")
> response.write varfind
> response.write varin
>
>set cnEmps = server.createobject ("adodb.connection")
>connstr = "driver={microsoft access driver (*.mdb)};" & _
> "dbq=" & server.MapPath("thermo.mdb") cnEmps.open
>connstr,"", "" Select Case UCase (Request.QueryString("view"))
> Case "ALL"
> TheMessage = "All Employees"
> sqltext = "SELECT * FROM empdir"
> Case "DEPARTMENT"
> TheMessage = "For Department " & Request.QueryString
>("Department")
> sqltext = "SELECT * FROM empdir WHERE department = '" &
>Request.Querystring("Department") &"'"
> Case Else
> TheMessage = "Search Results"
> sqltext = "SELECT * FROM empdir WHERE %" & varin & "%'
>LIKE '%" & varfind & "%'"
>End Select
>set rsEmps = cnEmps.Execute(sqltext) <<<< this is line 24 >>>>>>
>
>
>%>
>
>this is the result that i get.
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
>expression '%name%' LIKE '%tim%''.
>
>/phone/results 2.asp, line 24
>
>
>any help would be appreciated
>
>
$subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
Message #7 by David Cameron <dcameron@i...> on Thu, 15 Nov 2001 09:57:18 +1100
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C16D5F.BBA7739E
Content-Type: text/plain;
charset="iso-8859-1"
Yes it is possible, just don't enclose the field name in %%.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: PUFFER, TIM [mailto:TIM.PUFFER@T...]
Sent: Thursday, 15 November 2001 8:34 AM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
i am try to get the field that the user wants to search from (This is a drop
down list on the search page). Like the name field or the office location
field.
the way I have it written is xxx is a variable. if that is possible?
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: Wednesday, November 14, 2001 4:27 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
The %name% and quotes are no good. WHERE xxx xxx must be a column name
within the empdir table
i.e.
SELECT * FROM empdir WHERE empname LIKE '%tim%' ...is OK assuming empname
is the column name in empdir
-----Original Message-----
From: PUFFER, TIM [mailto:TIM.PUFFER@T...]
Sent: Wednesday, November 14, 2001 5:07 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
here are the results of the Response.Write
SELECT * FROM empdir WHERE %name%' LIKE '%tim%'
-----Original Message-----
From: Imar Spaanjaars [mailto:Imar@S...]
Sent: Wednesday, November 14, 2001 3:35 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
You should do a Response.Write of sqltext right before you execute the
command and post the result of that to this list so we can take a look at
it. It's easier to debug a full SQL command than to browse through the code
and make some assumptions on how it will look.
I notice you are using a rather old way to connect to your database. Look
at the following address for a better way to connect:
http://www.able-consulting.com/ado_conn.htm#OLEDBProviderForMicrosoftJet
I wouldn't be surprised if that would solve your problem, as Access used
just the * as a wildcard in previous versions.
HtH
Imar
At 09:21 PM 11/14/2001 +0000, you wrote:
>in my search code i am asking the visitor to tell what text they are
>searching for with the varfind and which field in the database to
>search in with varin.
>
>here is my code:
>
>im varfind, varin, cnEmps, connstr, TheMessage, rsEmps
> varFind = request.form("find")
> varin = request.form("in")
> response.write varfind
> response.write varin
>
>set cnEmps = server.createobject ("adodb.connection")
>connstr = "driver={microsoft access driver (*.mdb)};" & _
> "dbq=" & server.MapPath("thermo.mdb") cnEmps.open
>connstr,"", "" Select Case UCase (Request.QueryString("view"))
> Case "ALL"
> TheMessage = "All Employees"
> sqltext = "SELECT * FROM empdir"
> Case "DEPARTMENT"
> TheMessage = "For Department " & Request.QueryString
>("Department")
> sqltext = "SELECT * FROM empdir WHERE department = '" &
>Request.Querystring("Department") &"'"
> Case Else
> TheMessage = "Search Results"
> sqltext = "SELECT * FROM empdir WHERE %" & varin & "%'
>LIKE '%" & varfind & "%'"
>End Select
>set rsEmps = cnEmps.Execute(sqltext) <<<< this is line 24 >>>>>>
>
>
>%>
>
>this is the result that i get.
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
>expression '%name%' LIKE '%tim%''.
>
>/phone/results 2.asp, line 24
>
>
>any help would be appreciated
>
>
$subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
$subst('Email.Unsub')
Message #8 by "Drew, Ron" <RDrew@B...> on Wed, 14 Nov 2001 18:22:06 -0500
|
|
As long as all the fields you want to search for are in the drop down and
they are the same type i.e. varchar you should not have a problem doing the
request.form for the field and executing.
Sql = "SELECT * FROM empdir WHERE " & request.form("fieldforcolumn") & "
LIKE '%" & request.form("userinputfield") & "%'"
-----Original Message-----
From: PUFFER, TIM [mailto:TIM.PUFFER@T...]
Sent: Wednesday, November 14, 2001 5:34 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
i am try to get the field that the user wants to search from (This is a drop
down list on the search page). Like the name field or the office location
field.
the way I have it written is xxx is a variable. if that is possible?
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: Wednesday, November 14, 2001 4:27 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
The %name% and quotes are no good. WHERE xxx xxx must be a column name
within the empdir table
i.e.
SELECT * FROM empdir WHERE empname LIKE '%tim%' ...is OK assuming empname
is the column name in empdir
-----Original Message-----
From: PUFFER, TIM [mailto:TIM.PUFFER@T...]
Sent: Wednesday, November 14, 2001 5:07 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
here are the results of the Response.Write
SELECT * FROM empdir WHERE %name%' LIKE '%tim%'
-----Original Message-----
From: Imar Spaanjaars [mailto:Imar@S...]
Sent: Wednesday, November 14, 2001 3:35 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
You should do a Response.Write of sqltext right before you execute the
command and post the result of that to this list so we can take a look at
it. It's easier to debug a full SQL command than to browse through the code
and make some assumptions on how it will look.
I notice you are using a rather old way to connect to your database. Look
at the following address for a better way to connect:
http://www.able-consulting.com/ado_conn.htm#OLEDBProviderForMicrosoftJet
I wouldn't be surprised if that would solve your problem, as Access used
just the * as a wildcard in previous versions.
HtH
Imar
At 09:21 PM 11/14/2001 +0000, you wrote:
>in my search code i am asking the visitor to tell what text they are
>searching for with the varfind and which field in the database to
>search in with varin.
>
>here is my code:
>
>im varfind, varin, cnEmps, connstr, TheMessage, rsEmps
> varFind = request.form("find")
> varin = request.form("in")
> response.write varfind
> response.write varin
>
>set cnEmps = server.createobject ("adodb.connection")
>connstr = "driver={microsoft access driver (*.mdb)};" & _
> "dbq=" & server.MapPath("thermo.mdb") cnEmps.open
>connstr,"", "" Select Case UCase (Request.QueryString("view"))
> Case "ALL"
> TheMessage = "All Employees"
> sqltext = "SELECT * FROM empdir"
> Case "DEPARTMENT"
> TheMessage = "For Department " & Request.QueryString
>("Department")
> sqltext = "SELECT * FROM empdir WHERE department = '" &
>Request.Querystring("Department") &"'"
> Case Else
> TheMessage = "Search Results"
> sqltext = "SELECT * FROM empdir WHERE %" & varin & "%'
>LIKE '%" & varfind & "%'"
>End Select
>set rsEmps = cnEmps.Execute(sqltext) <<<< this is line 24 >>>>>>
>
>
>%>
>
>this is the result that i get.
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
>expression '%name%' LIKE '%tim%''.
>
>/phone/results 2.asp, line 24
>
>
>any help would be appreciated
>
>
$subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
$subst('Email.Unsub')
Message #9 by "Andrew Haslett" <scooter@p...> on Thu, 15 Nov 2001 15:13:22 +1030
|
|
Yes Tim, it will work, but why are you putting the %% around the field
name?? You posted that the results of your response.write was:
SELECT * FROM empdir WHERE %name%' LIKE '%tim%'
The problem is here: %name% . You don't need to surrounding %%. As long
as 'name' is an existing field name then it will work.
Cheers,
Andrew
-----Original Message-----
From: PUFFER, TIM [mailto:TIM.PUFFER@T...]
Sent: Thursday, 15 November 2001 9:04 AM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
i am try to get the field that the user wants to search from (This is a drop
down list on the search page). Like the name field or the office location
field.
the way I have it written is xxx is a variable. if that is possible?
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: Wednesday, November 14, 2001 4:27 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
The %name% and quotes are no good. WHERE xxx xxx must be a column name
within the empdir table
i.e.
SELECT * FROM empdir WHERE empname LIKE '%tim%' ...is OK assuming empname
is the column name in empdir
-----Original Message-----
From: PUFFER, TIM [mailto:TIM.PUFFER@T...]
Sent: Wednesday, November 14, 2001 5:07 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
here are the results of the Response.Write
SELECT * FROM empdir WHERE %name%' LIKE '%tim%'
-----Original Message-----
From: Imar Spaanjaars [mailto:Imar@S...]
Sent: Wednesday, November 14, 2001 3:35 PM
To: ASP Databases
Subject: [asp_databases] Re: Database syntax Error
You should do a Response.Write of sqltext right before you execute the
command and post the result of that to this list so we can take a look at
it. It's easier to debug a full SQL command than to browse through the code
and make some assumptions on how it will look.
I notice you are using a rather old way to connect to your database. Look
at the following address for a better way to connect:
http://www.able-consulting.com/ado_conn.htm#OLEDBProviderForMicrosoftJet
I wouldn't be surprised if that would solve your problem, as Access used
just the * as a wildcard in previous versions.
HtH
Imar
At 09:21 PM 11/14/2001 +0000, you wrote:
>in my search code i am asking the visitor to tell what text they are
>searching for with the varfind and which field in the database to
>search in with varin.
>
>here is my code:
>
>im varfind, varin, cnEmps, connstr, TheMessage, rsEmps
> varFind = request.form("find")
> varin = request.form("in")
> response.write varfind
> response.write varin
>
>set cnEmps = server.createobject ("adodb.connection")
>connstr = "driver={microsoft access driver (*.mdb)};" & _
> "dbq=" & server.MapPath("thermo.mdb") cnEmps.open
>connstr,"", "" Select Case UCase (Request.QueryString("view"))
> Case "ALL"
> TheMessage = "All Employees"
> sqltext = "SELECT * FROM empdir"
> Case "DEPARTMENT"
> TheMessage = "For Department " & Request.QueryString
>("Department")
> sqltext = "SELECT * FROM empdir WHERE department = '" &
>Request.Querystring("Department") &"'"
> Case Else
> TheMessage = "Search Results"
> sqltext = "SELECT * FROM empdir WHERE %" & varin & "%'
>LIKE '%" & varfind & "%'"
>End Select
>set rsEmps = cnEmps.Execute(sqltext) <<<< this is line 24 >>>>>>
>
>
>%>
>
>this is the result that i get.
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
>expression '%name%' LIKE '%tim%''.
>
>/phone/results 2.asp, line 24
>
>
>any help would be appreciated
>
>
$subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
tim.puffer@t...
$subst('Email.Unsub')
$subst('Email.Unsub')
|
|
|
View Cart
