Recordset Sort problems

[atmospherik]

I am trying to sort a recordset and i am having problems.

I have tried the follwing but output is not sorted...

Dim rsManufacturer
Set rsManufacturer = Server.CreateObject("ADODB.Recordset&quot
rsManufacturer.Open "SELECT * FROM tblManufacturers", strConn
rsManufacturer.CursorLocation = 3

<% rsManufacturer.Sort &quot;Manufacturer&quot; %>
<%While (NOT rsManufacturer.EOF)%>
'output record here
<%rsManufacturer.MoveNext()

Have also tried SQL &quot;..ORDER BY Manufacturer&quot; (manufacturer is name of field I want to sort by) but this doesn't work either)

Would appreciate any help here.
Thanks in advance,
Paul.

 

[foxbox]

???

&quot;SELECT * FROM tblManufacturers ORDER BY Manufacturer&quot;

should work (and i prefer to do sorting with the database).
What is the fieldtype of &quot;Manufacturer&quot;? If it is text and there are numbers in it, it can go wrong:
'1 '
'10 '
'2 '





hth,
Foxbox

 

[atmospherik]

Hi FoxBox

will try SQL again but didn't seem to be able to get that to work earlier. The table is actually only a list of manufacturers (text) with an ID (autonumber) so that I can use drop down boxes in Access.

Paul

 

[atmospherik]

Just retried the SQL and records are still in the order of the database.

I have just realised that after making the query I do a recordset filter then remove it (I need to do this to lookup a manufacturer name from the id number). Then I output the records - could this be causing the problem?

 

[foxbox]

?? to lookup a name? Normally you join tables:


<%
cSQL = &quot;SELECT * FROM tblManufacturers &quot; &_
&quot;ORDER BY Manufacturer&quot;
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open cSQL, strConn

do While (NOT rs.EOF)
response.write rs(0)
' etc
rs.MoveNext()
loop
rs.close
%>


Your lookup remark is puzzling. The manufacturer name is not in this table? If that is the case you should include a join in the SQL - statement






hth,
Foxbox

 

[atmospherik]

Sorry, maybe I'm not explaining too well...

The manufacturer table has two fields - id number and manufacturer. This is used within another MS Access table to give a drop down list of manufacturers.

Unfortunately, Access requires the id number to be autonumber and therefore must be ordered by which order the manufacturers are added to the table.

I use the manufacturer recordset twice on my page - once for a navbar (list of manufacturers) and once to select which products to display.

the navbar is the list i wish to have alphabetically sorted irrespective of which order they are added to the db.

in order to build a SQL query for the required products I use the manufacturer name, convert this to the id number using the rsManufacturer filter etc. then this number can be used to select which products are by this manufacturer (since the manufacturer field in the main product table is a number - for the Access drop down list)

Is this any clearer ????!!!

 

[foxbox]

in total that is something like this (q&d)


[test.asp]

<%
' build list of manufacturers
cSQL = &quot;SELECT id, manufacturer FROM &quot; &_
&quot;tblManufacturers &quot; &_
&quot;ORDER BY Manufacturer&quot;
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open cSQL, strConn

do While (NOT rs.EOF)
' Each name becomes a link to this script
response.write &quot;<a href=test.asp?id=&quot; & rs(0) &_
&quot;>&quot; & rs(1) & &quot;</a>&nbsp;&quot;
rs.MoveNext()
loop
rs.close

dim nID
nID = request.querystring(&quot;id&quot
if nID <> &quot;&quot; then
' show products of actual manufacturer
cSQL = &quot;SELECT * FROM tblProducts &quot; &_
&quot;where id = &quot; & nID
rs.Open cSQL, strConn
do While (NOT rs.EOF)
response.write rs(0)
' etc
rs.MoveNext()
loop
rs.close
end if


%>


hth,
Foxbox

 

[atmospherik]

Thanks foxbox, that works

Don't know what I was doing wrong!

Paul.