Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can other tools help me develop ASP

Tools to build ASP

How can other tools help me develop ASP

by  onpnt  Posted    (Edited  )
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
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
Code: 
<html>
<head>
<title>The best tool for the task</title>
</head>

<body>
<table>
<%
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
	%>
		<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

End If

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


Short! Maintainable! Efficient!

Happy Programming!
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top