I have an Acces database, etrcr2, with one table, etrcr2.  There is a web form

for a person to select the date of the records they want to view.  This alone

would be easy to do.  However, what I want to accomplish is when the table of

records is displayed, I want each column heading to be a link where if the user

clicks on it, the column is sorted by that particular field.  The code I am

using is below.  The error I get is: Syntax error in date in query expression

'edate = ##'.   The edate field is a date/time field in Access.



<%

u_order=request.querystring("u_order")

choice=request.form("choice")

%>



<% select case u_order

case

"id","eday","edate","timestart","timeend","equip","room","instructor","eclass","

deliveredby","pickedupby"

%>





<% case else

response.redirect "result_date_sort.asp?u_order=id"

end select

%>



<%

accessdb="etrcr2"

cn="DRIVER={Microsoft Access Driver (*.mdb)};"

cn=cn & "DBQ=" & server.mappath(accessdb)

Set rs = Server.CreateObject("ADODB.Recordset")

sql = "select id, eday, edate, timestart, timeend, equip, room, instructor,

eclass, deliveredby, pickedupby from etrcr2 WHERE edate = #" & choice & "# order

by "& u_order &" ASC;"



response.write sql



rs.Open sql, cn



%>





Any help would be most appreciated.



Facilis descensus averni est

Timothy Hopkins

thopkins@r...

At what point is the error generated exactly?



What does response.write sql produce?  Is choice blank?



Stephen

Response.Write produces select id, eday, edate, timestart, timeend, equip,

room, instructor, eclass, deliveredby, pickedupby from etrcr2 WHERE edate 

## order by id ASC;



The error occurs immediately after the SQL statement is written.



-----Original Message-----

From: stephenb@w... [mailto:stephenb@w...]

Sent: Wednesday, September 20, 2000 7:49 AM

To: ASP Databases

Subject: [asp_databases] Re: Access Query syntax error





At what point is the error generated exactly?



What does response.write sql produce?  Is choice blank?



Stephen

Well, it's pretty obvious no?



WHERE edate = ##



there's no value for edate to be equal to...

I missed the original post, but it seem you need to look at the code where

you assign a value to whatever goes between the #



eg if you do this



<%

dteQueryDate = Request.Form("field1")

...

strSQL = strSQL & "WHERE edate = #" & dteDateQuery & "#"

%>



you will have a problem since dteDateQuery doesn't hold anything. Maybe

there's nothing being passed from the previous page? Maybe you made a typo

in the Request.Form("") reference?



Cheers

Ken

----- Original Message -----

From: "Timothy Hopkins" <hopkin11@m...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, September 21, 2000 4:36 AM

Subject: [asp_databases] Re: Access Query syntax error





> Response.Write produces select id, eday, edate, timestart, timeend, equip,

> room, instructor, eclass, deliveredby, pickedupby from etrcr2 WHERE edate



> ## order by id ASC;

>

> The error occurs immediately after the SQL statement is written.

>

> -----Original Message-----

> From: stephenb@w... [mailto:stephenb@w...]

> Sent: Wednesday, September 20, 2000 7:49 AM

> To: ASP Databases

> Subject: [asp_databases] Re: Access Query syntax error

>

>

> At what point is the error generated exactly?

>

> What does response.write sql produce?  Is choice blank?

>

> Stephen

No, that's not it.  Here are all of the details:



On this page:

 http://faculty.roosevelt.edu/hopkins/roosevelt/etrc/result_date.asp you can

select a date (try September 20) and when you hit submit the records for that

date are returned. Here is the code for the results page:



 <%

 Dim listchoice, strSQL, cn

 listchoice = Request.Form("choice")

 accessdb="etrcr"

 cn="DRIVER={Microsoft Access Driver (*.mdb)};"

 cn=cn & "DBQ=" & server.mappath(accessdb)

 Set rs = Server.CreateObject("ADODB.Recordset")



 strSQL = "SELECT id, eday, edate, timestart, timeend, equip, room,

 instructor, eclass, deliveredby, pickedupby FROM etrcr WHERE edate=#" &

listchoice & "# order by timestart"

 rs.Open strSQL, cn

 %



 Everything is working fine so far.

 _____________________________________________________________



 Next, I wanted to add the functionality of sorting, so that the columns would

be hyperlinks that, when clicked, the records would be sorted. An example that

is working is here:

 http://faculty.roosevelt.edu/hopkins/roosevelt/recruit/result_date_all_old.asp



 The code that got this working:

 <%

 u_order=request.querystring("u_order")

 %



 <% select case u_order

 case

 "ID","campus","eday","emonth","edate","eyear","timestart","locatio

 n","type",

 "counselor" %



 <% case else

 response.redirect "result_date_all_old.asp?u_order=eyear"

 end select

 %



 <%

 accessdb="recruit"

 cn="DRIVER={Microsoft Access Driver (*.mdb)};"

 cn=cn & "DBQ=" & server.mappath(accessdb)

 Set rs = Server.CreateObject("ADODB.Recordset")



 sql = "select * from recruit order by "& u_order &" ASC;"



 response.write sql



 rs.Open sql, cn



 %

 ____________________________________



 Now, if I combine the code (and change database and table references, field

names, etc.) I get errors. To see one type of error, go to this page and select

a date (Sep 20) and click submit:



http://faculty.roosevelt.edu/hopkins/roosevelt/etrc/date-select-sort.htm



 Here's the code:



 <%

 u_order=request.querystring("u_order")

 choice=request.form("choice")

 %



 <% select case u_order

 case

 "id","eday","edate","timestart","timeend","equip","room","instruct

 or","eclas

 s","deliveredby","pickedupby"

 %





 <% case else

 response.redirect "result_date_sort.asp?u_order=id"

 end select

 %



 <%

 accessdb="etrcr2"

 cn="DRIVER={Microsoft Access Driver (*.mdb)};"

 cn=cn & "DBQ=" & server.mappath(accessdb)

 Set rs = Server.CreateObject("ADODB.Recordset")

 sql = "select id, eday, edate, timestart, timeend, equip, room, instructor,

eclass, deliveredby, pickedupby from etrcr2 WHERE edate = #" & choice & "# order

by "& u_order &" ASC;"



 response.write sql



 rs.Open sql, cn



 %



 ______________________________________



-----Original Message-----

From: Ken Schaefer [mailto:ken@a...]

Sent: Wednesday, September 20, 2000 11:24 PM

To: ASP Databases

Subject: [asp_databases] Re: Access Query syntax error





Well, it's pretty obvious no?



WHERE edate = ##



there's no value for edate to be equal to...

I missed the original post, but it seem you need to look at the code where

you assign a value to whatever goes between the #



eg if you do this



<%

dteQueryDate = Request.Form("field1")

...

strSQL = strSQL & "WHERE edate = #" & dteDateQuery & "#"

%>



you will have a problem since dteDateQuery doesn't hold anything. Maybe

there's nothing being passed from the previous page? Maybe you made a typo

in the Request.Form("") reference?



Cheers

Ken

----- Original Message -----

From: "Timothy Hopkins" <hopkin11@m...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, September 21, 2000 4:36 AM

Subject: [asp_databases] Re: Access Query syntax error





> Response.Write produces select id, eday, edate, timestart, timeend, equip,

> room, instructor, eclass, deliveredby, pickedupby from etrcr2 WHERE edate



> ## order by id ASC;

>

> The error occurs immediately after the SQL statement is written.

>

> -----Original Message-----

> From: stephenb@w... [mailto:stephenb@w...]

> Sent: Wednesday, September 20, 2000 7:49 AM

> To: ASP Databases

> Subject: [asp_databases] Re: Access Query syntax error

>

>

> At what point is the error generated exactly?

>

> What does response.write sql produce?  Is choice blank?

>

> Stephen

Hi Tim



When I go to your page I get this error upon submitting:



Microsoft VBScript compilation error '800a03fe'

Expected 'Select'

/hopkins/roosevelt/etrc/result_date_sort.asp, line 12

end selectset

----^



looks like you have a typo.



Cheers

Ken



----- Original Message -----

From: "Timothy Hopkins" <hopkin11@m...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, September 22, 2000 1:23 AM

Subject: [asp_databases] Re: Access Query syntax error





> No, that's not it.  Here are all of the details:

>

> On this page:

>  http://faculty.roosevelt.edu/hopkins/roosevelt/etrc/result_date.asp you

can

> select a date (try September 20) and when you hit submit the records for

that

> date are returned. Here is the code for the results page:

>

>  <%

>  Dim listchoice, strSQL, cn

>  listchoice = Request.Form("choice")

>  accessdb="etrcr"

>  cn="DRIVER={Microsoft Access Driver (*.mdb)};"

>  cn=cn & "DBQ=" & server.mappath(accessdb)

>  Set rs = Server.CreateObject("ADODB.Recordset")

>

>  strSQL = "SELECT id, eday, edate, timestart, timeend, equip, room,

>  instructor, eclass, deliveredby, pickedupby FROM etrcr WHERE edate=#" &

> listchoice & "# order by timestart"

>  rs.Open strSQL, cn

>  %

>

>  Everything is working fine so far.

>  _____________________________________________________________

>

>  Next, I wanted to add the functionality of sorting, so that the columns

would

> be hyperlinks that, when clicked, the records would be sorted. An example

that

> is working is here:

>

http://faculty.roosevelt.edu/hopkins/roosevelt/recruit/result_date_all_old.a

sp

>

>  The code that got this working:

>  <%

>  u_order=request.querystring("u_order")

>  %

>

>  <% select case u_order

>  case

>  "ID","campus","eday","emonth","edate","eyear","timestart","locatio

>  n","type",

>  "counselor" %

>

>  <% case else

>  response.redirect "result_date_all_old.asp?u_order=eyear"

>  end select

>  %

>

>  <%

>  accessdb="recruit"

>  cn="DRIVER={Microsoft Access Driver (*.mdb)};"

>  cn=cn & "DBQ=" & server.mappath(accessdb)

>  Set rs = Server.CreateObject("ADODB.Recordset")

>

>  sql = "select * from recruit order by "& u_order &" ASC;"

>

>  response.write sql

>

>  rs.Open sql, cn

>

>  %

>  ____________________________________

>

>  Now, if I combine the code (and change database and table references,

field

> names, etc.) I get errors. To see one type of error, go to this page and

select

> a date (Sep 20) and click submit:

>

> http://faculty.roosevelt.edu/hopkins/roosevelt/etrc/date-select-sort.htm

>

>  Here's the code:

>

>  <%

>  u_order=request.querystring("u_order")

>  choice=request.form("choice")

>  %

>

>  <% select case u_order

>  case

>  "id","eday","edate","timestart","timeend","equip","room","instruct

>  or","eclas

>  s","deliveredby","pickedupby"

>  %

>

>

>  <% case else

>  response.redirect "result_date_sort.asp?u_order=id"

>  end select

>  %

>

>  <%

>  accessdb="etrcr2"

>  cn="DRIVER={Microsoft Access Driver (*.mdb)};"

>  cn=cn & "DBQ=" & server.mappath(accessdb)

>  Set rs = Server.CreateObject("ADODB.Recordset")

>  sql = "select id, eday, edate, timestart, timeend, equip, room,

instructor,

> eclass, deliveredby, pickedupby from etrcr2 WHERE edate = #" & choice & "#

order

> by "& u_order &" ASC;"

>

>  response.write sql

>

>  rs.Open sql, cn

>

>  %

>

>  ______________________________________

>

> -----Original Message-----

> From: Ken Schaefer [mailto:ken@a...]

> Sent: Wednesday, September 20, 2000 11:24 PM

> To: ASP Databases

> Subject: [asp_databases] Re: Access Query syntax error

>

>

> Well, it's pretty obvious no?

>

> WHERE edate = ##

>

> there's no value for edate to be equal to...

> I missed the original post, but it seem you need to look at the code where

> you assign a value to whatever goes between the #

>

> eg if you do this

>

> <%

> dteQueryDate = Request.Form("field1")

> ...

> strSQL = strSQL & "WHERE edate = #" & dteDateQuery & "#"

> %>

>

> you will have a problem since dteDateQuery doesn't hold anything. Maybe

> there's nothing being passed from the previous page? Maybe you made a typo

> in the Request.Form("") reference?

>

> Cheers

> Ken