Ordering data from SQL/ASP

[Fawke101]

Hi there,

I have an application that rips data from an SQL server and displays it in
columnar form on an ASP page, using a
Do until RS.EOF loop.

I wish to add 2 arrows to each column (up and down) and when clicked, orders
the data on the page by that column (up - A-Z and down Z-A etc.....)
Is this possible in ASP or would i need some Javascript/Client side code to
do this?

The problem is the ASP SELECT statement orders the data initially, i just
need to know how the user can re-order the data after it is on the page?

Any ideas/examples much appreciated.


--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

[Bob Barrows [MVP]]

Re: Ordering data from SQL/ASP

Fawke101 wrote:[color=blue]
> Hi there,
>
> I have an application that rips data from an SQL server and displays
> it in columnar form on an ASP page, using a
> Do until RS.EOF loop.
>
> I wish to add 2 arrows to each column (up and down) and when clicked,
> orders the data on the page by that column (up - A-Z and down Z-A
> etc.....)
> Is this possible in ASP or would i need some Javascript/Client side
> code to do this?
>
> The problem is the ASP SELECT statement orders the data initially, i
> just need to know how the user can re-order the data after it is on
> the page?
>
> Any ideas/examples much appreciated.[/color]

You will need client-side code for this. You should be able to find examples
via a Google search using the keywords:

DHTML sort table columns

Please follow up in a client-side newsgroup: look for a newsgroup with
"dhtml" in its name, or go to one of the .scripting groups.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Mike D]

Ordering data from SQL/ASP

I use ASP to do it it just requires another trip to the
server. Build hyperlinks out of the arrows and pass your
sort instructions through the querystring. I pass "U"
or "D" for Asending or Desending then build your order for
you select statement from that.

I would post sample code but my server is currently off.
If I get my server up I will post back later.

Mike



[color=blue]
>-----Original Message-----
>Hi there,
>
>I have an application that rips data from an SQL server[/color]
and displays it in[color=blue]
>columnar form on an ASP page, using a
>Do until RS.EOF loop.
>
>I wish to add 2 arrows to each column (up and down) and[/color]
when clicked, orders[color=blue]
>the data on the page by that column (up - A-Z and down Z-[/color]
A etc.....)[color=blue]
>Is this possible in ASP or would i need some[/color]
Javascript/Client side code to[color=blue]
>do this?
>
>The problem is the ASP SELECT statement orders the data[/color]
initially, i just[color=blue]
>need to know how the user can re-order the data after it[/color]
is on the page?[color=blue]
>
>Any ideas/examples much appreciated.
>
>
>--
>Thanks in advance
>
>Fawke
>
>Please remove ANTI and SPAM
>from my email address before emailing me.
>
>www.bradflack.com
>
>
>.
>[/color]

[Fawke101]

Re: Ordering data from SQL/ASP

Thanks for that Bob, i have joint posted this to a client side newsgroup so
hopefully should get some more feedback from there,

Thanks again

--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:ejmUWABEEH A.3836@TK2MSFTN GP09.phx.gbl...[color=blue]
> Fawke101 wrote:[color=green]
> > Hi there,
> >
> > I have an application that rips data from an SQL server and displays
> > it in columnar form on an ASP page, using a
> > Do until RS.EOF loop.
> >
> > I wish to add 2 arrows to each column (up and down) and when clicked,
> > orders the data on the page by that column (up - A-Z and down Z-A
> > etc.....)
> > Is this possible in ASP or would i need some Javascript/Client side
> > code to do this?
> >
> > The problem is the ASP SELECT statement orders the data initially, i
> > just need to know how the user can re-order the data after it is on
> > the page?
> >
> > Any ideas/examples much appreciated.[/color]
>
> You will need client-side code for this. You should be able to find[/color]
examples[color=blue]
> via a Google search using the keywords:
>
> DHTML sort table columns
>
> Please follow up in a client-side newsgroup: look for a newsgroup with
> "dhtml" in its name, or go to one of the .scripting groups.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>[/color]

[Bob Barrows [MVP]]

Re: Ordering data from SQL/ASP

Fawke101 wrote:[color=blue]
> Thanks for that Bob, i have joint posted this to a client side
> newsgroup so hopefully should get some more feedback from there,
>[/color]
Yes, I did not notice the crosspost until I saw your message in the other
group. Thank you for crossposting instead of multiposting.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Viatcheslav V. Vassiliev]

Re: Ordering data from SQL/ASP

You may find client-side code but if your table is large this code may be
too slow. The possible solution is to reload web-page when user presses a
button and pass parameter to the page that specifies sort column:

http://mydomain.com/mypage.asp?sort=mycolumn

and refill table on server with ORDER BY in SQL according sort parameter.

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE

"Fawke101" <guy@ANTIbradfl ack.SPAMcom> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: news:%23msO31AE EHA.3412@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> Hi there,
>
> I have an application that rips data from an SQL server and displays it in
> columnar form on an ASP page, using a
> Do until RS.EOF loop.
>
> I wish to add 2 arrows to each column (up and down) and when clicked,[/color]
orders[color=blue]
> the data on the page by that column (up - A-Z and down Z-A etc.....)
> Is this possible in ASP or would i need some Javascript/Client side code[/color]
to[color=blue]
> do this?
>
> The problem is the ASP SELECT statement orders the data initially, i just
> need to know how the user can re-order the data after it is on the page?
>
> Any ideas/examples much appreciated.
>
>
> --
> Thanks in advance
>
> Fawke
>
> Please remove ANTI and SPAM
> from my email address before emailing me.
>
> www.bradflack.com
>
>[/color]

[Bob Barrows [MVP]]

Re: Ordering data from SQL/ASP

Viatcheslav V. Vassiliev wrote:[color=blue]
> You may find client-side code but if your table is large this code
> may be too slow. The possible solution is to reload web-page when
> user presses a button and pass parameter to the page that specifies
> sort column:
>
> http://mydomain.com/mypage.asp?sort=mycolumn
>
> and refill table on server with ORDER BY in SQL according sort
> parameter.
>[/color]

A client-side solution does not have to involve bringing the entire table to
the client. For example, XMLHTTP can be used to request data from a
server-side page.

If this is not a "paging" situation, then the client already has all the
data, which can be sorted in client-side code. Why waste time and resources
by going back to the server? Of course, if you are paging through the data,
then you probably will need to go back to the server for data since the new
sort order might require that a different page of data be displayed.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Fawke101]

Re: Ordering data from SQL/ASP

Hi there,

The data displayed on the page is the data displayed on the page. No paging
situation is apparent and the client side code definately sounds the most
efficient in my situation. This is not a complex site, just ripping and
displaying data.

Could anyone give me any more specifics on how i can do this in JS/DHTML??

Thanks

--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:OAcEJICEEH A.3236@TK2MSFTN GP09.phx.gbl...[color=blue]
> Viatcheslav V. Vassiliev wrote:[color=green]
> > You may find client-side code but if your table is large this code
> > may be too slow. The possible solution is to reload web-page when
> > user presses a button and pass parameter to the page that specifies
> > sort column:
> >
> > http://mydomain.com/mypage.asp?sort=mycolumn
> >
> > and refill table on server with ORDER BY in SQL according sort
> > parameter.
> >[/color]
>
> A client-side solution does not have to involve bringing the entire table[/color]
to[color=blue]
> the client. For example, XMLHTTP can be used to request data from a
> server-side page.
>
> If this is not a "paging" situation, then the client already has all the
> data, which can be sorted in client-side code. Why waste time and[/color]
resources[color=blue]
> by going back to the server? Of course, if you are paging through the[/color]
data,[color=blue]
> then you probably will need to go back to the server for data since the[/color]
new[color=blue]
> sort order might require that a different page of data be displayed.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>[/color]

[Bob Barrows [MVP]]

Re: Ordering data from SQL/ASP

Fawke101 wrote:[color=blue]
> Hi there,
>
> The data displayed on the page is the data displayed on the page. No
> paging situation is apparent and the client side code definately
> sounds the most efficient in my situation. This is not a complex
> site, just ripping and displaying data.
>
> Could anyone give me any more specifics on how i can do this in
> JS/DHTML??
>[/color]
Did you try the Google search? I saw several examples when I tried the
keywords I recommended earlier. In fact, the very first hit from that search
was this:

http://www.kryogenix.org/code/browser/sorttable/

It looks very slick.

I know there have been several articles about how to do this on the
4GuysFromRolla site.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Viatcheslav V. Vassiliev]

Re: Ordering data from SQL/ASP

Even in non-paging situation requesting data from server could be faster.
That is because HTML DOM does not have methods like
beginUpdate()\e ndUpdate() and when re-arranging rows web-page will be
updated (repainted) on every operation - even on medium-size dataset this
can be very time consuming. Solutions that store data on client-side outside
table and create table on the fly will require much coding and probably will
not be browser independent.

Plus with request to the server client will have refreshed data with
updates/deletes that happened in last minutes/seconds.

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: news:OAcEJICEEH A.3236@TK2MSFTN GP09.phx.gbl...[color=blue]
> Viatcheslav V. Vassiliev wrote:[color=green]
> > You may find client-side code but if your table is large this code
> > may be too slow. The possible solution is to reload web-page when
> > user presses a button and pass parameter to the page that specifies
> > sort column:
> >
> > http://mydomain.com/mypage.asp?sort=mycolumn
> >
> > and refill table on server with ORDER BY in SQL according sort
> > parameter.
> >[/color]
>
> A client-side solution does not have to involve bringing the entire table[/color]
to[color=blue]
> the client. For example, XMLHTTP can be used to request data from a
> server-side page.
>
> If this is not a "paging" situation, then the client already has all the
> data, which can be sorted in client-side code. Why waste time and[/color]
resources[color=blue]
> by going back to the server? Of course, if you are paging through the[/color]
data,[color=blue]
> then you probably will need to go back to the server for data since the[/color]
new[color=blue]
> sort order might require that a different page of data be displayed.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>[/color]

[Fawke101]

Re: Ordering data from SQL/ASP

ok, do you have any more details on this, or any links to sites, as i seem
to be returning few relevant hits on google?

Thanks again

--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Viatchesla v V. Vassiliev" <msnewsgroup@ww w-sharp.com> wrote in message
news:OC2ksLDEEH A.1452@TK2MSFTN GP09.phx.gbl...[color=blue]
> Even in non-paging situation requesting data from server could be faster.
> That is because HTML DOM does not have methods like
> beginUpdate()\e ndUpdate() and when re-arranging rows web-page will be
> updated (repainted) on every operation - even on medium-size dataset this
> can be very time consuming. Solutions that store data on client-side[/color]
outside[color=blue]
> table and create table on the fly will require much coding and probably[/color]
will[color=blue]
> not be browser independent.
>
> Plus with request to the server client will have refreshed data with
> updates/deletes that happened in last minutes/seconds.
>
> //------------------------------------
> Regards,
> Vassiliev V. V.
> http://www-sharp.com -
> Scripting/HTA/.Net Framework IDE
>
> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
> ÓÌÅÄÕÀÝÅÅ: news:OAcEJICEEH A.3236@TK2MSFTN GP09.phx.gbl...[color=green]
> > Viatcheslav V. Vassiliev wrote:[color=darkred]
> > > You may find client-side code but if your table is large this code
> > > may be too slow. The possible solution is to reload web-page when
> > > user presses a button and pass parameter to the page that specifies
> > > sort column:
> > >
> > > http://mydomain.com/mypage.asp?sort=mycolumn
> > >
> > > and refill table on server with ORDER BY in SQL according sort
> > > parameter.
> > >[/color]
> >
> > A client-side solution does not have to involve bringing the entire[/color][/color]
table[color=blue]
> to[color=green]
> > the client. For example, XMLHTTP can be used to request data from a
> > server-side page.
> >
> > If this is not a "paging" situation, then the client already has all the
> > data, which can be sorted in client-side code. Why waste time and[/color]
> resources[color=green]
> > by going back to the server? Of course, if you are paging through the[/color]
> data,[color=green]
> > then you probably will need to go back to the server for data since the[/color]
> new[color=green]
> > sort order might require that a different page of data be displayed.
> >
> > Bob Barrows
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >[/color]
>
>[/color]

[Jeff Cochran]

Re: Ordering data from SQL/ASP

On Mon, 22 Mar 2004 17:45:56 -0000, "Fawke101"
<guy@ANTIbradfl ack.SPAMcom> wrote:
[color=blue]
>ok, do you have any more details on this, or any links to sites, as i seem
>to be returning few relevant hits on google?[/color]

This site has a list of methods for you:

http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=javascript+table+sort

And yes, it's a Google search. For "javascript table sort" which
seemed pretty obvious to search on. And the first 42 links all are
right on the topic you're asking about. Could be more but I figured
after checking 42 I'd done *way* too much of your work and that you
need to get some experience under your belt as well.

I have the feeling that these won't help you either though, since I
don't think you have the background or experience to understand them
based on your posts, so you may need to start with something a little
less daunting and work through some tutorials first.

Jeff

[Viatcheslav V. Vassiliev]

Re: Ordering data from SQL/ASP

Your code may look like (look for line breaks):

<% @Language="JScr ipt"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test</title>
<meta name="GENERATOR " content="MSHTML 6.00.2800.1400" >
<script>
function sortDesc(column )
{
//replace test.asp with valid address
window.navigate ("test.asp?sort =" + column + "&dir=desc" );
}

function sortAsc(column)
{
//replace test.asp with valid address
window.navigate ("test.asp?sort =" + column);
}
</script>
</head>
<body>
<%
var conn = Server.CreateOb ject("ADODB.Con nection");
var rs = null;

var strSQL = "SELECT * FROM [Customers]";

if (Request.QueryS tring("sort").C ount > 0)
{
var strSort = Request.QuerySt ring("sort");
strSQL += " ORDER BY [" + strSort + "]";

if (Request.QueryS tring("dir").Co unt > 0)
{
var strDir = Request.QuerySt ring("dir");
if (strDir == "desc") strSQL += " DESC";
}
}
else
{
//Apply default sort order
}

conn.Open("Prov ider=SQLOLEDB;I ntegrated Security=SSPI;I nitial
catalog=Northwi nd;");
rs = conn.Execute(st rSQL);
%>
<table border="1">
<thead>
<tr>
<%
for (i = 0; i < rs.Fields.Count ; i++)
{
var fldName = rs.Fields(i).Na me;
Response.Write( "<TH>" + fldName + "<BR>");
Response.Write( "<BUTTON onclick=\"sortA sc('" + fldName +
"')\">A</BUTTON>");
Response.Write( "<BUTTON onclick=\"sortD esc('" + fldName +
"')\">V</BUTTON>");
Response.Write( "</TH>\r\n");
}
%>
</tr>
</thead>
<tbody>
<tr>
<%
rs.MoveFirst();
while (!rs.EOF)
{
Response.Write( "<TR>\r\n") ;

for (i = 0; i < rs.Fields.Count ; i++)
{
Response.Write( "<TD>" + rs.Fields(i).Va lue + "</TD>\r\n");
}

Response.Write( "</TR>\r\n");
rs.MoveNext();
}
%>
</tr>
</tbody>
</table>
</body>
</html>

"Fawke101" <guy@ANTIbradfl ack.SPAMcom> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: news:e05gHWDEEH A.2088@TK2MSFTN GP10.phx.gbl...[color=blue]
> ok, do you have any more details on this, or any links to sites, as i seem
> to be returning few relevant hits on google?
>
> Thanks again
>
> --
> Thanks in advance
>
> Fawke
>
> Please remove ANTI and SPAM
> from my email address before emailing me.
>
> www.bradflack.com
> "Viatchesla v V. Vassiliev" <msnewsgroup@ww w-sharp.com> wrote in message
> news:OC2ksLDEEH A.1452@TK2MSFTN GP09.phx.gbl...[color=green]
> > Even in non-paging situation requesting data from server could be[/color][/color]
faster.[color=blue][color=green]
> > That is because HTML DOM does not have methods like
> > beginUpdate()\e ndUpdate() and when re-arranging rows web-page will be
> > updated (repainted) on every operation - even on medium-size dataset[/color][/color]
this[color=blue][color=green]
> > can be very time consuming. Solutions that store data on client-side[/color]
> outside[color=green]
> > table and create table on the fly will require much coding and probably[/color]
> will[color=green]
> > not be browser independent.
> >
> > Plus with request to the server client will have refreshed data with
> > updates/deletes that happened in last minutes/seconds.
> >
> > //------------------------------------
> > Regards,
> > Vassiliev V. V.
> > http://www-sharp.com -
> > Scripting/HTA/.Net Framework IDE
> >
> > "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×[/color][/color]
ÎÏ×ÏÓÔÑÈ[color=blue][color=green]
> > ÓÌÅÄÕÀÝÅÅ: news:OAcEJICEEH A.3236@TK2MSFTN GP09.phx.gbl...[color=darkred]
> > > Viatcheslav V. Vassiliev wrote:
> > > > You may find client-side code but if your table is large this code
> > > > may be too slow. The possible solution is to reload web-page when
> > > > user presses a button and pass parameter to the page that specifies
> > > > sort column:
> > > >
> > > > http://mydomain.com/mypage.asp?sort=mycolumn
> > > >
> > > > and refill table on server with ORDER BY in SQL according sort
> > > > parameter.
> > > >
> > >
> > > A client-side solution does not have to involve bringing the entire[/color][/color]
> table[color=green]
> > to[color=darkred]
> > > the client. For example, XMLHTTP can be used to request data from a
> > > server-side page.
> > >
> > > If this is not a "paging" situation, then the client already has all[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > data, which can be sorted in client-side code. Why waste time and[/color]
> > resources[color=darkred]
> > > by going back to the server? Of course, if you are paging through the[/color]
> > data,[color=darkred]
> > > then you probably will need to go back to the server for data since[/color][/color][/color]
the[color=blue][color=green]
> > new[color=darkred]
> > > sort order might require that a different page of data be displayed.
> > >
> > > Bob Barrows
> > > --
> > > Microsoft MVP -- ASP/ASP.NET
> > > Please reply to the newsgroup. The email account listed in my From
> > > header is my spam trap, so I don't check it very often. You will get a
> > > quicker response by posting to the newsgroup.
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Bob Barrows [MVP]]

Re: Ordering data from SQL/ASP

Viatcheslav V. Vassiliev wrote:[color=blue]
> Even in non-paging situation requesting data from server could be
> faster. That is because HTML DOM does not have methods like
> beginUpdate()\e ndUpdate() and when re-arranging rows web-page will be
> updated (repainted) on every operation -[/color]

This is totally contrary to my experience. I never perceive a repaint until
the entire table resort is completed. In fact, there have been times I've
wanted to write something to the screen during this type of operation and
have been unable to without using settimeout.
[color=blue]
> even on medium-size dataset
> this can be very time consuming. Solutions that store data on
> client-side outside table and create table on the fly will require
> much coding and probably will not be browser independent.[/color]

I can't argue with the browser independence, but I'm sure code could be
written that is compatible with most browsers.
[color=blue]
>
> Plus with request to the server client will have refreshed data with
> updates/deletes that happened in last minutes/seconds.[/color]

If that is a concern, then of course one needs to refresh the data from the
database. If not, there is no point in making another trip to the server.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.