
	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: 60610 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

Fat Cow Hosting

FatCow Review

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

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.

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.

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

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

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

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).

ASP CreateTextFile by Jeff Anderson An explanation of the CreateTextFile Method, part of the ASP FileSystemObject

Recent Forum Threads

•	Significant Factors
•	Perl array access
•	Re: huffman encoding and decoding in C++...
•	Perl One Liner: Replace {(
•	Re: html including php, accessing the functions
•	Something like an INI editor or a DelimitedText-Editor
•	Error Deleting File or Folder
•	Re: How can use ASP.NET RegularExpressionValidator for alphanumeric 10 digit input
•	Re: drop-down menu selected value

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