| ADODB Overview
By combining ActiveX® Data Objects with Active
Server Pages technology, connecting to a database within your Burton Networks hosted web site
can be achieved rather easily. The following progressive examples
are provided for your customization where Microsoft.Jet.OLEDB.4.0 is the current OLEDB
driver, domain.com is your registered
domain name, fpdb is your ODBC directory
name, and database.mdb is the database
file in question:
Connection Object
With performance gains in excess of 50% than
standard DSN connection types, connecting to your database at the
OLE Layer ensures your pages are assembled faster, conserves
processor bandwidth, and is available FREE of charge. Taking advantage of
these benefits, the following code is an example of how to create,
open, close and destroy the connection object: <%
Dim
obj_CONNECTION
Set obj_CONNECTION
= Server.CreateObject("ADODB.Connection")
obj_CONNECTION.Provider = "Microsoft.Jet.OLEDB.4.0"
obj_CONNECTION.ConnectionString =
"C:\Inetpub\wwwroot\domain.com\fpdb\database.mdb"
obj_CONNECTION.Open
'...
'...
'...
obj_CONNECTION.Close
Set obj_CONNECTION = Nothing
%>
Recordset Object
Once a connection to your database is open, use
the recordset object to add, edit, delete, or view records in the
database. Assuming the database has a table named tbl_STATES, the following code is an example of
how to create, open, close, and destroy the recordset
object: <%
Dim obj_CONNECTION
Set obj_CONNECTION =
Server.CreateObject("ADODB.Connection")
obj_CONNECTION.Provider = "Microsoft.Jet.OLEDB.4.0"
obj_CONNECTION.ConnectionString =
"C:\Inetpub\wwwroot\domain.com\fpdb\database.mdb"
obj_CONNECTION.Open
Dim obj_RECORDSET
Set obj_RECORDSET =
Server.CreateObject("ADODB.Recordset")
obj_RECORDSET.Source = "SELECT * FROM tbl_STATES"
obj_RECORDSET.ActiveConnection = obj_CONNECTION
'To add, edit
and delete records...
obj_RECORDSET.CursorType
= 1
obj_RECORDSET.LockType =
3
'To
view records...
obj_RECORDSET.CursorType
= 0
obj_RECORDSET.LockType =
1
obj_RECORDSET.Open
'...
'...
'...
obj_RECORDSET.Close
Set obj_RECORDSET = Nothing
obj_CONNECTION.Close
Set obj_CONNECTION = Nothing
%>
Add
Records
Suppose we wanted to add a new record to the
tbl_STATES table with the str_STATE field having a value of Michigan, the following code illustrates one
way to do such: <%
Dim obj_CONNECTION
Set obj_CONNECTION =
Server.CreateObject("ADODB.Connection")
obj_CONNECTION.Provider = "Microsoft.Jet.OLEDB.4.0"
obj_CONNECTION.ConnectionString =
"C:\Inetpub\wwwroot\domain.com\fpdb\database.mdb"
obj_CONNECTION.Open
Dim obj_RECORDSET
Set obj_RECORDSET =
Server.CreateObject("ADODB.Recordset")
obj_RECORDSET.Source = "SELECT * FROM tbl_STATES"
obj_RECORDSET.ActiveConnection = obj_CONNECTION
obj_RECORDSET.CursorType = 1
obj_RECORDSET.LockType = 3
obj_RECORDSET.Open
obj_RECORDSET.AddNew
obj_RECORDSET.Fields("str_STATE").Value = "Michigan"
obj_RECORDSET.Update
obj_RECORDSET.Close
Set obj_RECORDSET = Nothing
obj_CONNECTION.Close
Set obj_CONNECTION = Nothing
%>
Edit
Records
Suppose we wanted to edit all records in the
tbl_STATES table with the str_STATE field having a value of Michigan, the following code illustrates one
way to do such: <%
Dim obj_CONNECTION
Set obj_CONNECTION =
Server.CreateObject("ADODB.Connection")
obj_CONNECTION.Provider = "Microsoft.Jet.OLEDB.4.0"
obj_CONNECTION.ConnectionString =
"C:\Inetpub\wwwroot\domain.com\fpdb\database.mdb"
obj_CONNECTION.Open
Dim obj_RECORDSET
Set obj_RECORDSET =
Server.CreateObject("ADODB.Recordset")
obj_RECORDSET.Source = "SELECT * FROM tbl_STATES WHERE str_STATE = 'Michigan'"
obj_RECORDSET.ActiveConnection = obj_CONNECTION
obj_RECORDSET.CursorType = 1
obj_RECORDSET.LockType = 3
obj_RECORDSET.Open
While Not obj_RECORDSET.EOF
obj_RECORDSET.Fields("str_STATE").Value = "Michigan"
obj_RECORDSET.Update
obj_RECORDSET.MoveNext
Wend
obj_RECORDSET.Close
Set obj_RECORDSET = Nothing
Set obj_CONNECTION = Nothing
%>
Delete Records
Suppose we wanted to delete all records in the
tbl_STATES table with the str_STATE field having a value of Michigan, the following code illustrates one
way to do such: <%
Dim obj_CONNECTION
Set obj_CONNECTION =
Server.CreateObject("ADODB.Connection")
obj_CONNECTION.Provider = "Microsoft.Jet.OLEDB.4.0"
obj_CONNECTION.ConnectionString =
"C:\Inetpub\wwwroot\domain.com\fpdb\database.mdb"
obj_CONNECTION.Open
Dim obj_RECORDSET
Set obj_RECORDSET =
Server.CreateObject("ADODB.Recordset")
obj_RECORDSET.Source = "SELECT * FROM tbl_STATES WHERE str_STATE = 'Michigan'"
obj_RECORDSET.ActiveConnection = obj_CONNECTION
obj_RECORDSET.CursorType = 1
obj_RECORDSET.LockType = 3
obj_RECORDSET.Open
While Not obj_RECORDSET.EOF
obj_RECORDSET.Delete
obj_RECORDSET.Update
obj_RECORDSET.MoveNext
Wend
obj_RECORDSET.Close
Set obj_RECORDSET = Nothing
obj_CONNECTION.Close
Set obj_CONNECTION = Nothing
%>
View
Records
Suppose we wanted to view all records in the
tbl_STATES table with the str_STATE field having a value beginning with
M, the following code illustrates one way
to do such: <%
Dim obj_CONNECTION
Set obj_CONNECTION =
Server.CreateObject("ADODB.Connection")
obj_CONNECTION.Provider = "Microsoft.Jet.OLEDB.4.0"
obj_CONNECTION.ConnectionString =
"C:\Inetpub\wwwroot\domain.com\fpdb\database.mdb"
obj_CONNECTION.Open
Dim obj_RECORDSET
Set obj_RECORDSET =
Server.CreateObject("ADODB.Recordset")
obj_RECORDSET.Source = "SELECT * FROM tbl_STATES WHERE str_STATE LIKE 'M%'"
obj_RECORDSET.ActiveConnection = obj_CONNECTION
obj_RECORDSET.CursorType = 0
obj_RECORDSET.LockType = 1
obj_RECORDSET.Open
While Not obj_RECORDSET.EOF
Repsonse.Write "<p>" &
obj_RECORDSET.Fields("str_STATE").Value
& "</p>"
obj_RECORDSET.MoveNext
Wend
obj_RECORDSET.Close
Set obj_RECORDSET = Nothing
obj_CONNECTION.Close
Set obj_CONNECTION = Nothing
%>
| 
