ASP database Connection Classes

ASP Tutorial Database Tutorial Connection Class

Take into account the majority of the use of the database connection using a common process, so you need to modify the code, if you have established a database connection, the lines of comments off the line. The Mssql,access,mysql tutorial is built into the code, the way to connect the ORACLE4 database, and of course you can add or remove it in your source code. Modifications such as:

Dim a:a = Creatconn (0, "testdata", "localhost", "username", "UserPassword")
Dim b:b = Creatconn (1, "Data/%testdb%.mdb", "", "", "")

To illustrate, the 1th argument can be a string. If you are using Access, the 2nd parameter input relative path and absolute path are OK, and if you have a password, you can enter it in the 5th parameter, for example:

Dim C:c = Creatconn ("Access", "E:mywebdata%testdb%.mdb", "" "," "," Mdbpassword ")

<%
'==========================================================================
' File name: clsdbctrl.asp
' Function: Database Operation class
' Author: coldstone (coldstone[in]qq.com)
' Program version: v1.0.5
' Finish time: 2005.09.23
' Modified time: 2007.10.30
' Copyright notice: You can use this program code in any work, but please keep this copyright information.
' If you modify the code in the program and get a better application, please send me a copy, thank you.
'==========================================================================

Dim a:a = Creatconn (0, "master", "localhost", "sa", "") ' MSSQL database
' Dim a:a = Creatconn (1, Data/%testdb%.mdb, "", "", ") ' Access database
' Dim a:a = Creatconn (1, "E:mywebdata%testdb%.mdb", "", "", "Mdbpassword")
Dim conn
' Openconn () ' The default connection object established at load time conn, using database A by default
SUB Openconn:set conn = OC (a): End Sub
Sub Closeconn:co (conn): End Sub

Function oc (ByVal connstr)
On Error Resume Next
Dim objconn
Set objconn = Server.CreateObject ("Adodb.connection")
objConn.Open connstr
If Err.Number <> 0 Then
Response.Write ("<div id=" "Dberror" > Database server-side connection error, please contact your site administrator. </div> ")
' Response.Write ("error message:" & Err.Description)
Objconn.close
Set objconn = Nothing
Response.End
End If
Set oc = objconn
End Function

Sub Co (obj)
On Error Resume Next
Set obj = Nothing
End Sub

Function Creatconn (ByVal dbtype, ByVal strDB, ByVal Strserver, ByVal Struid, ByVal Strpwd)
Dim temps Tutorial Tr
Select Case DbType
Case "0", "MSSQL"
TempStr = "Driver={sql server};server=" &strserver& "uid=" &struid& ";p wd=" &strpwd& "; Database= "&strdb
Case "1", "Access"
Dim Tdb:if InStr (strDB, ":") >0 then:tdb = Strdb:else:tdb = Server.MapPath (STRDB): End If
TempStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" &tdb& "Jet oledb:database password=" &strpwd & ";"
Case "3", "MySQL"
TempStr = "driver={mysql};server=" &strserver& ";p ort=3306;option=131072;stmt=; Database= "&strdb&" uid= "&struid&";p wd= "&strpwd&";
Case "4", "Oracle"
TEMPSTR = "Driver={microsoft ODBC for Oracle};server=" &strserver& "uid=" &struid& ";p wd=" &strpwd & ";"
End Select
Creatconn = TempStr
End Function

Class Dbctrl
 private debug
 private idbconn
 private idberr
 
  Private Sub Class_Initialize ()
  debug = true     ' Debug mode open
  Idberr = ' Error occurred: '
& nbsp If IsObject (conn) then
   set idbconn = conn
  End If
 end Sub
 
 privat E Sub Class_Terminate ()
  Set idbconn = Nothing
  If Debug and idberr<> "error occurred:" Then Response.Write (Idberr)
 end Sub
 
 public Property Let Dbconn (Pdbconn)
  if IsObject (pdbconn) then
  ;  Set idbconn = Pdbconn
  Else
   Set idbconn = conn
  End If
 end property
 
 public Property Get Dberr ()
  Dberr = Idberr
 end property
 
 pu Blic Property Get version
  Version = ASP Database Ctrl v1.0 by Coldstone
 end Property

Public Function Autoid (ByVal tablename)
On Error Resume Next
Dim M_no,sql, M_firtempno
Set M_no=server.createobject ("Adodb.recordset")
Sql= "SELECT * from [" &tablename&] "
M_no.open sql,idbconn,3,3
If M_no.eof Then
Autoid=1
Else
Do as not m_no.eof
M_firtempno=m_no.fields (0). Value
M_no.movenext
If M_no.eof Then
Autoid=m_firtempno+1
End If
Loop
End If
If Err.Number <> 0 Then
Idberr = idberr & "Invalid query condition!" <br/> "
If Debug Then Idberr = idberr & "error message:" & Err.Description
Response.End ()
Exit function
End If
M_no.close
Set m_no = Nothing
End Function

Public Function Getrecord (ByVal tablename,byval Fieldslist,byval Condition,byval Orderfield,byval)
On Error Resume Next
Dim rstrecordlist
Set Rstrecordlist=server.createobject ("Adodb.recordset")
With Rstrecordlist
. ActiveConnection = Idbconn
. CursorType = 3
. LockType = 3
. Source = Wgetrecord (Tablename,fieldslist,condition,orderfield,shown)
. Open
If Err.Number <> 0 Then
Idberr = idberr & "Invalid query condition!" <br/> "
If Debug Then Idberr = idberr & "error message:" & Err.Description
. Close
Set rstrecordlist = Nothing
Response.End ()
Exit function
End If
End With
Set Getrecord=rstrecordlist
End Function

Public Function Wgetrecord (ByVal tablename,byval Fieldslist,byval Condition,byval Orderfield,byval)
Dim strselect
strselect= "SELECT"
If shown > 0 Then
Strselect = strselect & "Top" & Shown & ""
End If
If fieldslist<> "" Then
Strselect = strselect & fieldslist
Else
Strselect = strselect & "*"
End If
Strselect = Strselect & "from [" & TableName & "]"
If condition <> "" Then
Strselect = Strselect & "where" & Valuetosql (tablename,condition,1)
End If
If OrderField <> "" Then
Strselect = Strselect & "ORDER BY" & OrderField
End If
Wgetrecord = Strselect
End Function

Public Function Getrecordbysql (ByVal strselect)
On Error Resume Next
Dim rstrecordlist
Set Rstrecordlist=server.createobject ("Adodb.recordset")
With Rstrecordlist
. ActiveConnection =idbconn
. CursorType = 3
. LockType = 3
. Source = Strselect
. Open
If Err.Number <> 0 Then
Idberr = idberr & "Invalid query condition!" <br/> "
If Debug Then Idberr = idberr & "error message:" & Err.Description
. Close
Set rstrecordlist = Nothing
Response.End ()
Exit function
End If
End With
Set getrecordbysql = Rstrecordlist
End Function

Public Function Getrecorddetail (ByVal tablename,byval Condition)
On Error Resume Next
Dim Rstrecorddetail, Strselect
Set Rstrecorddetail=server.createobject ("Adodb.recordset")
With Rstrecorddetail
. ActiveConnection =idbconn
Strselect = "SELECT * FROM [" & TableName & "] where" & Valuetosql (tablename,condition,1)
. CursorType = 3
. LockType = 3
. Source = Strselect
. Open
If Err.Number <> 0 Then
Idberr = idberr & "Invalid query condition!" <br/> "
If Debug Then Idberr = idberr & "error message:" & Err.Description
. Close
Set rstrecorddetail = Nothing
Response.End ()
Exit function
End If
End With
Set Getrecorddetail=rstrecorddetail
End Function

 public function AddRecord (ByVal tablename, ByVal ValueList)
  On Error Resume Next
  Doexecute ( Waddrecord (tablename,valuelist))
  If Err.Number <> 0 then
   idberr = idberr & "Write to database error!" <br/> "
   if debug then Idberr = idberr &" error message: "& Err.Description
   ' doexecut E "rollback tran Tran_insert" ' If there is an Add transaction (transaction rollback)
   addrecord = 0
   Exit Function
  END If
  AddRecord = autoid (tablename)-1
 end function
 
 public function Waddrecord (byv Al TableName, ByVal valuelist)
  Dim tempsql, Tempfiled, tempvalue
  tempfiled = Valuetosql (tablename,va luelist,2)
  Tempvalue = Valuetosql (tablename,valuelist,3)
  Tempsql = "INSERT INTO [" & TableName & Amp "] (" & tempfiled & ") VALUES (" & Tempvalue & ")"
  Waddrecord = tempsql
 end function< /p>

Public Function UpdateRecord (ByVal tablename,byval Condition,byval ValueList)
On Error Resume Next
Doexecute (Wupdaterecord (tablename,condition,valuelist))
If Err.Number <> 0 Then
Idberr = idberr & Update Database Error! <br/> "
If Debug Then Idberr = idberr & "error message:" & Err.Description
' Doexecute ' rollback tran tran_update ' If there is an Add transaction (transaction Roll back)
UpdateRecord = 0
Exit function
End If
UpdateRecord = 1
End Function

Public Function Wupdaterecord (ByVal tablename,byval Condition,byval ValueList)
Dim tmpsql
Tmpsql = "Update [" &tablename& "] set"
Tmpsql = Tmpsql & Valuetosql (tablename,valuelist,0)
Tmpsql = Tmpsql & "where" & Valuetosql (tablename,condition,1)
Wupdaterecord = Tmpsql
End Function

Public Function DeleteRecord (ByVal tablename,byval Idfieldname,byval Idvalues)
On Error Resume Next
Dim sql
sql = "Delete from [&tablename&] where [' &idfieldname&]" in ("
If IsArray (idvalues) Then
sql = SQL & "SELECT [" &idfieldname& "] from [" &tablename& "] where" & Valuetosql (Tablename,idvalue s,1)
Else
sql = SQL & idvalues
End If
sql = SQL & ")"
Doexecute (SQL)
If Err.Number <> 0 Then
Idberr = idberr & Delete data Error! <br/> "
If Debug Then Idberr = idberr & "error message:" & Err.Description
' Doexecute ' rollback tran Tran_delete ' If there is an Add transaction (transaction Roll back)
DeleteRecord = 0
Exit function
End If
DeleteRecord = 1
End Function

Public Function Wdeleterecord (ByVal tablename,byval Idfieldname,byval Idvalues)
On Error Resume Next
Dim sql
sql = "Delete from [&tablename&] where [' &idfieldname&]" in ("
If IsArray (idvalues) Then
sql = SQL & "SELECT [" &idfieldname& "] from [" &tablename& "] where" & Valuetosql (Tablename,idvalue s,1)
Else
sql = SQL & idvalues
End If
sql = SQL & ")"
Wdeleterecord = sql
End Function

Public Function Readtable (ByVal tablename,byval Condition,byval Getfieldnames)
On Error Resume Next
Dim rstgetvalue,sql,basecondition,arrtemp,arrstr,tempstr,i
TempStr = "": Arrstr = ""
' Give the SQL conditional statement
Basecondition = Valuetosql (tablename,condition,1)
' Read data
Set rstgetvalue = Server.CreateObject ("Adodb.recordset")
sql = ' Select ' &getfieldnames& ' from [' &tablename&] where ' &basecondition
Rstgetvalue.open sql,idbconn,3,3
If Rstgetvalue.recordcount > 0 Then
If InStr (Getfieldnames, ",") >0 Then
arrtemp = Split (Getfieldnames, ",")
For i = 0 To UBound (arrtemp)
If i<>0 then Arrstr = Arrstr &chr (112) &AMP;CHR (112) &AMP;CHR (113)
Arrstr = arrstr & Rstgetvalue.fields (i). Value
Next
TempStr = Split (ARRSTR,CHR (112) &AMP;CHR (112) &AMP;CHR (113))
Else
TempStr = Rstgetvalue.fields (0). Value
End If
End If
If Err.Number <> 0 Then
Idberr = Idberr & "Get data Error!" <br/> "
If Debug Then Idberr = idberr & "error message:" & Err.Description
Rstgetvalue.close ()
Set rstgetvalue = Nothing
Exit function
End If
Rstgetvalue.close ()
Set rstgetvalue = Nothing
Readtable = TempStr
End Function

Public Function C (ByVal objRS)
Objrs.close ()
Set objRS = Nothing
End Function

Private Function Valuetosql (ByVal tablename, ByVal ValueList, ByVal Stype)
Dim strtemp
strtemp = ValueList
If IsArray (valuelist) Then
strtemp = ""
Dim rstemp, Currentfield, CurrentValue, I
Set rstemp = Server.CreateObject ("Adodb.recordset")
With Rstemp
. ActiveConnection = Idbconn
. CursorType = 3
. LockType = 3
. Source = "SELECT * FROM [" & TableName &] WHERE 1 =-1 "
. Open
For i = 0 To UBound (valuelist)
Currentfield = Left (ValueList (i), InStr (ValueList (i), ":")-1)
CurrentValue = Mid (ValueList (i), InStr (ValueList (i), ":") +1)
If I <> 0 Then
Select Case Stype
Case 1
strtemp = strtemp & "and"
Case Else
strtemp = strtemp & ","
End Select
End If
If stype = 2 Then
strtemp = strtemp & "[" & Currentfield & "]"
Else
Select Case. Fields (Currentfield). Type
Case 7,133,134,135,8,129,200,201,202,203
If stype = 3 Then
strtemp = strtemp & "'" &currentvalue& ""
Else
strtemp = strtemp & "[" & Currentfield & "] = '" &currentvalue& "'"
End If
Case 11
If UCase (CStr (currentvalue)) = "true" Then
If stype = 3 Then
strtemp = strtemp & "1"
Else
strtemp = strtemp & "[" & Currentfield & "] = 1"
End If
Else
If stype = 3 Then
strtemp = strtemp & "0"
Else
strtemp = strtemp & "[" & Currentfield & "] = 0"
End If
End If
Case Else
If stype = 3 Then
strtemp = strtemp & CurrentValue
Else
strtemp = strtemp & "[" & Currentfield & "] =" & CurrentValue
End If
End Select
End If
Next
End With
If Err.Number <> 0 Then
Idberr = idberr & "Generate SQL statement error!" <br/> "
If Debug Then Idberr = idberr & "error message:" & Err.Description
Rstemp.close ()
Set rstemp = Nothing
Exit function
End If
Rstemp.close ()
Set rstemp = Nothing
End If
Valuetosql = strtemp
End Function

Private Function Doexecute (ByVal sql)
Dim executecmd
Set executecmd = Server.CreateObject ("Adodb.command")
With Executecmd
. ActiveConnection = Idbconn
. CommandText = sql
. Execute
End With
Set executecmd = Nothing
End Function
End Class
%>

Apply this class using the following code:
A database connection:

<%
Openconn () ' Open database connection
Dim Db:set db = New Dbctrl ' builds on image
' Your code here ...
Co (db): Closeconn () Release object, close database connection
%>

or (one or more database connections):

<%
Dim Db1:set db1 = New Dbctrl:db1.dbconn = OC (a)
Dim Db2:set DB2 = New Dbctrl:db2.dbconn = OC (b)
' Your code here ...
Co (DB1): Co (DB2)
%>

4. Examples of specific operations can refer to the code examples in each function description.

Methods and Properties Overview (detailed usage and examples below):

Reference:
Creatconn Build database connection string
OC Establish database connection
Co release to open default database connection like
Openconn
Closeconn Close default database connection
Dbctrl The. Dbconn property, gets the database connection to manipulate, the default value is Conn
Dbctrl.dberr property, read-only, output-captured error message
Dbctrl.version property, read-only, program version information
Dbctrl.autoid method , automatically gets the unique serial number
Dbctrl.getrecord method, obtains the qualifying record set
Dbctrl.getrecordbysql method, obtains the recordset according to the SQL statement
Dbctrl.getrecorddetail method to add a new record
Dbctrl.addrecord method, according to the detailed data of a specified record
Dbctrl.updaterecord method, which is more recent than the specified criteria
Dbctrl.deleterecord method, Deletes a qualifying record
Dbctrl.readtable method that gets the contents of other fields in a record according to the specified criteria
Dbctrl.c method, turn off the recordset pair like
Dbctrl.wgetrecord,
Dbctrl.waddrecord,
Dbctrl.wupdaterecord,
Dbctrl.wdeleterecord These 4 methods are SQL statements that obtain the appropriate action (preceded by W)