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
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 GROUP BY EmployeeID
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
<html> <head> <title>The best tool for the task</title> </head>
<body> <table> <% Dim objConn Dim objConnString Dim getSQL Dim viewRS
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 %> <tr> <td style="width:200px;"><%=viewRS.Fields("Employee ID").Name%></td> <td style="width:200px;"><%=viewRS.Fields("Number Of Sales").Name%></td> </tr> <% Do While NOT viewRS.EOF %> <tr> <td style="width:200px;"><%=viewRS("Employee ID")%></td> <td style="width:200px;"><%=viewRS("Number Of Sales")%></td> </tr> <% viewRS.MoveNext Loop
Set viewSQL = Nothing objConn.Close %> </table> </body> </html>
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