ICoombs.co.uk

Contents

• ICoombs Home
• About
• ASP Free Members Club
• ASP Examples
  • Text Stream
  • Passing Parameters
  • Remove Characters
  • Date Time Functions
  • Append XML File
  • AJAX
  • GetRS Function
• Links
• Contact

ASP Free Members Club

Recent Members

GetRS Function

The GetRS Function Simplifies the retrieving of records from a database,  and even has an option for testing the query passed to the function.

The function has 3 parameters, the first is the SQL query, the second is an option for returning the records in an array or as a recordset and the third is the Query test option.

Usage:

To return an Array use...

arrRS = GetRS("SELECT * FROM TableName WHERE Field = 'Value'", "Array", False)

This will return an Array containing the records matching the Query, to access the array use arrRS(n, x) Where N is the field Number and X is the row identifier.

To return a Recordset use...

Set arrRS = GetRS("SELECT * FROM TableName WHERE Field = 'Value'", "Recordset", False)

This will return a standard recordset where you access the records and field in the usual manner, i.e. arrRS("FieldName") for accessing a field and arrRS.MoveNext to move to the next record.

To test your query use...

Set arrRS = GetRS("SELECT * FROM TableName WHERE Field = 'Value'", "Recordset", True)

Or

arrRS = GetRS("SELECT * FROM TableName WHERE Field = 'Value'", "Array", True)	

This will display your query and the query results in a table so you can check your query is returning the correct results

Testing this page:

To test this page on your own Access database just change the two variables at the top, srcDatabase is the path and file name of your database and strSQL is the query you want to run against the database. If you want to test on any other database just change the connection string or add your own connection string include file and change the ADODB.Connection to adoCon

NOTE: When testing the query the function terminates and will not execute any of the page below the function call, to change this just uncomment the two lines indicated in the code

<%
' *****************************************************
' * Function GetRs Usage *
' *****************************************************
' * *
' * GetRS("SQLQueryString", ReturnType, QueryTest) *
' * SQLQueryString = "SELECT * FROM tblTable" *
' * *
' * ReturnType = "Array" or "Recordset" *
' * If you want a GteRows array then use Array *
' * otherwise use Recordset to return as a RecordSet *
' * *
' * QueryTest = True or False *
' * True will display the query and the results *
' * False Returns the function results *
' * *
' *****************************************************

' *****************************************************
' * Database and Query setup *
' *****************************************************
srcDatabase = "DRIVE:\PATH\FILENAME.mdb"
strSQL = "SELECT TOP 5 * FROM tblTimeZones"
' *****************************************************

Function GetRS(SQLstring, ReturnType, QueryTest)
Set adoCon = Server.CreateObject("ADODB.Connection")

sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & srcDatabase
adoCon.Open sConnStr
Set objRS = adoCon.Execute(SQLstring)

'Test section
If QueryTest = True Then
Response.Write SQLstring & "<br /><br /><table border=""1""><tr>"
For Each Item in objRS.Fields
Response.Write "<td>" & Item.Name & "</td>"
Next
Response.Write "</tr><tr>"
Do While NOT objRS.EOF
For Each Item in objRS.Fields
If Item = "" OR IsNull(Item) Then
Response.Write "<td>" & "&nbsp;" & "</td>"
Else
Response.Write "<td>" & Item & "</td>"
End if
Next
Response.Write "</tr>"
objRS.MoveNext
Loop
Response.Write "</table>"
objRS.Close
adoCon.Close
Set objRS = Nothing
set adoCon = Nothing
' ############################################################################
' To allow the page to finish uncomment the next two lines
' GetRS = "Terminate"
' Exit Function
' ############################################################################
Response.Write "<br /><b>Page Execution Terminated</b>"
Response. End
End if

If UCase(ReturnType) = "ARRAY" Then
If Not objRS.EOF Then arrRS = objRS.GetRows()
Set objRS = Nothing
GetRS = arrRS
Elseif UCase(ReturnType) = "RECORDSET" Then
If Not objRS.EOF Then
Set GetRS = objRS
set objRS = Nothing
End if
End if
End Function
%>

 

Example Array

1 (ACDT) Australian Central Daylight Time

2 (ACST) Australian Central Standard Time

3 (ADT) Atlantic Daylight Time

4 (AEDT) Australian Eastern Daylight Time or Australian Eastern Summer Time

5 (AEST) Australian Eastern Standard Time

Example Record Set

ACDT (Australian Central Daylight Time)

ACST (Australian Central Standard Time)

ADT (Atlantic Daylight Time)

AEDT (Australian Eastern Daylight Time or Australian Eastern Summer Time)

AEST (Australian Eastern Standard Time)

Example QueryTest

SELECT TOP 5 * FROM tblTimeZones

ID	Abbreviation	Fullname	Location	Timezone	diff
1	ACDT	Australian Central Daylight Time	Australia	GMT + 10:30 hours	10.5
2	ACST	Australian Central Standard Time	Australia	GMT + 09:30 hours	9.5
3	ADT	Atlantic Daylight Time	North America	GMT - 03:00 hours	-3
4	AEDT	Australian Eastern Daylight Time or Australian Eastern Summer Time	Australia	GMT + 11:00 hours	11
5	AEST	Australian Eastern Standard Time	Australia	GMT + 10:00 hours	10

GetRS Comments

Please feel free to leave comments, questions or suggestions here, I will respond to questions as soon as possible but please leave your correct email address as I sometimes email responses to questions.

© ICoombs.co.uk, 2008