sorting columns... I know, I know... (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

aipnit -> sorting columns... I know, I know... (1/16/2007 14:30:59)

Hi all,

I know that there are quite a few posts on this subject. I truly have read, I swear, every dang one of them. I have tried several, several different times all of the ones that I have found. I even went through the tutorial that uses the Northwind database... to no avail. It still comes up with DRW error.

Here is my code from my website... any help would be greatly appreciated! And a huge thanks in advance!
AP

<!--webbot bot="DatabaseRegionStart" s-columnnames="CourseTrainingName,StartDate,ClassDayEve,Location1,CARESApplicable" s-columntypes="202,135,202,202,202" s-dataconnection="CARES_DB" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="CourseTrainingName" s-menuvalue="CourseTrainingName" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns="CourseTrainingName,StartDate,ClassDayEve,Location1,CARESApplicable" s-criteria s-order s-sql="SELECT CourseTrainingName, StartDate, ClassDayEve, Location1, CARESApplicable FROM viewclasses ORDER BY ::SortColumn:: ASC" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="SortColumn=CourseTrainingName" s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the start of a Database Results region.</font></td></tr>" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="TRUE" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT CourseTrainingName, StartDate, ClassDayEve, Location1, CARESApplicable FROM viewclasses ORDER BY ::SortColumn:: ASC"
fp_sDefault="SortColumn=CourseTrainingName"
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="CARES_DB"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="CourseTrainingName"
fp_sMenuValue="CourseTrainingName"
fp_sColTypes="&CourseTrainingName=202&StartDate=135&ClassDayEve=202&Location1=202&CARESApplicable=202&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



rdouglass -> RE: sorting columns... I know, I know... (1/16/2007 15:03:58)

1. What is the exact error?

2. What happens if you put this *just before* the DRW:

<%=Request("SortColumn")%>

Does a value print out that you're expecting?

3. If it were me, I'd put the DRW on a diet and do this:

Before the DRW put this:

<%DIM mySortColumn
IF trim(Request("SortColumn")&"") = "" THEN
mySortColumn = "CourseTrainingName"
ELSE
mySortColumn = trim(Request("SortColumn")&"")
END IF%>

and then your DRW query line should look like this:

fp_sQry="SELECT CourseTrainingName, StartDate, ClassDayEve, Location1, CARESApplicable FROM viewclasses ORDER BY " & mySortColumn & " ASC"

Anything help there?



aipnit -> RE: sorting columns... I know, I know... (1/16/2007 16:11:04)

rdouglass,

Thank you for your reply. I do apologize for my inexperience, but will try and answer your questions.

1) The error is when I Preview in IE - it comes up with DRW error in yellow

2) I put <%=Request("SortColumn")%> where you suggested, but I am not sure what I am looking for when I do that

3) I inserted your suggestion and this is what ends up - when it shows up in the fp_sQry, it cuts off after ORDER BY


<%=Request("SortColumn")%>
<%DIM mySortColumn
IF trim(Request("SortColumn")&"") = "" THEN mySortColumn = "CourseTrainingName"
ELSE
mySortColumn = trim(Request("SortColumn")&"")
END IF%>

<!--webbot bot= {omitted beginning} s-sql="SELECT CourseTrainingName, StartDate, ClassDayEve, Location1, CARESApplicable FROM CoursesTrainings ORDER BY " & mySortColumn & " ASC"

<%
fp_sQry="SELECT CourseTrainingName, StartDate, ClassDayEve, Location1, CARESApplicable FROM CoursesTrainings ORDER BY "



swoosh -> RE: sorting columns... I know, I know... (1/16/2007 16:32:02)

Look at this thread and read what Duane "BeTheBall" posted. It works like a charm. Hope it helps.

Sorting Thread

Notice the code is also dieted



aipnit -> RE: sorting columns... I know, I know... (1/17/2007 13:12:35)

hmm - okay. I will take a look at it this morning. Thank you for the information.



aipnit -> RE: sorting columns... I know, I know... (1/17/2007 13:22:44)

Alright, I went to the page that you suggested, Swoosh, thank you. I believe that I have cleaned up using Spooky's Diet as well - thanks to both of you for that suggestion.

However, I am not sure what to put after the WHERE in the sQry - here is my code thus far (with ??? in the WHERE area)

Thanks again and hope this makes sense.
AP

<%
fp_sQry="SELECT * FROM viewclasses WHERE (CourseTrainingName = '???????') ORDER BY ::SortColumn:: ASC"
fp_sDefault="SortColumn=CourseTrainingName"
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="CARES_DB"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&CourseTrainingName=202&StartDate=135&ClassDayEve=202&Location1=202&CARESApplicable=202&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<tr>
<td>
<%=FP_FieldVal(fp_rs,"CourseTrainingName")%></td>
<td>
<%=FP_FieldVal(fp_rs,"StartDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ClassDayEve")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Location1")%></td>
<td>
<%=FP_FieldVal(fp_rs,"CARESApplicable")%></td>
</tr>



BeTheBall -> RE: sorting columns... I know, I know... (1/17/2007 15:42:34)

The WHERE clause in optional. That is there as a filter because in the example I posted, we were not retrieving all records in the table. Try:

SELECT * FROM viewclasses ORDER BY ::SortColumn:: ASC



aipnit -> RE: sorting columns... I know, I know... (1/23/2007 14:46:10)

Okay, so here is my code - however, it still doesn't work. It still comes up with DRW Error in IE.

I have included the columns (I only want them to be able to sort by a couple of them.

Thanks in advance for your continued assistance.
AP

<th ALIGN="LEFT"><b>
<a href="default.asp?SortColumn=CourseTrainingName">
<font color="#0000FF">CourseTrainingName</font></a></b></th>
<th ALIGN="LEFT"><b>StartDate</b></th>
<th ALIGN="LEFT"><b>
<a href="default.asp?SortColumn=ClassDayEve">
<font color="#0000FF">ClassDayEve</font></a></b></th>
<th ALIGN="LEFT"><b>Location1</b></th>
<th ALIGN="LEFT"><b>CARESApplicable</b></th>
</tr>
</thead>
<tbody>

<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>

<%
fp_sQry="SELECT * FROM viewclasses ORDER BY ::SortColumn:: ASC"
fp_sDefault="SortColumn=CourseTrainingName"
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="CARES_DB"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&CourseTrainingName=202&StartDate=135&ClassDayEve=202&Location1=202&CARESApplicable=202&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<%=FP_FieldVal(fp_rs,"CourseTrainingName")%></td>
<td>
<%=FP_FieldVal(fp_rs,"StartDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ClassDayEve")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Location1")%></td>
<td>
<%=FP_FieldVal(fp_rs,"CARESApplicable")%></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->



BeTheBall -> RE: sorting columns... I know, I know... (1/23/2007 18:00:57)

See if this makes any difference:

fp_sQry="SELECT * FROM viewclasses ORDER BY &" Request.QueryString("SortColumn") &" ASC"

If not, please post the text of the error message.



rdouglass -> RE: sorting columns... I know, I know... (1/23/2007 20:31:37)

quote:

ORDER BY &" Request


(I think there's a typo there...)

...ORDER BY " & Request...



BeTheBall -> RE: sorting columns... I know, I know... (1/23/2007 20:49:04)

Oops. Right you are. Thanks for spotting it.



aipnit -> RE: sorting columns... I know, I know... (1/24/2007 12:35:49)

quote:

fp_sQry="SELECT * FROM viewclasses ORDER BY "& Request.QueryString("SortColumn") &" ASC"
If not, please post the text of the error message.


BeTheBall,

I replaced with this fp_sQry, but it didn't work. When you say to post the text of ther error message, I am assuming you mean that when I choose to 'Preview in IE' what that error message says - which is,

"Database Results Wizard Error - The operation failed. If this continues, please contact your server administrator."

If there is another place to pull an error, let me know and I'll get you that text as well.

As always... thanks!
AP



BeTheBall -> RE: sorting columns... I know, I know... (1/24/2007 15:29:18)

Try this small edit:

fp_sQry="SELECT * FROM viewclasses ORDER BY "& Request("SortColumn") &" ASC"



aipnit -> RE: sorting columns... I know, I know... (1/24/2007 16:03:48)

quote:

fp_sQry="SELECT * FROM viewclasses ORDER BY "& Request("SortColumn") &" ASC"


Sheesh, I am not sure what I am doing or not doing to make this not work. I get the same error with this - hmmmm. Just not sure what to do. hmph. [8|]



BeTheBall -> RE: sorting columns... I know, I know... (1/24/2007 16:09:16)

Do what Spooky suggests in the first post of this thread:

http://www.frontpagewebmaster.com/m-175524/tm.htm

Then, test the page again. You will still get an error, but the message should be more descriptive.



aipnit -> RE: sorting columns... I know, I know... (1/24/2007 16:18:07)

ahhh, okay... the error is:

Database Results Wizard Error
Description: Syntax error in ORDER BY clause.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine



BeTheBall -> RE: sorting columns... I know, I know... (1/24/2007 19:19:34)

I think you need text delimiters around the variable. Does this work?

fp_sQry="SELECT * FROM viewclasses ORDER BY '"& Request("SortColumn") &"' ASC"



aipnit -> RE: sorting columns... I know, I know... (1/25/2007 14:34:03)

quote:

fp_sQry="SELECT * FROM viewclasses ORDER BY '"& Request("SortColumn") &"' ASC"


Thanks - that finally got rid of the error and pulls the classes from the database!!

However, now when I click on the column headings, it doesn't sort them. I assume it may have something to do with the Request("SortColumn") in the fp_sQry, but I don't know how to make the changes necessary.

Here is how I have them set up:

<th ALIGN="LEFT"><b>
<a href="default.asp?SortColumn=CourseTrainingName">
<font color="#0000FF">Name</font></a></b></th>
<th ALIGN="LEFT"><b>
<a href="default.asp?SortColumn=StartDate">
<font color="#0000FF">StartDate</font></a></b></th>
<th ALIGN="LEFT"><b>
<a href="default.asp?SortColumn=ClassDayEve">
<font color="#0000FF">Day or Eve</font></a></b></th>

Any ideas??
AP



BeTheBall -> RE: sorting columns... I know, I know... (1/25/2007 16:33:17)

Don't suppose you have a link we can see? I do see where you are going to have a little trouble because not all the columns are text. That means we will need to build the delimiters dynamically to match the datatype of the field upon which you are sorting.



aipnit -> RE: sorting columns... I know, I know... (1/25/2007 16:42:18)

Oh sure, the link for this particular issue is:

http://www.childnet.org/amy/default.asp

As you can see, they are all Text except the date, which is Date/Time.

Thank you for working with me on this!
AP



BeTheBall -> RE: sorting columns... I know, I know... (1/25/2007 16:54:41)

Hmmm. It doesn't sort correctly when the page loads either. Is this an Access database? If so, download the database from the server, do a repair and compact, and then upload again. Not sure this will fix it, but I noticed a couple of my pages not sorting correctly and they fell in line after I repaired and compacted.



aipnit -> RE: sorting columns... I know, I know... (1/25/2007 18:17:16)

quote:

do a repair and compact

Unfortunately that didn't work.


quote:

That means we will need to build the delimiters dynamically to match the datatype of the field upon which you are sorting.

What you suggested above, is that a grueling process?



BeTheBall -> RE: sorting columns... I know, I know... (1/25/2007 19:25:57)

No, but we probably need to at least get the text columns working before even worrying about it. I am a little stumped at the moment.

If you hardcode a column name do the results sort correctly on that column, such as this:

fp_sQry="SELECT * FROM viewclasses ORDER BY 'CourseTrainingName' ASC"



aipnit -> RE: sorting columns... I know, I know... (1/25/2007 19:31:57)

No - the results do not sort correctly. I tried CourseTrainingName and ClassDayEve.

Does that have to do with this line? Should I change it to something else?
fp_sDefault="SortColumn=CourseTrainingName"



BeTheBall -> RE: sorting columns... I know, I know... (1/25/2007 19:52:35)

Go back to the original line, but leave out the ASC. i.e.,

fp_sQry="SELECT * FROM viewclasses ORDER BY ::SortColumn::"



aipnit -> RE: sorting columns... I know, I know... (1/25/2007 19:55:30)

quote:

fp_sQry="SELECT * FROM viewclasses ORDER BY ::SortColumn::"


This produced the following error:

Database Results Wizard Error
Unable to find operator in query string. Query string currently is SELECT * FROM viewclasses ORDER BY ::SortColumn::



BeTheBall -> RE: sorting columns... I know, I know... (1/25/2007 20:07:09)

Try taking this:

<%
fp_sQry="SELECT * FROM viewclasses ORDER BY ::SortColumn:: ASC"

and changing it to this:

<%
If Request("SortColumn") = "" Then
SortColumn = CourseTrainingName
Else
SortColumn = Request("SortColumn")
End If
fp_sQry="SELECT * FROM viewclasses ORDER BY ::SortColumn:: ASC"



aipnit -> RE: sorting columns... I know, I know... (1/25/2007 20:13:32)

That produced the same error. I refreshed and cleared out cache and all that...

Database Results Wizard Error
Unable to find operator in query string. Query string currently is SELECT * FROM viewclasses ORDER BY ::SortColumn:: ASC



BeTheBall -> RE: sorting columns... I know, I know... (1/25/2007 20:17:24)

I need some sleep.

fp_sQry="SELECT * FROM viewclasses ORDER BY "& Request("SortColumn") &" ASC"



aipnit -> RE: sorting columns... I know, I know... (1/25/2007 20:24:31)

Thank you for your continuing efforts with my "problem", I really appreciate it!!! - I will continue to try tomorrow, it's time to go home and eat!

Have a great evening!
AP

It is pulling up from the database again, however, it still isn't sorting. http://www.childnet.org/amy/default.asp

This is what I have in my code:
<%
If Request("SortColumn") = "" Then
SortColumn = CourseTrainingName
Else
SortColumn = Request("SortColumn")
End If
fp_sQry="SELECT * FROM viewclasses ORDER BY '"& Request("SortColumn") &"' ASC"




Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.140625