[thelist] Friday Freebie

Scott Dexter sgd at ti3.com
Fri Jan 26 13:54:31 CST 2001


(for those who waited in vain for last week's FF, I'm sorry. I was in
Steamboat, skiing my arse off)


<tip subject="disgusting cheat" type="SQL Server">
Hopefully, we're well aware of letting the database do our work for us,
right?

Well, just how far can we take that? We can take it all the way to writing
the html for us, mua ha ha ha ha! How? With a really ugly Select statement:

Select '<tr><td>'+FirstName+'</td>' as [<th>First Name</th>],
'<td>'+LastName+'</td></tr>' as [<th>Last Name</th>] from TbEmployees

--That's right, you can add in the <td>'s and <tr>'s to the output. Cool,
eh?

An example:
<%
' SetupSQL is a function I wrote, find it at the bottom
Set oConn=SetupSQL("DSN=MyServer;UID=ReadOnlyUser;PWD=uglyman")
sqlstr = "Select '<tr><td>'+FirstName+'</td>' as [<th>First Name</th>],
'<td>'+LastName+'</td></tr>' as [<th>Last Name</th>] from TbEmployees"

Set oRS = oConn.Execute(sqlstr)

outputstr = "<table border=1>"
for each fld in oRS.Fields
	outputstr = outputstr & fld.Name	
Next
do while not oRS.EOF
	outputstr = outputstr & oRS(0) & oRS(1) & vbNewLine
	oRS.MoveNext
Loop
outputstr = outputstr & "</table>"

response.write outputstr
%>

You'll get this for your output (example):

<table border=1><th>First Name</th><th>Last
Name</th><tr><td>THOMAS</td><td>DANIEL</td></tr>
<tr><td>MAMADOU</td><td>DIOP</td></tr>
<tr><td>MASSIEL</td><td>DIAZ</td></tr>
<tr><td>Dawn</td><td>Dyer</td></tr>
<tr><td>William</td><td>Dixson</td></tr>
<tr><td>ROBERT</td><td>DOMINGO</td></tr>
</table>

Pretty disgusting, eh? ;)
</tip>

<tip type="DB Setup function for ASP">
<%
Function SetupSQL(byval connstr)
Dim DBCON
Set DBCON = Server.CreateObject("ADODB.Connection")
DBCON.Open connstr
Set SetupSQL = DBCON
Set DBCON = Nothing
End Function
%>

Use:

<% Set oConn = SetupSQL("DSN=MyServer;UID=foouser;PWD=bar") %>
</tip>

sgd
--
work: http://www.ti3.com/
non: http://thinksafely.org/




More information about the thelist mailing list