ADO Functions for ASP

[hrothenb@bcpl.net]

<%

'// ADO function library
' Declare the LockType and CursorLocation constants
Const adLockBatchOptimistic = 4
Const adUseClient = 3

'---------------------------------------
'// Retrive a simple recordset from a SELECT statement
Function SQLSelect(strSQL, strConnect)

Dim oConn
Dim oRs
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.CursorLocation = adUseClient
oRs.LockType = adLockBatchOptimistic
'response.write "<br> Connection string: " &
strConnect
oConn.ConnectionString = strConnect
oConn.Open
Set oRs.ActiveConnection = oConn
oRs.Open strSQL
'response.write "<br>Recordset count: [" &
oRs.recordcount & "]<br>"
Set SQLSelect = oRs
Set oRs.ActiveConnection = Nothing
oConn.Close
End Function
'---------------------------------------
Function SQLExecuteQuery(strSQL, strConnect)

Dim oConn, oCmd
Dim iRows
Set oConn = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")
oConn.Open strConnect
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = strSQL
oCmd.CommandType = 1 'adCmdText
oCmd.Execute(iRows)
SQLExecuteQuery = iRows
oConn.Close
End Function

'---------------------------------------
'---------------------------------------
Function GetRecordsetContents(rs)
Dim strOutput
Dim I
If Not (rs.BOF = True And rs.EOF = True) Then
strOutput = strOutput & "<br>"
strOutput = "<table " & table_options &
"><caption>" & heading & "</caption>" & chr(10)
strOutput = strOutput & "<tr>" & chr(10)
For I = 0 To rs.Fields.Count -1
strOutput = strOutput & "<th " &
header_options & ">" &
CStr(NulltoBlank(rs.Fields(I).Name)) & "</th>" &
chr(10)
Next
strOutput = strOutput & "</tr>" & chr(10)
While Not rs.EOF
strOutput = strOutput & "<tr>" &
chr(10)
For I = 0 To rs.Fields.Count -1
strOutput = strOutput & "<td " &
datacell_options & ">" &
CStr(NulltoBlank(rs.Fields(I).Value)) & "</td>" &
chr(10)
Next
strOutput = strOutput & "</tr>" &
chr(10)
rs.MoveNext
Wend
strOutput = strOutput & "</table>" &
chr(10)
strOutput = strOutput & "<br>"
Else
strOutput = heading & "<br>No records to
display."
End If

GetRecordsetContents = strOutput

End Function
'---------------------------------------
Function NulltoBlank(strValue)
If IsNull(strValue) Then
strValue = ""
End If
NulltoBlank = strValue
END Function
'---------------------------------------
%>

[Howard Rothenburg]

The reason I posted this is that I saw a few requests for help creating
ADO functions for ASP at the google groups site, in
microsoft.public.inetserver.asp.db, including one by caijunfu. I
couldn't post a reply to the messages at google, so I had to post a
separate message through a usenet client.

I just wanted to help but welcome any comments on the code or improved
versions.

I hope other people looking for this information find it useful.


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Atrax]

they could be made a little more generic. Here's an early version of
some of mine (JScript)

[url]http://rtfm.atrax.co.uk/infinitemonkeys/articles/asp/911.asp[/url]

they've been changed substantially since then, so I guess I should
update the article one of these days.



________________________________________
Atrax. MVP, IIS
[url]http://rtfm.atrax.co.uk/[/url]

newsflash : Atrax.Richedit 1.0 now released.
[url]http://rtfm.atrax.co.uk/infinitemonkeys/components/Atrax.RichEdit/[/url]

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Howard Rothenburg wrote:

> The reason I posted this is that I saw a few requests for help
> creating ADO functions for ASP at the google groups site, in
> microsoft.public.inetserver.asp.db, including one by caijunfu. I
> couldn't post a reply to the messages at google, so I had to post a
> separate message through a usenet client.

It would have been helpful to quote at least a little bit of the original
message so people, including caijunfu, could see the context. :-)

>
> I just wanted to help but welcome any comments on the code or improved
> versions.

OK, these seem like excellent functions that are very similar to ones I used
to write for my VB applications (minus the dynamic sql of course: once I
found out about stored procedures, there was no way I was going to cobble
together another sql string in VB code). Using client-side, disconnected
recordsets are ideal in the VB world, since VB apps work on the users'
desktops and there is no worry about multi-threading, throughput, or
consuming system resources. In ASP, however, the developer does have to
worry about these things.

Disconnected recordsets (any recordset for that matter) are large binary
objects that consume a fair amount of system resources. Additionally,
looping through a recordset is extremely slow, compared to looping through
something like an array. If you need to maximize throughput on your web
server, both of these things can be fatal. That is why I almost always
recommend using the cheapest, fastest cursor, the default server-side
read-only cursor, when retrieving data for display in ASP. The recommended
method for processing the recordset data depends on the amount of control
one needs over the output data. If minimal control over formatting is
needed, GetString is the fastest way to dump data to the screen. If you need
to control the formatting of various data elements, a GetRows array beats a
recordset loop by a mile. (If you need to see code that uses these
functions, just do a quick Google search for posts by me that contain either
of these words.)

HTH,
Bob Barrows

[Ken Schaefer]

Quoting Bob Barrows (who certainly knows his stuff):

: recordset loop by a mile. (If you need to see code that uses these
: functions, just do a quick Google search for posts by me that contain
either
: of these words.)

Hey, I'll post a simple function for free :-)

<%
Function ArrayFromSQL( _
ByRef objConn, _
ByVal strSQL _
)

Dim objRS ' as ADODB.Recordset

Set objRS = objConn.Execute(strSQL)

If not objRS.EOF then
ArrayFromSQL = objRS.GetRows
End If

objRS.Close
Set objRS = Nothing

End Function
'
'
strSQL = "SELECT field1 FROM myTable
arrResults = ArrayFromSQL(objConn, strSQL)
%>

Cheers
Ken




"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:O7IhT0hbDHA.1832@TK2MSFTNGP09.phx.gbl...
: Howard Rothenburg wrote:
: > The reason I posted this is that I saw a few requests for help
: > creating ADO functions for ASP at the google groups site, in
: > microsoft.public.inetserver.asp.db, including one by caijunfu. I
: > couldn't post a reply to the messages at google, so I had to post a
: > separate message through a usenet client.
:
: It would have been helpful to quote at least a little bit of the original
: message so people, including caijunfu, could see the context. :-)
:
: >
: > I just wanted to help but welcome any comments on the code or improved
: > versions.
:
: OK, these seem like excellent functions that are very similar to ones I
used
: to write for my VB applications (minus the dynamic sql of course: once I
: found out about stored procedures, there was no way I was going to cobble
: together another sql string in VB code). Using client-side, disconnected
: recordsets are ideal in the VB world, since VB apps work on the users'
: desktops and there is no worry about multi-threading, throughput, or
: consuming system resources. In ASP, however, the developer does have to
: worry about these things.
:
: Disconnected recordsets (any recordset for that matter) are large binary
: objects that consume a fair amount of system resources. Additionally,
: looping through a recordset is extremely slow, compared to looping through
: something like an array. If you need to maximize throughput on your web
: server, both of these things can be fatal. That is why I almost always
: recommend using the cheapest, fastest cursor, the default server-side
: read-only cursor, when retrieving data for display in ASP. The recommended
: method for processing the recordset data depends on the amount of control
: one needs over the output data. If minimal control over formatting is
: needed, GetString is the fastest way to dump data to the screen. If you
need
: to control the formatting of various data elements, a GetRows array beats
a
: recordset loop by a mile. (If you need to see code that uses these
: functions, just do a quick Google search for posts by me that contain
either
: of these words.)
:
: HTH,
: Bob Barrows
:
:

[Ken Schaefer]

Unfortunately:
[url]http://www.google.com.au/search?q=Bob+Barrows+Looping+through+array[/url]
doesn't return much :-(

<%
Function PlainIterateArray ( _
ByVal ArrayToDisplay _
)

If not isArray(ArrayToDisplay) or isEmpty(ArrayToDisplay) then
Err.Raise (-1, "ArrayToDisplay", "Use an Array!")
End If

Dim i
Dim j

For i = 0 to UBound(ArrayToDisplay, 1)

For j = 0 to UBound(ArrayToDisplay, 0)

Response.Write(ArrayToDisplay(j,i) & " - ")

Next

Response.Write("<br>" & vbCrLf)

Next

End Function
%>

Cheers
Ken

From: "Bob Barrows" <reb_01501@yahoo.com>
Subject: Re: ADO Functions for ASP
Date: Friday, 29 August 2003 10:33 PM

Cool :-)
But that's the easy part. I was thinking more about the code used to loop
through the resulting array. :-)
Again - I've posted many examples, as I'm sure you have. But feel free to
post another freebie ... :-)

Bob

Ken Schaefer wrote:

>> recordset loop by a mile. (If you need to see code that uses these
>> functions, just do a quick Google search for posts by me that
>> contain either of these words.)

>
> Hey, I'll post a simple function for free :-)
>
> <%
> Function ArrayFromSQL( _
> ByRef objConn, _
> ByVal strSQL _
> )
>
> Dim objRS ' as ADODB.Recordset
>
> Set objRS = objConn.Execute(strSQL)
>
> If not objRS.EOF then
> ArrayFromSQL = objRS.GetRows
> End If
>
> objRS.Close
> Set objRS = Nothing
>
> End Function
> '
> '
> strSQL = "SELECT field1 FROM myTable
> arrResults = ArrayFromSQL(objConn, strSQL)
> %>
>
> Cheers
> Ken

[Bob Barrows]

:-)
Ah, but this does:
[url]http://www.google.com/groups?as_q=GetRows&safe=images&ie=UTF-8&oe=UTF-8&as_uauthors=Bob%20Barrows&lr=&hl=en[/url]

Bob
Ken Schaefer wrote:

> Unfortunately:
> [url]http://www.google.com.au/search?q=Bob+Barrows+Looping+through+array[/url]
> doesn't return much :-(
>
> <%
> Function PlainIterateArray ( _
> ByVal ArrayToDisplay _
> )
>
> If not isArray(ArrayToDisplay) or isEmpty(ArrayToDisplay) then
> Err.Raise (-1, "ArrayToDisplay", "Use an Array!")
> End If
>
> Dim i
> Dim j
>
> For i = 0 to UBound(ArrayToDisplay, 1)
>
> For j = 0 to UBound(ArrayToDisplay, 0)
>
> Response.Write(ArrayToDisplay(j,i) & " - ")
>
> Next
>
> Response.Write("<br>" & vbCrLf)
>
> Next
>
> End Function
> %>
>
> Cheers
> Ken
>
> From: "Bob Barrows" <reb_01501@yahoo.com>
> Subject: Re: ADO Functions for ASP
> Date: Friday, 29 August 2003 10:33 PM
>
> Cool :-)
> But that's the easy part. I was thinking more about the code used to
> loop through the resulting array. :-)
> Again - I've posted many examples, as I'm sure you have. But feel
> free to post another freebie ... :-)
>
> Bob
>
> Ken Schaefer wrote:

>>> recordset loop by a mile. (If you need to see code that uses these
>>> functions, just do a quick Google search for posts by me that
>>> contain either of these words.)

>>
>> Hey, I'll post a simple function for free :-)
>>
>> <%
>> Function ArrayFromSQL( _
>> ByRef objConn, _
>> ByVal strSQL _
>> )
>>
>> Dim objRS ' as ADODB.Recordset
>>
>> Set objRS = objConn.Execute(strSQL)
>>
>> If not objRS.EOF then
>> ArrayFromSQL = objRS.GetRows
>> End If
>>
>> objRS.Close
>> Set objRS = Nothing
>>
>> End Function
>> '
>> '
>> strSQL = "SELECT field1 FROM myTable
>> arrResults = ArrayFromSQL(objConn, strSQL)
>> %>
>>
>> Cheers
>> Ken