Here is an asp DataWrapper class that you may use to perform various database operations without having to manage opening and closing recordsets and connections.
The Error properties will let you know when there is any error.
The RS2XML Function returns well formed XML built on the recordSet passed into it. it uses the database name as the root element, Column names as Element Names.
The rest should be pretty much self-explainatory.
here is The ASP Class:
(WATCH OUT FOR Line Wrapping here!)
And Here is the Example page on how to use it:
Or you can download them from :
I hope you all find it usefull.
- J
The Error properties will let you know when there is any error.
The RS2XML Function returns well formed XML built on the recordSet passed into it. it uses the database name as the root element, Column names as Element Names.
The rest should be pretty much self-explainatory.
here is The ASP Class:
(WATCH OUT FOR Line Wrapping here!)
Code:
<%
'==================================================
' Generic ASP Data Access Class
' Jason Withrow - 2004
'
' Distribute Freely - Enjoy
'
' jason@myveryownwebsite.com
'==================================================
Class cDBUtils
'===========================
'## Private Class Members ##
'===========================
Private m_sConnect
Private m_oConnection
Private m_oTempRS
Private m_lErrorNumber
Private m_sErrDescription
Private m_lErrorCount
Private vbQuote
'=============================================
'## Public Error Number Class Property ##
'=============================================
Public Property Get ErrorNumber()
ErrorNumber = m_lErrorNumber
End Property
Public Property Let ErrorNumber(lInput)
m_lErrorNumber = lInput
End Property
'=================================================
'## Public Error Description Class Property ##
' holds error msg for LAST error encountered
'================================================
Public Property Get ErrorDescription()
ErrorDescription = m_sErrDescription
End Property
Public Property Let ErrorDescription(sInput)
m_sErrDescription = sInput
End Property
'=============================================
'## Public Error Count Class Property ##
'=============================================
Public Property Get ErrorCount()
ErrorCount = m_lErrorCount
End Property
Public Property Let ErrorCount(lInput)
m_lErrorCount = lInput
End Property
'=====================================
'## Private ADO Connection Property ##
'=====================================
Private Property Get Connection()
'## IF m_oConnection is an object and Connection is not already Set
'## Create a New instance of Connection Property, otherwise reuse existing
If isObject(m_oConnection) AND Not isObject(Connection) Then
Set Connection = m_oConnection
End IF
End Property
Private Property Set Connection(oInput)
IF Not isObject(m_oConnection) Then
Set m_oConnection = oInput
End IF
End Property
'=============================================
'## Public Read/Write ADO Connection String ##
'=============================================
Public Property Get ConnectionString()
ConnectionString = m_sConnect
End Property
Public Property Let ConnectionString(sInput)
m_sConnect = sInput
End Property
Private Sub OpenConnection()
IF isObject(Connection) Then
IF (Connection.State = 0) Then
Connection.Open ConnectionString
End IF
Else
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open ConnectionString
End IF
End Sub
'=====================================
'## Private CloseConnection ##
'## {Closes an Open Connection} ##
'=====================================
Private Sub CloseConnection()
IF isObject(Connection) AND (Connection.State > 0) Then
Connection.Close
End IF
End Sub
'=====================================================
'## Private GetStatus ##
'## Returns The State of the Connection ##
'## Used internally to determine Connection Status ##
'=====================================================
Private Function GetStatus()
IF isObject(Connection) Then
GetStatus = Connection.State
Else
GetStatus = 0
End IF
End Function
'===============================================================
'## Function Insertdata
' Inserts Data into DB via inline inserts
' Returns:
' Boolean (True = Success | False = Error)
'===============================================================
Public Function InsertData(sTableName, [ParamArray])
Dim uBoundX, tempString
Dim iNameIterator, iValueIterator
Dim sql, oCommand
IF Not isArray([ParamArray]) Then
IncrementErrorCount
AddErrortext("Array expected in cDBUtils:InsertData")
AddErrorNumber(1303)
InsertData = False
Exit Function
End IF
tempString = "INSERT INTO [" & sTableName & "] ("
On Error Resume Next
uBoundX = uBound([ParamArray],1)
For iNameIterator = 0 to uBoundX
IF (iNameIterator <> 0) AND (iNameIterator <> uBoundX) AND (iNameIterator > 0) Then
tempString = tempString & [ParamArray](iNameIterator,0) & ","
Else
tempString = tempString & [ParamArray](iNameIterator,0)
End IF
Next
tempString = tempString & ") VALUES ("
For iValueIterator = 0 to uBoundX
IF (iValueIterator <> 0) AND (iValueIterator <> uBoundX) AND (iValueIterator > 0) Then
IF isNull([ParamArray](iValueIterator,1)) Then
tempString = tempString & "'<NULL>'" & ","
Else
tempString = tempString & [ParamArray](iValueIterator,1) & ","
End IF
Else
IF isNull([ParamArray](iValueIterator,1)) Then
tempString = tempString & "'<NULL>'"
Else
tempString = tempString & [ParamArray](iValueIterator,1)
End IF
End IF
Next
tempString = tempString & ")"
sql = tempString
On Error Resume Next
OpenConnection
Set oCommand = Server.CreateObject("ADODB.Command")
oCommand.ActiveConnection = Connection
oCommand.CommandType = &H0001
oCommand.CommandText = sql
oCommand.Execute sql
Set oCommand = Nothing
IF err.number <> 0 Then
IncrementErrorCount
AddErrortext(err.Description & " in cDBUtils:InsertData")
AddErrorNumber(1304)
InsertData = False
Else
InsertData = True
End IF
End Function
'===============================================================
'## Function Updatedata
' Updates Data via inline inserts
' Returns:
' Boolean (True = Success | False = Error)
'===============================================================
Public Function UpdateData(sTableName, [ParamArray], sClause)
Dim uBoundX, tempString
Dim iNameIterator, iValueIterator
Dim sql, oCommand
IF Not isArray([ParamArray]) Then
IncrementErrorCount
AddErrortext("Array expected in cDBUtils:UpdateData")
AddErrorNumber(1307)
InsertData = False
Exit Function
End IF
tempString = "UPDATE [" & sTableName & "] "
On Error Resume Next
uBoundX = uBound([ParamArray],1)
For iNameIterator = 1 to uBoundX
IF (iNameIterator <> 0) AND (iNameIterator <> uBoundX) AND (iNameIterator > 0) Then
tempString = tempString & "SET " & [ParamArray](iNameIterator,0) & " = " & [ParamArray](iNameIterator,1) & ","
Else
tempString = tempString & "SET " & [ParamArray](iNameIterator,0) & " = " & [ParamArray](iNameIterator,1)
End IF
Next
sql = tempString & " " & sClause
On Error Resume Next
OpenConnection
Set oCommand = Server.CreateObject("ADODB.Command")
oCommand.ActiveConnection = Connection
oCommand.CommandType = &H0001
oCommand.CommandText = sql
oCommand.Execute sql
Set oCommand = Nothing
IF err.number <> 0 Then
IncrementErrorCount
AddErrortext(err.Description & " in cDBUtils:UpdateData")
AddErrorNumber(1308)
UpdateData = False
Else
UpdateData = True
End IF
End Function
Private Function NullHelper(sInput)
If isNull(sInput) or isEmpty(sInput) Then
NullHelper = "'<NULL>'"
End IF
End Function
Public Function ExecuteInlineSql(sql)
On Error Resume Next
OpenConnection
Set m_oTempRS = Server.CreateObject("ADODB.RecordSet")
m_oTempRS.Open sql, Connection, 3, 2
If err.number <> 0 Then
IncrementErrorCount
AddErrortext(Err.Description & " in cDBUtils:InsertData")
AddErrorNumber(1305)
ExecuteInlineSql = False
Else
Set ExecuteInlineSql = m_oTempRS
End IF
End Function
Public Function ExecProc(sProc, [ParamArray])
Dim uBoundX, uBoundY, tempString
On Error Resume Next
'======================================
' Determine if proc has params
'======================================
IF isArray([ParamArray]) Then
uBoundX = uBound([ParamArray])
Dim iValueIterator
tempString = "EXEC " & sProc & " "
'Retrieve param names
For iValueIterator = 0 to uBoundX
IF (iValueIterator <> 0) AND (iValueIterator <> uBoundX) AND (iValueIterator > 0) Then
tempString = tempString & [ParamArray](iValueIterator) & ","
Else
tempString = tempString & [ParamArray](iValueIterator)
End IF
Next
Else
tempString = "EXEC " & sProc
End IF
Dim sql
sql = tempString
OpenConnection
Set m_oTempRS = Server.CreateObject("ADODB.RecordSet")
m_oTempRS.Open sql, Connection, 3, 2
IF err.number <> 0 Then
IncrementErrorCount
AddErrortext(Err.Description & " in cDBUtils:ExecProc")
AddErrorNumber(1306)
ExecProc = False
Else
Set ExecProc = m_oTempRS
End IF
End Function
Private Sub IncrementErrorCount()
ErrorCount = (ErrorCount + 1)
End Sub
Private Sub AddErrorText(sInput)
ErrorDescription = sInput
End Sub
Private Sub AddErrorNumber(lInput)
ErrorNumber = lInput
End Sub
'============================================================
'## Public ResetErrors
' Returns : nothing
' Resets Error Properties
' Use this to reset error properties of this class
'===========================================================
Public Sub ResetErrors()
ErrorCount = 0
ErrorDescription = Null
ErrorNumber = 0
End Sub
'===================================================
' Public Function RS2XL
' Input: RecordSet Object
' OutPut: XML String - format below:
' <ROW index=1>
' <ColumnName>Column value</ColumnName>
'</ROW>
'===================================================
Public Function RS2XML(byRef oRS)
If Not isObject(oRS) Then
IncrementErrorCount
AddErrorText("RecordSet Expected in method cDBUtils:RS2XML")
AddErrorNumber(1301)
Exit Function
End IF
Dim iRowCount, iColCount
Dim tempString, sElementName
iRowCount = 1
On Error Resume Next
oRS.MoveFirst
tempString = "<" & Connection.DefaultDatabase & ">" & vbCrLf
Do While Not oRS.EOF
tempString = (tempString & "<ROW Index=" & vbQuote & iRowCount & vbQuote & ">" & vbCrLf)
For iColCount = 0 To oRS.Fields.Count - 1
sElementName = oRS.Fields(iColCount).Name
tempString = (tempString & "<" & sElementName & ">" & Validify(oRS.Fields(iColCount).Value) & "</" & sElementName & ">" & vbCrLf)
Next
tempString = tempString & "</ROW>" & vbCrLF
oRS.MoveNext
iRowCount = (iRowCount + 1)
Loop
tempString = (tempString & "</" & Connection.DefaultDatabase & ">")
If err.number <> 0 Then
IncrementErrorCount
AddErrorText(err.Description & " in method RS2XML")
AddErrorNumber(1302)
RS2XML = False
Else
RS2XML = tempString
End IF
Set oRS = Nothing
End Function
Private Function Validify(sInput)
sInput = Replace(sInput, "]", "")
sInput = Server.HTMLEncode(sInput)
Validify = sInput
End Function
'=========================================
'## Class_Terminate ##
'## Fires during class destruction ##
'=========================================
Private Sub Class_Terminate()
If IsObject(m_oConnection) Then
CloseConnection
Set m_oConnection = Nothing
End IF
If isObject(m_oTempRS) Then
If m_oTempRS.State > 0 Then
m_oTempRS.Close
End IF
Set m_oTempRS = Nothing
End IF
End Sub
'================================
' Class Constructor / Initializer
'================================
Private Sub Class_Initialize()
vbQuote = Chr(34)
'Place ConnectionString Here
'ConnectionString = Application("sConnect")
ConnectionString = "Provider=SQLOLEDB;Password=dunno;User ID=sa;Initial Catalog=NorthWind;Data Source=WITHROWJXP;"
ErrorCount = 0
ErrorDescription = Null
ErrorNumber = 0
End Sub
'=============================================
' ## Shameless Plugs ##
'=============================================
' Support Independent Music
' Visit
' [URL unfurl="true"]http://www.daisyricochet.com[/URL]
' and
' [URL unfurl="true"]http://parisRailroad.com[/URL] today!
'============================================
End Class
%>And Here is the Example page on how to use it:
Code:
<%@ Language=VBScript %>
<!--#include File="dbutils.asp"-->
<html>
<head>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
</head>
<body>
<%
Set oDB = New cDBUtils
'Retrieves Connection String
Response.Write(oDB.ConnectionString & "<BR>")
'=========================================================
' EXAMPLE:
' Using ExecuteInlineSql to Return a RecordSet
'========================================================
Dim oRS
Set oRS = oDB.ExecuteInlineSql("SELECT * FROM Customers")
Do While Not oRS.EOF
Response.Write(oRS.Fields("ContactName") & "<BR>")
oRS.MoveNext
Loop
Set oRS = Nothing
'=========================================================
' EXAMPLE:
' Using ExecProc Without Parameters
'IMPORTANT!!!
' MAKE SURE you pass a Null reference in to 2nd Arg
'========================================================
'======================================================
' MAKE SURE Params are in the order the PROC Expects!
'======================================================
Dim vArray1
vArray1 = null '<-- Set to Null for procs without input params
Set oRS = oDB.ExecProc("[Ten Most Expensive Products]", vArray1)
Do While Not oRS.EOF
Response.Write(oRS.Fields("TenMostExpensiveProducts") & "<BR>")
oRS.MoveNext
Loop
Set oRS = Nothing
'========================================================
' EXAMPLE:
' Using ExecProc WITH Parameters
'IMPORTANT!!!
' Ensure that params are single quoted if need be
'========================================================
Dim vArray2(0)
vArray2(0) = "'ALFKI'"
Set oRS = oDB.ExecProc("[CustOrdersOrders]", vArray2)
Do While Not oRS.EOF
Response.Write(oRS.Fields("OrderID") & "<BR>")
oRS.MoveNext
Loop
Dim sXML
sXML = oDB.RS2XML(oRS)
Response.Write(sXML)
Set oRS = Nothing
Dim vArray3(11,1)
vArray3(1,0) = "CustomerID" 'ColumnName
vArray3(1,1) = "'ASPD7'" 'Value to insert (notice the single quotes for types that required them!)
vArray3(2,0) = "CompanyName" 'ColumnName
vArray3(2,1) = "'ASP DB Class Test'" 'Value to insert
vArray3(3,0) = "ContactName"
vArray3(3,1) = "'Jason Withrow'"
vArray3(4,0) = "ContactTitle"
vArray3(4,1) = "'Developer'"
vArray3(5,0) = "Address"
vArray3(5,1) = "'21 Jump Street'"
vArray3(6,0) = "City"
vArray3(6,1) = "'Bloomington'"
vArray3(7,0) = "Region"
vArray3(7,1) = Null
vArray3(8,0) = "PostalCode"
vArray3(8,1) = "'02345'"
vArray3(9,0) = "Country"
vArray3(9,1) = "'USA'"
vArray3(10,0) = "Phone"
vArray3(10,1) = "'555-123-6969'"
vArray3(11,0) = "Fax"
vArray3(11,1) = Null
Dim bWhat
bWhat = oDB.InsertData("Customers", vArray3)
IF oDB.ErrorCount > 0 Then
Response.Write("ERROR!!!" & oDB.ErrorDescription & " " & oDB.ErrorNumber & "<BR>")
End IF
Response.Write(bWhat)
oDB.ResetErrors 'Reset errors
'========================================================
' EXAMPLE:
' Using UpdateDate to Update a table
'IMPORTANT!!!
' Ensure that params are single quoted if need be
'
'========================================================
Dim vArray4(1,1)
vArray4(1,0) = "CustomerID"
vArray4(1,1) = "'ABCD1'"
'Call UpdateData specifying
' 1 - Table to Update
' 2 - Array of Field / value pairs to update
' 3 - Option SQL Clause (Pass in "" to update with no Clause)
bWhatNow = oDB.UpdateData("Customers", vArray4, "WHERE CustomerID = 'ASPD2'")
IF oDB.ErrorCount > 0 Then
Response.Write("ERROR!!!" & oDB.ErrorDescription & " " & oDB.ErrorNumber & "<BR>")
End IF
Response.Write(bWhatNow)
Set oDB = Nothing
%>
</body>
</html>Or you can download them from :
I hope you all find it usefull.
- J
