Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: ASP (Active Server Pages) FAQ

Tools to build ASP

How can other tools help me develop ASP by onpnt
Posted: 24 Jan 04 (Edited 24 Jan 04)

This one is basically geared towards not using the tools that are available to you in order to get the output you need in your ASP pages.  I will not go into syntax or what functions are doing but give you a broad sense of how to program the correct way and to use the correct tool for the task.  This is one example out of hundreds.  Use it to get motivation to search out other ways to improve your skills.  

SQL in direct is the most powerful tool you have to get what you need from your backend resources.  That's what it was developed for and why it is so stadardized.  I dread seeing SQL not being used the way it should be used.  For a example of very poor development in a active page.  WeÆll use our favorite hacking database, NorthWind.  Say you need to get the total count of orders by the employee that took the order.  Visually you need this

Number of Orders    EmployeeID
123                     1
96                      2
127                     3
156                     4
42                      5
67                      6
72                      7
104                     8
43                      9

Meaning EmployeeID 1 has 123 entries.  What makes me cringe in my chair is someone getting the contents of the table like this

SELECT * FROM orders

And then taking some prehistoric way of looping though a recordset while creating a conditioning statement that scares the server into dragging out the process.  

IÆm not going to try and venture into how you would run through there and create the view you may need for this scenario, because I donÆt condone that method of getting data.  So how should you use the tools that you have available to you to make this efficient and easy to maintain along with just the correct programming you should be doing?  Take your SQL to the next level!  The language you use to write your ASP pages is one tool you have, not the only tool.  YouÆve probably been using SQL countless hundreds of times in your scripts with that SELECT * FROM orders situation.  So lets take that to the next level and cut our typing down, make your code easy to debug (maintain) and more efficient.

So IÆll throw it out there

SELECT Count(EmployeeID) AS [Number Of Sales],
EmployeeID AS [Employee ID]
FROM orders

Which outputs exactly what we needed from the table shown earlier?

What did I get here?  
1)    I have my column headings and whatever other means you want to identify the data.
2)    I got my data in the exact form I need it.
3)    I only have to display it with a pointer without conditioning

You can only imagine how long with you take in a horrid loop and If, Case or whatever the language would need to iterate through a SELECT * to get that view.
In order to show you how this works hereÆs a script to use that SQL to hit the database and generate the view with ASP.  Remember; use the best tool available for the task to be done.  ThatÆs programming and systems design!  It should never be questioned simply do to not knowing something as good as what youÆre used to using.

Here the script to play with  

<title>The best tool for the task</title>

Dim objConn
Dim objConnString
Dim getSQL
Dim viewRS

objConnString = "Driver=SQL Server;SERVER=XXXXX;uid=XXXXX;pwd=XXXXX;DATABASE=NorthWind"

Set objConn =    server.CreateObject("ADODB.Connection")
objConn.Open objConnString

getSQL = "SELECT Count(EmployeeID) AS [Number Of Sales], " & _
         "EmployeeID AS [Employee ID] " & _
         "FROM orders " & _
         "GROUP BY EmployeeID"

Set viewRS = objConn.Execute(getSQL)

If NOT viewRS.EOF Then
            <td style="width:200px;"><%=viewRS.Fields("Employee ID").Name%></td>
            <td style="width:200px;"><%=viewRS.Fields("Number Of Sales").Name%></td>
    Do While NOT viewRS.EOF
            <td style="width:200px;"><%=viewRS("Employee ID")%></td>
            <td style="width:200px;"><%=viewRS("Number Of Sales")%></td>

End If

Set viewSQL = Nothing

And the output as tested
Employee ID    Number Of Sales
1               123
2               96
3               127
4               156
5               42
6               67
7               72
8               104
9               43

Short!  Maintainable! Efficient!

Happy Programming!

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close