|
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
%>
|
