
	ASP Shopping Cart, Forum & BBS - all for $20 from CodeToad Plus!

Home || ASP | ASP.Net | C++/C# | DHTML | HTML | Java | Javascript | Perl | VB | XML || CodeToad Plus! || Forums || RAM

Search Site:

Home » ASP » Article

ADO Connection & Recordset Functions

Article by:

Coretys (8/4/2003)

	Add to Favourites
	Tell a friend

Summary:

These functions should help dealing with ADO Connections and Recordsets a bit easier. If you have any suggestions, then please tell me.

Viewed: 78488 times

Rating (9 votes):

2.4 out of 5

Rate this Article

Read Comments

Post Comments

ADO Connection & Recordset Functions

Examples to the code below:

Set cn = OpenCn("db1")

Opens a connection to the "db1" database.

Set rs = OpenRs(cn, "SELECT * FROM tblProducts;")

Opens a recordset. You need to set the connetion first.

CloseRs(rs)

Closes a recordset.

CloseCn(cn)

Closes a connection.

arr = GetArrayAll("db1", "SELECT * FROM tblProducts;")

arr is an array containing the records from tblProducts.

arr = GetArrayPaged("db1", "SELECT * FROM tblProducts;", 20, 3, intPageTotal, intRecTotal)

arr is an array containing one specific page of the recordset. The values intPageTotal and intRecTotal will be set by the function and can be used later on to create a navigation.

Select All Code

' OPEN ADO CONNECTION TO MS ACCESS DATABASE

Function OpenCn(tmpDB)
    Set cn = CreateObject("ADODB.Connection")
    Select Case tmpDB
        Case "db1"
            dbPath = Server.MapPath("..\..\MyFolder\MyDb1.mdb")
        Case "db2"
            dbPath = Server.MapPath("..\..\MyFolder\MyDb2.mdb")
        Case "db3"
            dbPath = Server.MapPath("..\..\MyFolder\MyDb3.mdb")
    End Select
    On Error Resume Next
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
    cn.Open()
    If Err.Number <> 0 Then strErr = strErr & Err.Number & " / " & Err.Description & "<br>"
    Set OpenCn = cn
    Set cn = Nothing
End Function

' CLOSE ADO CONNECTION

Sub CloseCn(cn)
    On Error Resume Next
    If IsObject(cn) Then
        If cn.State = 1 Then: cn.Close
    End If
    If Err.Number <> 0 Then strErr = strErr & Err.Number & " / " & Err.Description & "<br>"
    Set cn = Nothing
End Sub

' OPEN ADO RECORDSET

Function OpenRs(cnRs, strSource)
    On Error Resume Next
    Set rs = CreateObject("ADODB.Recordset")
    rs.Source = strSource
    rs.ActiveConnection = cnRs
    rs.CursorType = 3        ' adOpenDynamic
    rs.LockType = 3        ' adLockOptimistic
    rs.Open
    Set OpenRs = rs
    If Err.Number <> 0 Then strErr = strErr & Err.Number & " / " & Err.Description & "<br>"
    Set rs = Nothing
End Function

' CLOSE ADO RECORDSET

Sub CloseRs(rs)
    On Error Resume Next
    If IsObject(rs) Then
        If rs.State = 1 Then: rs.Close
    End If
    If Err.Number <> 0 Then strErr = strErr & Err.Number & " / " & Err.Description & "<br>"
    Set rs = Nothing
End Sub

' GET ARRAY OF ALL RECORDS

Function GetArrayAll(tmpDB, strSource)
    On Error Resume Next
    Set cn = OpenCn(tmpDB)
    Set rs = OpenRs(cn, strSource)
    If IsObject(rs) Then
        If Not rs.EOF Then GetArrayAll = rs.GetRows()
    End If
    If Err.Number <> 0 Then strErr = strErr & Err.Number & " / " & Err.Description & "<br>"
    CloseRs(rs)
    CloseCn(cn)
End Function

' GET ARRAY OF PAGED RECORDSET

Function GetArrayPaged(tmpDB, tmpSource, tmpPageSize, tmpPageNum, tmpPageTotal, tmpRecTotal)
    On Error Resume Next
    Set cn = OpenCn(tmpDB)
    Set rs = OpenRs(cn, tmpSource)
    If IsObject(rs) Then
        If Not rs.EOF Then
            tmpRecTotal = rs.RecordCount + 0
            tmpPageTotal = Int((tmpRecTotal - 1) / tmpPageSize) + 1
            If tmpPageNum < 1 Then tmpPageNum = 1
            If tmpPageNum > tmpPageTotal Then tmpPageNum = tmpPageTotal
            rs.CacheSize = tmpPageSize
            rs.PageSize = tmpPageSize
            rs.AbsolutePage = tmpPageNum
            GetArrayPaged = rs.GetRows(tmpPageSize)
        End If
    Else
        GetArrayPaged = rs
    End If
    If Err.Number <> 0 Then strErr = strErr & Err.Number & " / " & Err.Description & "<br>"
    CloseRs(rs)
    CloseCn(cn)
End Function

Useful Links

CodeToad Experts

Can't find the answer?
Our Site experts are answering questions for free in the CodeToad forums

Rate this article: Poor Excellent

View highlighted Comments

User Comments on 'ADO Connection & Recordset Functions'

RELATED ARTICLES
ASP FilesystemObject by Jeff Anderson An introduction to the Filesystemobject

ASP GetTempName by Jeff Anderson Use the GetTempName method to create a randomly generated temporary file on the server.

ASP Format Date and Time Script by Jeff Anderson An ASP script showing the variety of date and time formats possible using the FormatDateTime Function.

ASP OpenTextFile by Jeff Anderson An introduction to the OpenTextFile Method of the FileSystemObject

Email validation using Regular Expression by Jeff Anderson Using regular expression syntax is an exellent way to thoroughly validate an email. It's possible in ASP.

Add or Subtract Hours in SQL or ASP using DateAdd by Jeff Anderson A beginners guide to using the SQL DATEADD function to add or subtract hours. Particularly useful when setting the time displayed on the ASP page to a different time zone (eg when the server is in the US, and the site is for a UK audience).

The asp:radiobutton and asp:radiobuttonlist control by David Sussman, et al In HTML, radio buttons are used when we need to make multiple sets of choices available, but we want the user to select only one of them.

The asp:checkbox and asp:checkboxlist control by David Sussman, et al Checkboxes are similar to radio buttons, and in HTML, they were used to allow multiple choices from a group of buttons.

ASP FileExists by Jeff Anderson An introduction to the FileExistsMethod of the FileSystemObject

Concatenate strings in sql by Jeff Anderson A brief introduction to concatenating strings in an sql query (using SQL server or access databases).

Recent Forum Threads

•	Run a program both on windows and linux
•	VERO.SurfCAM.v2014
•	Schlumberger.Petrel.V2013.2
•	Petrel.V2013.2
•	Altair.HyperWorks.v12
•	VoluMill.v6.1
•	VoluMill.NEXION.6
•	VERO.SurfCAM.v2014
•	Schlumberger.Petrel.V2013.2

Recent Articles

	ASP GetTempName
	Decode and Encode UTF-8
	ASP GetFile
	ASP FolderExists
	ASP FileExists
	ASP OpenTextFile
	ASP FilesystemObject
	ASP CreateFolder
	ASP CreateTextFile
	Javascript Get Selected Text

privacy - contact - advertise - hot links - link to us - submit your article