ASP - ASP database categoryASP,database | Programing Reference For .Net,Java,C

ASP database category

ASP database category

1, the preamble referred ASP database operation, the majority of people will think: sharing the connection string ConStr, Conn.Open ConStr the establishment of a database connection, Conn.Execute SqlCmd implementation of orders, RecordSet.Open Sql, Conn, a record set 1,1 indeed this method was 99% of the people or companies use. The database generated during operation of the mistakes, I am afraid that 99 percent of the processing will be carried out, either in the process of beginning accession on error resume next "easily" jump the past, or that the erroneous information together with the error code "body violence" in the browser front . The first caseèŽ«æ˜Žå…¶å¦™people may have the strange result, the latter may be in a certain time (for example, when connecting to the database) you expose sensitive information affecting security of the Web site. Of course, there are still individual programmers responsible comrades will be prone to errors behind the operation if err.xxxx accession to deal with possible mistakes, but it does not seem to be a good approach, fell into possible missing.
I have no wish to understand why in the VB and ASP.NET exist in the On Error Goto, just in ASP was cancelled.
In addition to mentioning is that, when you use in front on error resume next in the back do not want to resume next, I am sorry, no way, you can only put it "to implement in the end." Look at the other languages abnormal mechanism, try .. finally catch .. arbitrary, really cool cool!

Having said that, ASP is not for the abnormal mechanisms such as the introduction of new content, after all, ASP language itself has decided it is impossible to achieve (in ASP.NET realized), but would like to ASP in the most common and most easily There is a mistake in the database operation, to find an effective way of dealing with mistakes, and the conn, RecordSet packages, maximizing the streamlining. Thus they were below the database category.

Second, the database category
1, functions, as mentioned earlier, the purpose of this class is to ADODB.Connection, such as cumbersome operation Adodb.Recordset package together and realize category error handling. Now look at the category of members, properties and methods:
1): (No members of the public or the protection)
2) Attributes:
ClassName-return type of Version-return-to return to the final version LastError mistakes IgnoreError-set / neglect to return to the database to return to the wrong Connection-connect object (ADODB.Connection)
ConnectionString-set / Back to connect string (this example for SQL Server, such as other accordance with the actual settings)
FieldCount, PageSize, PageCount, AbsolutePage, AbsolutePosition, Bof, Eof-please refer to the corresponding content Adodb.Recordset 3):
Setup-connected set of data server account, password, database name, host / IP
Connect-Close-Close connecting to the database and the database connection Query-release resources for the implementation of the database and return to order data sets ExeSQL-implementation of SQL commands (not to return database)
FieldName returned to the designated serial number-the name of Fields to return to the designated-(serial number or field) Data-field value Ditto MoveNext, MovePrevious, MoveFirst, MoveLast-please refer to the corresponding content Adodb.Recordset

2, the realization of the code (DBSql.inc.asp)
Content is too long, here on / folding … <%
'================================================= ======================
'CLASS NAME: clsDB
'DESIGN BY: Bangguohui
'DATE: 2003-12-18
'SITE: http://kacarton.yeah.net/
'EMAIL: kacarton@sohu.com
'MODIFY:
'2004-6-25: upgrading the data engine, returned error code is less than 0 (it can also be a value of the ASP
'There is a change in the definition), is amended error detection err.number> 0 ==> err.number <> 0
'2004-6-30: Laws error handling, such as ignoring the cursor types, and other non-error nature of change tips
'================================================= ======================

Class clsDB

'Name of this class
'Var string
'@ Access Private
'@ See property: Name
Private m_strName

'Version of this class
'Var string
'@ Access Private
'@ See property: Version
Private m_strVersion

'Error Object
'@ Var ADODB.Connection.Errors
'@ Access private
'@ See property: LastError
Private m_LastError

'Ingore all Connection.Errors
'Var Boolean
'@ Access private
'@ See property: IgnoreError
Private m_IgnoreError

'Connection Object
'Var ADODB.Connection
'@ Access Private
'@ See property: Connection
Private m_Connection

'Is connection to database?
'Var boolean
'@ Private
Private m_bIsConnect

'RecordSet
'Var RecordSet
'@ Access Private
Private m_RecordSet

'Connection string
'Var string
'@ Access Private
'@ See property: ConnectionString
Private m_ConneStr

'Database server host name or IP
'Var string
'@ Access Private
'@ See property: Host
Private m_strHost

'Database name
'Var string
'@ Access Private
'@ See property: Database
Private m_strDatabase

'Account to connection database
'Var string
'@ Access Private
'@ See property: UserName
Private m_UserName

'Password to connection database
'Var string
'@ Access Private
'@ See property: Password
Private m_Password

'Get class name attribute.
'Usage: oTemplate.Name
'Access public
Public Property Get ClassName ()
ClassName = m_strName
End Property

'Get class version attribute.
'Usage: oTemplate.Version
'Access public
Public Property Get Version ()
Version = m_strVersion
End Property

'Get class last error messages.
'Usage: oTemplate.LastError
'@ Access public
Public Property Get LastError ()
LastError = m_LastError
End Property

'Get or Set Ignore connection.errors
Public Property Get IgnoreError ()
IgnoreError = m_IgnoreError
End Property

Public Property Let IgnoreError (ByVal Value)
M_IgnoreError = Value
End Property

'Get Connection
Public Property Get Connection ()
Connection = m_Connection
End Property

'Get connection string
Public Property Get ConnectionString ()
ConnectionString = m_ConneStr
End Property

'Set connection string
Public Property Let ConnectionString (ByVal Value)
M_ConneStr = Value
End Property

'Get data fields count
Public Property Get FieldCount ()
FieldCount = m_RecordSet.Fields.Count
End Property

'Get RecordSet PageSize
Public Property Get PageSize ()
On error resume next
PageSize = m_RecordSet.PageSize
If err.number <> 0 then ShowError ( "Can not get PageSize!")
End Property

'Set RecordSet Page Size
Public Property Let PageSize (ByVal Value)
On error resume next
M_RecordSet.PageSize = Value
If err.number <> 0 then ShowError ( "Can not set PageSize to" & Value)
End Property

'Get RecordSet page count
Public Property Get PageCount ()
PageCount = m_RecordSet.PageCount
End Property

'Get RecordSet record count
Public Property Get RecordCount ()
On error resume next
RecordCount = m_RecordSet.RecordCount
If err.number <> 0 then ShowError ( "Get RecordCount error.")
End Property

'Get RecordSet Absolute Page
Public Property Get AbsolutePage ()
On error resume next
AbsolutePage = m_RecordSet.AbsolutePage
If err.number <> 0 then ShowError ( "Can not get AbsolutePage!")
End Property

'Set RecordSet Absolute Page
Public Property Let AbsolutePage (ByVal Value)
On error resume next
M_RecordSet.AbsolutePage = Value
If err.number <> 0 then ShowError ( "Can not set AbsolutePage to" & Value)
End Property

'Get RecordSet Absolute Position
Public Property Get AbsolutePosition ()
On error resume next
AbsolutePosition = m_RecordSet.AbsolutePosition
If err.number <> 0 then ShowError ( "Can not get AbsolutePosition!")
End Property

'Set RecordSet Absolute Position
Public Property Let AbsolutePosition (ByVal Value)
On error resume next
M_RecordSet.AbsolutePosition = Value
If err.number <> 0 then ShowError ( "Can not set AbsolutePosition to" & Value)
End Property

'Bof
Public Property Get Bof ()
Bof = m_RecordSet.Bof
End Property

'Eof
Public Property Get Eof ()
Eof = m_RecordSet.EOF
End Property

'Setup the databease host name, database name, User name (account), the password
Public Sub Setup (Account and Password, and Database, Host)
M_UserName = Account
M_Password = Password
If Database <> "" then m_strDatabase = Database
If Host <> "" then m_strHost = Host
M_ConneStr = "SQL Server Driver = (); Server =" & & m_strHost "Database =" & _
M_strDatabase & "; Uid =" & & m_UserName "Pwd =" & m_Password & ";"
End Sub

'Connect to database
Public Function Connect ()
On error resume next
M_Connection.Open m_ConneStr
If err.number <> 0 Then ShowError ( "database connection errors: (Server:" & & m_strHost "Database:" m_strDatabase & & ")")
M_bIsConnect = true
Connect = true 'todo: / /
End Function

'Diconnect database
Public Function Close ()
On error resume next
Set m_RecordSet = Nothing
Set m_Connection = Nothing
M_bIsConnect = false
Close = true
If err.number <> 0 then ShowError ( "cut Database Connection error")
End Function

'Query
Public Sub Query (SQLCommand)
On error resume Next
If not m_bIsConnect then Connect
Set m_RecordSet = Server.CreateObject ( "Adodb.Recordset")
'Set m_RecordSet = m_Connection.Execute (SQLCommand)
M_RecordSet.Open SQLCommand, m_Connection, 1, 1
If err.number <> 0 then ShowError (SQLCommand): exit sub
If m_Connection.Errors.Count> = 0 And m_IgnoreError false then ProcessError (SQLCommand)
End Sub

'ExeSQL Command
Public Sub ExeSQL (SQLCommand)
On error resume Next
If not m_bIsConnect then Connect
M_Connection.Execute SQLCommand
If err.number <> 0 then ShowError (SQLCommand): exit sub
If m_Connection.Errors.Count> = 0 And m_IgnoreError false then ProcessError (SQLCommand)
End Sub

'Get Fields Name
Public Function FieldName (ByVal FieldId)
On error resume next
FieldName = m_RecordSet.Fields (FieldId). Name
If err.number <> 0 then ShowError ( "can not read the column" FieldID & & "names!")
End Function

'Get fields data
Public Function Fields (ByVal FieldId)
On error resume next
Fields = m_RecordSet.Fields (FieldId)
If err.number <> 0 then ShowError ( "can not read the column" & & FieldID "data!")
End Function

'Get fields data
Public Function Data (ByVal FieldId)
On error resume next
Data = m_RecordSet.Fields (FieldId)
If err.number <> 0 then ShowError ( "can not read" FieldID & & "Data!")
'If m_Connection.Errors.Count> 0 then ShowError ( "can not read" FieldID & & "Data!")
End Function

'Move to next record
Public Sub MoveNext ()
On error resume next
If m_bIsConnect then m_RecordSet.MoveNext
If err.number <> 0 then ShowError ( "MoveNext error")
End Sub

'Move to Previous record
Public Sub MovePrevious ()
On error resume next
If m_bIsConnect then m_RecordSet.MovePrevious
If err.number <> 0 then ShowError ( "MovePrevious error")
End Sub

'Move to First record
Public Sub MoveFirst ()
On error resume next
If m_bIsConnect then m_RecordSet.MoveFirst
If err.number <> 0 then ShowError ( "MoveFirst error")
End Sub

'Move to Last record
Public Sub MoveLast ()
On error resume next
If m_bIsConnect then m_RecordSet.MoveLast
If err.number <> 0 then ShowError ( "MoveLast error")
End Sub

'2004-6-30
Private Sub ProcessError (ByVal sqltxt)
For i = 0 to m_Connection.Errors.Count-1
If m_Connection.Errors.Item (i). Number <> 0 Then ShowError (sqltxt)
Next
End Sub

'This function is called whenever an error occurs and will handle the error
'Additionally the error message will be saved in m_strLastError.
'@ Param $ msg a string containing an error message
'@ Access private
'@ Return void
Private Sub ShowError (ByVal sqltxt)
For i = 0 to m_Connection.Errors.Count-1
Response.Write m_Connection.Errors.Item (i) & "(" & m_Connection.Errors.Item (i). Number & ") <br>"
Next
M_LastError = Err.Description

M_Connection.Errors.Clear
Response.Write "<br> ——————————————- ———–< br> "& _
"<font Color=red size=4>" & & sqltxt "</ font>"
'Response.Write "<br> —————————————— ————< br> "& _
' "<font Color=red size=4>" & Left (sqltxt, 10) & "…( erroneous information has been shielding), please contact the webmaster! </ Font> "
'Response.End
End Sub

'Class constructor, set class default attributes, you can change it
Private Sub class_Initialize
M_strName = "clsDB"
M_strVersion = "1.0"
Set m_Connection = Server.CreateObject ( "ADODB.Connection")
'Please here for you to amend the default value of connecting to the database
Setup "sa", "password", "Northwind", "(local)"
M_bIsConnect = False
M_IgnoreError = False
End Sub

'Class destructor, free memory.
Private Sub class_Terminate
Set m_RecordSet = Nothing
Set m_Connection = Nothing
End Sub

End Class

3, the use of examples
<!–# INCLUDE file = "DBSql.inc.asp" ->
<%
Function HTMLEncode (str)
If IsNull (str) Then HTMLEncode = "(NULL)" _
Else HTMLEncode = Server.HTMLEncode (str)
End Function

Dim sql, i
Set sql = New clsDB
Sql.Connect
Sql.ExeSQL ( "update Customers set Address = 'People's Republic of China' where ID = 1")
Sql.Query ( "select * from Customers")
Response.Write "<table border=1> <tr>"
For i = 0 To sql.FieldCount-1
Response.Write "<td>" & Server.HTMLEncode (sql.FieldName (i)) & "</ td>"
Next
Response.Write "</ tr>"
While Not sql.Eof
For i = 0 To sql.FieldCount-1
Response.Write "<td>" & HTMLEncode (sql.Data (i)) & "</ td>" 'here can be directly used to replace the name of i
Next
Response.Write "</ tr>"
Sql.MoveNext
Wend
Response.Write "</ table>"
Sql.Close
Set sql = Nothing
%>

Third, this summary is only a rough comparison of the database, there are many features not add ADODB, and the lack of flexibility. This paper aims to provide you with another kind of thinking, you feel that after reading this article or it is the harvest, I am very satisfied.
In addition, my next article "ASP package for paging up" will use this class.

Bookmark it: These icons link to social bookmarking sites where readers can share and discover new web pages.