sorting records

[mcpeekj]

i'm doing a site for a car dealership in asp and want the inventory to be sorted by year, make, model, etc, by a button press. i know it can be done cause just about every car site out there does it. i've looked everywhere, but can't find the code for it. any help would be appreciated.

 

[snowboardr]

in your select statement you add order by table_name to the end of it.. like this


"SELECT * FROM table_name ORDER BY table_name" www.vzio.com

/

 

[mcpeekj]

how do i add that code to my buttons?

 

[snowboardr]

<%
Dim OrderBy, OrderbyIs

OrderBy = Request.QueryString(&quot;order&quot

If isNumeric(OrderBy)= True then

Select Case OrderBy

Case &quot;1&quot;
OrderbyIs = &quot; ORDER BY date&quot;

Case &quot;2&quot;
OrderbyIs = &quot; ORDER BY year&quot;

' and so on..

Case Else
OrderbyIs = &quot;&quot;

End Select


&quot;SELECT * FROM table_name&quot; & OrderbyIs

%>

and your link to change the order by to date would be

your_page.asp?order=1


Hope that helps.
Jason www.vzio.com

/

 

[mcpeekj]

that's exactly what i'm wanting to do, but i'm having a problem with this line
&quot;SELECT * FROM table_name&quot; & OrderbyIs
it says there is an expected statement. i really appreciate the help.

 

[snowboardr]

well thats where your select statement is.. but you have to fix it to work with your db.. If you want display all records then you would put

'After -> rs = my_conn.Execute(strSQL)
Do while not rs.eof

' ... display some records..

rs.movenext
Loop


Dim ConnString, strSQL

ConnString = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;database.mdb&quot

set my_conn= Server.CreateObject(&quot;ADODB.Connection&quot
set rs = Server.CreateObject(&quot;ADODB.RecordSet&quot


my_conn.Open ConnString



strSQL = &quot;SELECT * FROM table_name&quot; & OrderbyIs


rs = my_conn.Execute(strSQL)


'# db values here

Response.Write(rs(&quot;db_column_name&quot)


my_conn.close
set my_conn = nothing

%>
www.vzio.com

/

 

[mcpeekj]

could the problem be that i'm not using sql on the rest of my page, but pasted in your sql code? i've got vbscript. actually, i'll just show you what i've got.
<% Option Explicit %>
<div id=&quot;content&quot; style=&quot;position:absolute; left:155px; top:197px; width:612px; height:263px; z-index:3; visibility: visible&quot;>
<%
Sub ShowRec(qc1, qc2, qc3, qc4)
Response.Write &quot;<table width=500 border=0 cellpadding=0 cellspacing=0&quot; & vbcrlf
Response.Write &quot;<tr>&quot;
Response.Write &quot;<td width=50>&quot;
Response.Write qc1
Response.Write &quot;</td><td width=100>&quot;
Response.Write qc2
Response.Write &quot;</td><td width=150>&quot;
Response.Write qc3
Response.Write &quot;</td><td width=25>&quot;
Response.Write qc4
Response.Write &quot;</td></tr></table>&quot;
End Sub
' Putting the values of ADO constants
Const adCmdTableDirect = &H0200
Const adLockReadOnly = 1
Const adOpenStatic = 1
Const adUseClient = 3

Dim connStr
connStr = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & _
Server.MapPath(&quot;db4.mdb&quot
Dim rs
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.CursorLocation = adUseClient
rs.Open &quot;tblInventory&quot;, connStr, adOpenStatic, adLockReadOnly, adCmdTableDirect

Dim OrderBy, OrderbyIs

OrderBy = Request.QueryString(&quot;order&quot

If isNumeric(OrderBy)= True then

Select Case OrderBy

Case &quot;1&quot;
OrderbyIs = &quot; ORDER BY year&quot;

Case &quot;2&quot;
OrderbyIs = &quot; ORDER BY make&quot;

' and so on..

Case Else
OrderbyIs = &quot;&quot;

End Select

&quot;SELECT * FROM tblInventory&quot; & OrderbyIs

' Moving the record pointer to the beginning
rs.MoveFirst
'where body is c : body = 'C'
rs.Filter = &quot;body = 'C'&quot;
Response.Write &quot;Showing only Cars/Vans :&quot; & vbcrlf
While Not rs.EOF
ShowRec rs(&quot;Year&quot, rs(&quot;Make&quot, rs(&quot;Model&quot, rs(&quot;price&quot
rs.MoveNext
Wend
' Moving the record pointer to the beginning
rs.MoveFirst
' Removing the filter
rs.Filter = &quot;&quot;
rs.Close
Set rs = Nothing
%>
<font size=&quot;1&quot;><a href=&quot;try.asp?order=2&quot;>sort by make</a></font>
and it puts out this error
Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/asp1/hooray/try.asp, line 55
&quot;SELECT * FROM tblInventory&quot; & OrderbyIs

 

[snowboardr]

Your problem is your sql statement..

your select statement is in a string.. then you have to excute it below that..

strSQL = &quot;SELECT * FROM table_name&quot; & OrderbyIs

rs = my_conn.Execute(strSQL) www.vzio.com

/

 

[Tarwn]

Starting with your code and marking the changes:

 <%
Dim connStr
connStr = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & _
          Server.MapPath(&quot;db4.mdb&quot;)

Dim rs, strSQL
Dim objCommand

Dim OrderBy, OrderbyIs
Dim whereClause

OrderBy = Request.QueryString(&quot;order&quot;) 

Select Case OrderBy
	Case &quot;1&quot;
		OrderbyIs = &quot; ORDER BY year&quot;

	Case &quot;2&quot;
		OrderbyIs = &quot; ORDER BY make&quot;

	' and so on..

	Case Else
		OrderbyIs = &quot;&quot;
End Select

whereClause = &quot; WHERE body = 'C'&quot;	'this can be replaced by any criteria, see explanation at bottom
strSQl = &quot;SELECT * FROM tblInventory&quot; & whereClause & OrderbyIs

	Set objCommand = Server.CreateObject(&quot;ADODB.Command&quot;)
	objCommand.ActiveConnection = connStr
	objCommand.CommandText=strSQL
	objCommand.CommandType=adCmdText	
	Set rs = objCommand.Execute
	Set objCommand = Nothing
	
	rs.MoveFirst
    
	Response.Write &quot;Showing only Cars/Vans :&quot; & vbcrlf
    
	  While Not rs.EOF
         ShowRec rs(&quot;Year&quot;), rs(&quot;Make&quot;), rs(&quot;Model&quot;), rs(&quot;price&quot;)
         rs.MoveNext
      Wend
   ' Moving the record pointer to the beginning
	rs.MoveFirst

	Set rs = Nothing
%>
<font size=&quot;1&quot;><a href=&quot;try.asp?order=2&quot;>sort by make</a></font>

The idea with the from clause is that you can actually set up multiple search criterion and add them. Pretend for a moment that you have checkboixes on the previous page. One for cars, one for trucks, one for vans (you get the idea)
They are named (respectively, for out example): chkCar, chkTruck, chkVan
Pretend that those correspond to the values in your db.
To create the fromClause you can simply do something like this:

strSQl = "SELECT * FROM tblInventory"

Dim fromClause

fromClause = " WHERE"

If Request(&quot;chkCar&quot;) <> &quot;&quot; Then
   fromClause = fromClause & &quot; body = 'C' OR&quot;
End If

If Request(&quot;chkTruck&quot;) <> &quot;&quot; Then
   fromClause = fromClause & &quot; body = 'T' OR&quot;
End If

If Request(&quot;chkVan&quot;) <> &quot;&quot; Then
   fromClause = fromClause & &quot; body = 'V' OR&quot;
End If

'if from clause length > 6 then we have search criteria
If len(fromClause) > 6 Then
   'kill the last OR
   fromClause = left(fromClause,len(fromClause)-2)

   strSQl = strSQl & fromClause
End If

strSQl = strSQl & OrderbyIs

You'll notice I also started the sql statement above the where and order additions, since that part won't change it's nice to put it up there out of the way. You should be able to see how you can also have a drop down instead of checkboxes, in that case you could call it cboBody or somesuch and if it <> &quot;&quot; then search where body = value of Request(&quot;cboCody&quot. All you would have to do is set up your options for the drop down like:

<select name=&quot;cboBody&quot;>
 <option value=&quot;&quot;>[Select BodyStyle]</option>
 <option value=&quot;C&quot;>Car</option>
 <option value=&quot;V&quot;>Van</option>
 <option value=&quot;T&quot;>Truck</option>
</select>

-Tarwn
Sorry if i got carried away, haven't slept in a while ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation