Help with sorting Access table using ASP

Hi all
I have been trying to sort an Access table "calendar" by the field
"Date" into ascending order.
I've tried a straight SQL query, but I don't believe my server
supports SQL although they do support ASP.
I've tried using a stored query in Access "Gigdatesort" with no luck.

Is anyone out there who is able to help?
It would be appreciated.

TIA.

Jon
Tue Mar 08 10:56:49 CST 2005

Hi John,

You'd just need some sql such as
select * from table order by datefield

You'll need to ensure your datefield really is a date field and not text or
else you won't get the correct sort.

--
Cheers,
Jon
Microsoft MVP

"John" <im@home.net> wrote in message
news:3mkr21lmvtdfn4e7oee9pag8nv5271b5rr@4ax.com...
> Hi all
> I have been trying to sort an Access table "calendar" by the field
> "Date" into ascending order.
> I've tried a straight SQL query, but I don't believe my server
> supports SQL although they do support ASP.
> I've tried using a stored query in Access "Gigdatesort" with no luck.
>
> Is anyone out there who is able to help?
> It would be appreciated.
>
> TIA.

John
Tue Mar 08 11:09:23 CST 2005

Thanks Jon, but as I said, my server doesn't support SQL.
When I try something like your code using "Select", its treated as
VBScript code so it looks for the keyword "Case" after select.
Also, yes, my date field is in a date format and not text or number
format.

On Tue, 8 Mar 2005 16:56:49 -0000, "Jon Spivey" <jons@mvps.org> wrote:

>Hi John,
>
>You'd just need some sql such as
>select * from table order by datefield
>
>You'll need to ensure your datefield really is a date field and not text or
>else you won't get the correct sort.

Jon
Tue Mar 08 11:21:02 CST 2005

Hi John,

If your server supports databases it will support sql. The confusion might
have arisen because select is a vbscript keyword as well as an sql one -
they do totally different things. Post up the code you have so far and we'll
soon get this working.

--
Cheers,
Jon
Microsoft MVP

"John" <im@home.net> wrote in message
news:qsmr2158kuribpllapf3gbo2ik0tjdbdf9@4ax.com...
> Thanks Jon, but as I said, my server doesn't support SQL.
> When I try something like your code using "Select", its treated as
> VBScript code so it looks for the keyword "Case" after select.
> Also, yes, my date field is in a date format and not text or number
> format.
>
> On Tue, 8 Mar 2005 16:56:49 -0000, "Jon Spivey" <jons@mvps.org> wrote:
>
>>Hi John,
>>
>>You'd just need some sql such as
>>select * from table order by datefield
>>
>>You'll need to ensure your datefield really is a date field and not text
>>or
>>else you won't get the correct sort.
>

Ronx
Tue Mar 08 11:35:53 CST 2005

If your server does not support SQL you cannot use any databases. SQL
is the "language" used to manipulate databases, no matter what server
or scripting language is being used.
It is more likely that either the server does not support any
database, or the syntax you are using is wrong.

A typical(?) database query should look (something) like this:


myConnString = Application("Database1_ConnectionString")
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open myConnString

mySQL="SELECT * FROM WebFaqs WHERE (WebFaqs.File = """ & sFilenm &
""")"
set rsTemp1 = myConnection.execute(mySQL)


Notice that the SQL statement is contained in a string, and cannot be
interpreted as an incomplete VBscript statement.

--
Ron Symonds
Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

"John" <im@home.net> wrote in message
news:3mkr21lmvtdfn4e7oee9pag8nv5271b5rr@4ax.com...
> Hi all
> I have been trying to sort an Access table "calendar" by the field
> "Date" into ascending order.
> I've tried a straight SQL query, but I don't believe my server
> supports SQL although they do support ASP.
> I've tried using a stored query in Access "Gigdatesort" with no
> luck.
>
> Is anyone out there who is able to help?
> It would be appreciated.
>
> TIA.

John
Tue Mar 08 14:02:15 CST 2005

Hi Jon,
Thanks again.
Here is the code I use to connect and open the Access table.....

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject ("ADODB.Recordset")
filepath=Server.MapPath("redlion.mdb")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
FilePath & ";"
objRS.Open "calendar", objConn, adOpenStatic, adLockReadOnly,
adCmdTable

Here is the code to display each field and then close the table and
connection...

While Not objRS.EOF
%>
<tr>
<td class="eventdate"><% =objRS("Date") %></td>
<td class="eventtime"><% =objRS("Time") %></td>
<td class="eventprice"><% =objRS("Price") %></td>
<td class="eventband"><% =objRS("Band") %></td>
<td class="eventbandlink"><% =objRS("Weblink") %></td>
</tr>
<tr>
<td colspan="5" class="eventbandsummary"><%
=objRS("Summary") %></td>
</tr>
<tr></tr>
<% objRS.Movenext
Wend
objRS.Close
objConn.Close
Set objRS=Nothing
Set objConn=Nothing
%>

Here is the SQL query that Access came up with to sort the "Date"
field in ascending order...

SELECT calendar.Date FROM calendar ORDER BY calendar.Date;

The trouble is, as I said before, the error code reported is looking
for the VBScript CASE keyword.

I'm not even sure where I should be placing the query?



On Tue, 8 Mar 2005 17:21:02 -0000, "Jon Spivey" <jons@mvps.org> wrote:

>Hi John,
>
>If your server supports databases it will support sql. The confusion might
>have arisen because select is a vbscript keyword as well as an sql one -
>they do totally different things. Post up the code you have so far and we'll
>soon get this working.

Stefan
Wed Mar 09 03:31:28 CST 2005

FYI
Date and Time are reserved VBscript words and should never be used for DB Field names
Rename your DB fields in your DB and scripts

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________


"John" <im@home.net> wrote in message news:561s215haq860vifdq76cd356anendifld@4ax.com...
| Hi Jon,
| Thanks again.
| Here is the code I use to connect and open the Access table.....
|
| Set objConn = Server.CreateObject("ADODB.Connection")
| Set objRS = Server.CreateObject ("ADODB.Recordset")
| filepath=Server.MapPath("redlion.mdb")
| objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
| FilePath & ";"
| objRS.Open "calendar", objConn, adOpenStatic, adLockReadOnly,
| adCmdTable
|
| Here is the code to display each field and then close the table and
| connection...
|
| While Not objRS.EOF
| %>
| <tr>
| <td class="eventdate"><% =objRS("Date") %></td>
| <td class="eventtime"><% =objRS("Time") %></td>
| <td class="eventprice"><% =objRS("Price") %></td>
| <td class="eventband"><% =objRS("Band") %></td>
| <td class="eventbandlink"><% =objRS("Weblink") %></td>
| </tr>
| <tr>
| <td colspan="5" class="eventbandsummary"><%
| =objRS("Summary") %></td>
| </tr>
| <tr></tr>
| <% objRS.Movenext
| Wend
| objRS.Close
| objConn.Close
| Set objRS=Nothing
| Set objConn=Nothing
| %>
|
| Here is the SQL query that Access came up with to sort the "Date"
| field in ascending order...
|
| SELECT calendar.Date FROM calendar ORDER BY calendar.Date;
|
| The trouble is, as I said before, the error code reported is looking
| for the VBScript CASE keyword.
|
| I'm not even sure where I should be placing the query?
|
|
|
| On Tue, 8 Mar 2005 17:21:02 -0000, "Jon Spivey" <jons@mvps.org> wrote:
|
| >Hi John,
| >
| >If your server supports databases it will support sql. The confusion might
| >have arisen because select is a vbscript keyword as well as an sql one -
| >they do totally different things. Post up the code you have so far and we'll
| >soon get this working.
|

Jon
Wed Mar 09 16:13:13 CST 2005

Hi John,

As Stefan pointed we need to address using date and time for field names -
you can either stick square brackets [ ] around them or change the field
name. Also it looks to me like the code you're using to open your recordset
is screwed. Try like this

<%
set oRs = server.createobject("adodb.recordset")
oRs.open "SELECT * FROM calendar ORDER BY [Date]",
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("redlion.mdb")
while not oRs.eof
%>
....rest as you have it

You don't need an explicit connection object to retrieve data. It's bad
practice to use * you should list out the fields you want - I just did it
this way cos it's quicker to type.

--
Cheers,
Jon
Microsoft MVP

"John" <im@home.net> wrote in message
news:561s215haq860vifdq76cd356anendifld@4ax.com...
> Hi Jon,
> Thanks again.
> Here is the code I use to connect and open the Access table.....
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objRS = Server.CreateObject ("ADODB.Recordset")
> filepath=Server.MapPath("redlion.mdb")
> objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> FilePath & ";"
> objRS.Open "calendar", objConn, adOpenStatic, adLockReadOnly,
> adCmdTable
>
> Here is the code to display each field and then close the table and
> connection...
>
> While Not objRS.EOF
> %>
> <tr>
> <td class="eventdate"><% =objRS("Date") %></td>
> <td class="eventtime"><% =objRS("Time") %></td>
> <td class="eventprice"><% =objRS("Price") %></td>
> <td class="eventband"><% =objRS("Band") %></td>
> <td class="eventbandlink"><% =objRS("Weblink") %></td>
> </tr>
> <tr>
> <td colspan="5" class="eventbandsummary"><%
> =objRS("Summary") %></td>
> </tr>
> <tr></tr>
> <% objRS.Movenext
> Wend
> objRS.Close
> objConn.Close
> Set objRS=Nothing
> Set objConn=Nothing
> %>
>
> Here is the SQL query that Access came up with to sort the "Date"
> field in ascending order...
>
> SELECT calendar.Date FROM calendar ORDER BY calendar.Date;
>
> The trouble is, as I said before, the error code reported is looking
> for the VBScript CASE keyword.
>
> I'm not even sure where I should be placing the query?
>
>
>
> On Tue, 8 Mar 2005 17:21:02 -0000, "Jon Spivey" <jons@mvps.org> wrote:
>
>>Hi John,
>>
>>If your server supports databases it will support sql. The confusion might
>>have arisen because select is a vbscript keyword as well as an sql one -
>>they do totally different things. Post up the code you have so far and
>>we'll
>>soon get this working.
>

John
Wed Mar 09 17:24:47 CST 2005

Thanks a bunch Jon, that worked great!
Now I can relax a little.
BTW, if I need to sort in descending order, do I follow Date with Desc

John.

On Wed, 9 Mar 2005 22:13:13 -0000, "Jon Spivey" <jons@mvps.org> wrote:

>Hi John,
>
>As Stefan pointed we need to address using date and time for field names -
>you can either stick square brackets [ ] around them or change the field
>name. Also it looks to me like the code you're using to open your recordset
>is screwed. Try like this
>
><%
>set oRs = server.createobject("adodb.recordset")
>oRs.open "SELECT * FROM calendar ORDER BY [Date]",
>"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
>Server.MapPath("redlion.mdb")
>while not oRs.eof
>%>
>....rest as you have it
>
>You don't need an explicit connection object to retrieve data. It's bad
>practice to use * you should list out the fields you want - I just did it
>this way cos it's quicker to type.

Jon
Wed Mar 09 17:55:17 CST 2005

Yes

--
Cheers,
Jon
Microsoft MVP

"John" <im@home.net> wrote in message
news:k51v21t6rbb9qovsfc7bjs67gffeqism32@4ax.com...
> Thanks a bunch Jon, that worked great!
> Now I can relax a little.
> BTW, if I need to sort in descending order, do I follow Date with Desc
>
> John.
>
> On Wed, 9 Mar 2005 22:13:13 -0000, "Jon Spivey" <jons@mvps.org> wrote:
>
>>Hi John,
>>
>>As Stefan pointed we need to address using date and time for field names -
>>you can either stick square brackets [ ] around them or change the field
>>name. Also it looks to me like the code you're using to open your
>>recordset
>>is screwed. Try like this
>>
>><%
>>set oRs = server.createobject("adodb.recordset")
>>oRs.open "SELECT * FROM calendar ORDER BY [Date]",
>>"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
>>Server.MapPath("redlion.mdb")
>>while not oRs.eof
>>%>
>>....rest as you have it
>>
>>You don't need an explicit connection object to retrieve data. It's bad
>>practice to use * you should list out the fields you want - I just did it
>>this way cos it's quicker to type.
>