
	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

Sponsored by:

FindMyHosting - Web Hosting Search

Summary:

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

Viewed: 47534 times

Rating (6 votes):

2.7 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

Affordable Windows Hosting

Reseller Hosting

Web+Hosting=$1.66

Web Hosting

Hosting

Windows Hosting

Web Hosting Providers

Cheap Hosting

Advertise here

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'

Posted by : mike at 16:15 on Sunday, May 16, 2004

How can I use the -BETWEEN- statement(range of price e.g.(SQL))in an ASP page.

Thanks

To post comments you need to become a member. If you are already a member, please log in .

RELATED ARTICLES
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 FilesystemObject by Jeff Anderson An introduction to 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.

Creating a Dynamic Reports using ASP and Excel by Jeff Anderson A simple way to generate Excel reports from a database using Excel.

Create an ASP SQL Stored Procedure by Jeff Anderson A beginners guide to setting up a stored procedure in SQL server and calling it from an ASP page.

Creating an SQL Trigger by Jeff Anderson A beginners guide to creating a Trigger in SQL Server

ASP Shopping Cart by CodeToad Plus! Complete source code and demo database(Access, though SQL compatible) to an ASP database driven e-commerce shopping basket, taking the user through from product selection to checkout. Available to CodeToad Plus! Members

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

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 OpenTextFile by Jeff Anderson An introduction to the OpenTextFile Method of the FileSystemObject

Recent Forum Threads

•	Re: Perl Script - File Handling.
•	Open a file from website
•	Re: to open 5 terminals from one and also execute different commands on each terminal
•	read a selected multiple line, those should match some of the values read in the files..
•	Help me please
•	Empty the contents of a file.
•	Month Start, End dates
•	show hide problem in dynamic table creation
•	Re: ASP Sendmail has huge delay - ANYONE??

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

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