ASP Classic - Alphabetical lists

4 Jul 2009 3:01 PM

bcap

Hi,

I have a table of contacts and have the SQL listed as ORDER BY
LastName so my contacts list is alphabetical order.

However, I am looking to learn how to disply the list broken up by
letter for easier reading and finding. For example I would like my
page to look like this:

A

Adams, John
Appleseed, Johnny

B

Boggs, Wade
Bugs, Bunny
Bush, George

C

Columbus, Christoper

And so on ...

Does anyone have suggestions on the best way to do this or could
provide any samples?

Thank you kindly, in advance!

4 Jul 2009 3:29 PM

Roberto Franceschetti

Easy....

<%
 Set oConn = Server.CreateObject("ADODB.Connection")
 oConn.Open "Your_Connection_String_Here"
 txtSQL = "SELECT * FROM tblCustomers ORDER BY LastName"
 set oRS = oConn.Execute(txtSQL)
 LastFirstLetter = ""
 CurrentFirstLetter = ""
 Do While not oRS.EOF
 CurrentFirstLetter = UCase(Left(oRS("LastName"), 1))
 if CurrentFirstLetter <> LastFirstLetter then
 Response.Write " " & CurrentFirstLetter &_
 " "
 LastFirstLetter = UCase(CurrentFirstLetter)
 end if

Response.write oRS("LastName") & " "

     oRS.MoveNext
   Loop
   oRS.Close
%>

--
Roberto Franceschetti
LogSat Software
http://www.logsat.com

bcap wrote:

Show quoteHide quote

> Hi,
>
> I have a table of contacts and have the SQL listed as ORDER BY
> LastName so my contacts list is alphabetical order.
>
> However, I am looking to learn how to disply the list broken up by
> letter for easier reading and finding. For example I would like my
> page to look like this:
>
> A
>
> Adams, John
> Appleseed, Johnny
>
> B
>
> Boggs, Wade
> Bugs, Bunny
> Bush, George
>
>
> C
>
> Columbus, Christoper
>
>
> And so on ...
>
>
> Does anyone have suggestions on the best way to do this or could
> provide any samples?
>
> Thank you kindly, in advance!
>
>
>
>

4 Jul 2009 3:38 PM

bcap

works great! thank you very much!

4 Jul 2009 6:41 PM

Adrienne Boswell

Gazing into my crystal ball I observed Roberto Franceschetti
<roberto_remove_t***@logsat.com> writing in news:4a4f7569$0$11847
$9a6e1***@unlimited.newshosting.com:

> Easy....
>
><%
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open "Your_Connection_String_Here"
> txtSQL = "SELECT * FROM tblCustomers ORDER BY LastName"

Select * is bad practice. Always explicitly state column names in
queries.

Show quoteHide quote

> set oRS = oConn.Execute(txtSQL)
> LastFirstLetter = ""
> CurrentFirstLetter = ""
> Do While not oRS.EOF
> CurrentFirstLetter = UCase(Left(oRS("LastName"), 1))
> if CurrentFirstLetter <> LastFirstLetter then
> Response.Write " " & CurrentFirstLetter &_
> " "
> LastFirstLetter = UCase(CurrentFirstLetter)
> end if
> Response.write oRS("LastName") & " "
> oRS.MoveNext
> Loop
> oRS.Close
> %>
>

There are more semantically correct ways of doing this, but that is OT
for this group.

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

4 Jul 2009 10:01 PM

Roberto Franceschetti

Adrienne Boswell wrote:
Show quoteHide quote

> Gazing into my crystal ball I observed Roberto Franceschetti
> <roberto_remove_t***@logsat.com> writing in news:4a4f7569$0$11847
> $9a6e1***@unlimited.newshosting.com:
>
>> Easy....
>>
>> <%
>> Set oConn = Server.CreateObject("ADODB.Connection")
>> oConn.Open "Your_Connection_String_Here"
>> txtSQL = "SELECT * FROM tblCustomers ORDER BY LastName"
>
> Select * is bad practice. Always explicitly state column names in
> queries.
>
>> set oRS = oConn.Execute(txtSQL)
>> LastFirstLetter = ""
>> CurrentFirstLetter = ""
>> Do While not oRS.EOF
>> CurrentFirstLetter = UCase(Left(oRS("LastName"), 1))
>> if CurrentFirstLetter <> LastFirstLetter then
>> Response.Write " " & CurrentFirstLetter &_
>> " "
>> LastFirstLetter = UCase(CurrentFirstLetter)
>> end if
>> Response.write oRS("LastName") & " "
>> oRS.MoveNext
>> Loop
>> oRS.Close
>> %>
>>
>
> There are more semantically correct ways of doing this, but that is OT
> for this group.
>

Looking at the "type" of question, I thought this simplified answer was
the best that applied here, no need to complicate things. Not to mention
that all I set aside to reply were 3 minutes :-)

--
Roberto Franceschetti
LogSat Software
http://www.logsat.com

5 Jul 2009 3:25 AM

news

> Adrienne Boswell at Home
>><%
>> Set oConn = Server.CreateObject("ADODB.Connection")
>> oConn.Open "Your_Connection_String_Here"
>> txtSQL = "SELECT * FROM tblCustomers ORDER BY LastName"
>
> Select * is bad practice. Always explicitly state column names in
> queries.
>

I thought that I was the only one that stated this fact.

OK.
For those that are new to working with databases rather
Access, SQL Server, MySQL, Oracle or others.

When you do this.

sql = "Select * FROM MyTable"

And you have this going on.

<%=rs("Field1")%><%=rs("Field15")%><%=rs("Field19")%>

OK.
When the page renders the information, the Query run against the databse,
and has the search all columns to find the
Once that match the Criteria.
So, this puts a lot of strain on the database, as well as spiking the CPU
during the process. (Tested on a large SQL Server database
And it spiked for 45-seconds at 65% on a 2.66gb Process 2Gb Memory, not
good)

So.
Supply your column names as suggest by myself as well as: Adrienne.

So it should look like this. (Example of a lot of fields)

sql = "Select Field1, Field2, Field3, Field4, Field5,
Field............,Field19 FROM MyTable"

This way, when you run your query against your table, it has the Fieldnames
readily available and there is not extra strain on the Server
To search and find the Columns, as they are their ready to service the
request.

Have a good one.
Wayne

5 Jul 2009 7:12 AM

Evertjan.

news wrote on 05 jul 2009 in microsoft.public.inetserver.asp.general:

Show quoteHide quote

>> Adrienne Boswell at Home
>>><%
>>> Set oConn = Server.CreateObject("ADODB.Connection")
>>> oConn.Open "Your_Connection_String_Here"
>>> txtSQL = "SELECT * FROM tblCustomers ORDER BY LastName"
>>
>> Select * is bad practice. Always explicitly state column names in
>> queries.
>>
>
> I thought that I was the only one that stated this fact.
>
> OK.
> For those that are new to working with databases rather
> Access, SQL Server, MySQL, Oracle or others.
>
> When you do this.
>
> sql = "Select * FROM MyTable"
>
> And you have this going on.
>
> <%=rs("Field1")%><%=rs("Field15")%><%=rs("Field19")%>
>
> OK.
> When the page renders the information, the Query run against the
> databse, and has the search all columns to find the
> Once that match the Criteria.
> So, this puts a lot of strain on the database, as well as spiking the
> CPU during the process. (Tested on a large SQL Server database
> And it spiked for 45-seconds at 65% on a 2.66gb Process 2Gb Memory,
> not good)
>
> So.
> Supply your column names as suggest by myself as well as: Adrienne.
>
> So it should look like this. (Example of a lot of fields)
>
> sql = "Select Field1, Field2, Field3, Field4, Field5,
> Field............,Field19 FROM MyTable"
>
> This way, when you run your query against your table, it has the
> Fieldnames readily available and there is not extra strain on the
> Server To search and find the Columns, as they are their ready to
> service the request.

You are right in the sense that it is [at least somewhat] cpu intensive.

1
However many pages are low in uses access/time,
sometimes the wole site is not heavily accessed,
and often a page is just for database management
by the webmaster only.

In these circumstances the use of "SELECT *" is not bad practice,
provided that the programmer knows his stuff, and that is what
programming is all aboud, ain't it?

===========

2
However the searching of the fieldnames should only be needed once every
sql execution, and could be only a fraction of the total procedure time
if more than a few records are retrieved.

I cannot believe the time for getting the database names exceeds the
fetching of one record. If not better try better programmed db-engine.

So the extra strain in these circumstances will be minimal.

==========

3
However:

"SELECT *"

plus

<%=rs(0)%><%=rs(1)%>

will not suffer the "extra strain" above, methinks.

[not that I recomment this last practice for multi field dbs]

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

5 Jul 2009 3:06 PM

news

>Evertjan
> I cannot believe the time for getting the database names exceeds the
> fetching of one record. If not better try better programmed db-engine.
>
> So the extra strain in these circumstances will be minimal.
>

Did not mention a single record did I?
I mentioned a large Database. That is pulling a LOT of records back when I
first started
With SQL Server.

I took the same script about a year or so later and re-wrote it using the
column names in my SQL Statement.
And the time was a HUGE difference in returning the records.
I learn more as time goes on, and you spread the information that you learn
to others in hopes that they
Will not make the same mistakes as you once did.

Enjoy

5 Jul 2009 5:04 PM

Evertjan.

news wrote on 05 jul 2009 in microsoft.public.inetserver.asp.general:

> Evertjan wrote:

[quoteing corrected]

>> I cannot believe the time for getting the database names exceeds the
>> fetching of one record. If not better try better programmed
>> db-engine.
>>
>> So the extra strain in these circumstances will be minimal.
>>
> Did not mention a single record did I?

No, but I did, and with reason, see below, where I repeat my reasoning.

> I mentioned a large Database. That is pulling a LOT of records back
> when I first started
> With SQL Server.

Why should a single sql execution, returning say 1000 records,
need to resolve the database names more than once?

Resolving the database names that were not resolved before
because of using "SELECT *" will reasonably not take more than the cpu-
time to resolve one record.

If it does, the engine must be badly written.

So the larger the number of returned records, the smaller the relative
impact of "SELECT *" must be.

> I took the same script about a year or so later and re-wrote it using
> the column names in my SQL Statement.
> And the time was a HUGE difference in returning the records.

Seems improbable to me, seen the above.

Perhaps you also improved your script in more ways,
used different indexing,
or used a newer SQL or ASP version?

The real test should be to test the same side by side,
using both sql strings, and do numerical comparisons, wouldn't it?

> I learn more as time goes on, and you spread the information that you
> learn to others in hopes that they

Good for you, and goor of you.

> Will not make the same mistakes as you once did.

Me? Ah, general you!

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)