Published:
13 Nov 2003
|
Abstract
This tutorial is an attempt to simplify and standardize database access within ASP applications. The examples can be used in any ASP application. While working with RecordSets, the GetRows method is used, thus releasing the database connection immediately. |
 |
by Justin Owens
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
26598/
187
|
|
|
|
| Database Class Code |
-- Database Class --
In order to return data, the data is returned from the class with an array using getrows, so the array must be erased after use to free up memory.
<%
‘Usage
‘Returning Data
‘Set db = New dbaccess
’Call db.DBOpenAccess("/dblocation/dbfile.mdb")
’Call db.OpenRec()
’arrArray = db.ExecuteSQL("SELECT * FROM [widgets];")
’Call db.CloseRec()
’Call db.DBClose()
’Response.Write(arrArray(0,0))
’Erase arrArray
‘-----------OR-------------
‘Update Functions
‘Set db = New dbaccess
’Call db.DBOpenAccess("/dblocation/dbfile.mdb")
’Call db.ExecuteUpdateSQL("DELETE FROM [widgets] WHERE [ID] = 1;")
’Call db.DBClose()
'++----------------------------------------------------------------
'++Class dbaccess
'++Author: Justin Owens
'++Date: 08/01/2002
'++You may use this class when developing your own applications
'++as long as this section of comments remains with the class.
'++----------------------------------------------------------------
Class dbaccess
'Declarations
Private cnnObj
Private objRec
Private strConnStr
'Subs
'Class Initialization
Private Sub Class_Initialize()
'Empty
End Sub
'Terminate Class
Private Sub Class_Terminate()
'Empty
End Sub
'Open Access Database
Public Sub DBOpenAccess(strDBLoc)
strConnStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
strConnStr = strConnStr & server.mappath(strDBLoc) & ";"
Set cnnObj = server.CreateObject("ADODB.Connection")
cnnObj.Open strConnStr
End Sub
'Open SQL Database
Public Sub DBOpenSQL(strSERVER,strDATABASE,strUID,strPWD)
strConnStr = "DRIVER={SQL Server};SERVER=" & strSERVER & ";UID=" & strUID & ";PWD=" & strPWD & ";DATABASE=" & strDATABASE & ";"
Set cnnObj = server.CreateObject("ADODB.Connection")
cnnObj.Open strConnStr
End Sub
'Close Database
Public Sub DBClose()
cnnObj.Close
Set cnnObj = Nothing
End Sub
'Functions
'Open Recordset
Public Function OpenRec()
Set objRec = server.CreateObject("ADODB.Connection")
End Function
'Execute SQL
'Returns GetRows array - if no recset returned, it returns false.
Public Function ExecuteSQL(strSQLStatement)
Set objRec = cnnObj.Execute(strSQLStatement)
If Not objRec.EOF Then
ExecuteSQL = objRec.GetRows()
Else
ExecuteSQL = False
End If
End Function
'Execute SQL
'Updates, inserts or deletes records in tables
Public Function ExecuteUpdateSQL(strSQLStatement)
Set objRec = cnnObj.Execute(strSQLStatement)
End Function
'Close RecordSet
Public Function CloseRec()
objRec.close
Set objRec = Nothing
End Function
End Class
%> |
|
|
|
|
Article Feedback
User Comments
Title:
well done!
Name:
ashok
Date:
6/19/2008 5:17:57 AM
Comment:
This is wonderful code! Though it could be more readable, it covered everything I needed to create my database class for my website! Thanks so much for posting it!
|
Title:
It is correct
Name:
Jim
Date:
4/23/2008 3:01:24 AM
Comment:
Baldev Rawat,,
The code is correct as posted.
|
Title:
i cannot under stand
Name:
MAJD
Date:
4/7/2008 8:30:12 AM
Comment:
i cannot under stand
|
Title:
Very well done!
Name:
stars2night
Date:
12/19/2007 2:35:47 PM
Comment:
This is wonderful code! Though it could be more readable, it covered everything I needed to create my database class for my website! Thanks so much for posting it!
|
Title:
Error
Name:
Baldev Rawat
Date:
9/26/2007 4:50:33 AM
Comment:
Hi Everybody
In Open Recordset Function
It should be ADODB.Recordset instead on ADODB.Connection because here we want to create a recordset.
|
Title:
Re : Ed
Name:
Martin
Date:
3/23/2007 5:56:43 AM
Comment:
You can use the IsArray() function to check if the array exist ;o)
|
Title:
bass
Name:
saamy
Date:
2/20/2007 6:51:27 AM
Comment:
very nice work.thank you , will be useful for all
|
Title:
Re: Ed
Name:
Justin
Date:
2/15/2006 2:18:59 AM
Comment:
I think the issue you are running into is the error handling for the returned array outside of the class.
If you are using the example code, you should notice I did not include error handling for uninitialized arrays (null recordsets). If the array is uninitialized and you try to erase the array, it will raise an error.
|
Title:
Great
Name:
Ed
Date:
2/14/2006 6:32:03 PM
Comment:
Works ok,
However if no results returned it should return false, instead the script fails. Anyone have a clue?
|
Title:
great
Name:
smail
Date:
6/11/2005 7:02:59 AM
Comment:
goooooooooooob & tk u very much
|
Title:
Database Class Code
Name:
Rudi
Date:
4/3/2005 4:57:49 PM
Comment:
After spending 6 hours trying to find the correct syntax for setting up reads from an Access database, I came accross this code sample. What a find! Of all the code samples I tried (an could not get to work), this was that only one that worked right away (just adding my variable names). Thanks a million!
|
Title:
great
Name:
ben
Date:
3/9/2005 4:51:20 PM
Comment:
great little pioece of code, thanks very much
|
|
|
|
|
|
