lecture note 10: simple database techniques. introduction –database system –access, sql server...

Lecture Note 10:Lecture Note 10: Simple Database Simple Database

TechniquesTechniques

IntroductionIntroduction

– Database System –Access, SQL Server and others.

– Microsoft Access - Interacting with this databases.

– ADO and the Connection and Recordset objects.

What's ADO?What's ADO?

• ADO (ActiveX Data Objects) is a collection of objects created by Microsoft that interact with databases. There's the Connection Object, which connects user to the databases and the Recordset Object which allows user to retrieve data from our databases.

Creating a DatabaseCreating a Database

• Create a database --- Using MS Office Access.

The Connection ObjectThe Connection Object

• Connection Object methods and properties: Mode, Provider, ConnectionString and Open.

ExampleExample

•  <% Dim DB, RS Set DB = Server.CreateObject("adoDB.connection") DB.Mode = adModeReadWrite DB.Provider = "MICROSOFT.JET.OLEDB.4.0" DB.ConnectionString = Server.MapPath("db1.mdb") DB.Open %>

ExampleExample

• Line1: The adovbs.inc is a large file of constants, which are variables except their values can't be changed. For instance, in the adovbs.inc file, the constant adOpenKeyset = 1. So when we use adOpenKeyset in our cursor parameter, we are actually using 1 in our cursor parameter. Why is this? Because that's the real value of our cursor parameter. Microsoft just made it easier for us, so instead of having to remember numbers, we just need to remember those constants. You will see this in action in our next example.

• Line 3: the code creates an instance of the Connection Object and names it DB. • Line 4: the code uses the property mode of the Connection Object and sets

it to read/write which not only allows us to read from the database, but the change or add to the database.

• Line 6: the code uses the connectionstring property of the Connection Object which locates our database.

• Line 7: the code uses the Open() method of the Connection Object to open the database.

Adding Data Using the Recordset ObjectAdding Data Using the Recordset Object

• The Recordset Object allows user to read/write/update/delete data from our database.

• EXAMPLE: Methods and Properties: Open, AddNew, Update and Close.

Adding Data Using the Recordset ObjectAdding Data Using the Recordset Object

• <%

Set RS = Server.CreateObject("adoDB.recordset") RS.Open "table1", DB, adOpenKeySet,adLockOptimistic,adCmdTable RS.AddNew RS("Name") = "Mary“ RS.UpDate RS.AddNew RS("name") = "John“ RS.Update RS.AddNew RS("name") = "Irene“ RS.Update

%>

ExampleExample• Line 2: like the previous example creates an instance of the Recordset Object and

names it RS. So now we have a variable named RS which is a copy of the Recordset Object.

• Line 3: opens the database using the following Recordset Object parameters: DB, adOpenStatic, adLockOptimistic, adCmdTable.

• DB is the ActiveConnection parameter. Use this Connection Object to connect the database.

• AdOpenStatic constant is the CurosorLocation parameter. It describes what cursor type we would like to use. A cursor is used to navigate through records. Forward, backwards, etc. Also, some cursors are non-updateable so we must use a cursor that supports updating a database. The adOpenKeySet allows this.

• AdLockOptimistic constant is the LockType parameter which allows us to add or update data to our database. With the AdLockOptimistic constant in the LockType parameter, the database only locks when the database is being updated. This way no one else can enter data until the data is finished updating. If you want to be on the safe side, you could use the AdLockPessimistic constant in the LockType parameter which locks the database as soon as the Update() method of the RecordSet Object is called.

• AdCmdTable constant is the Options parameter which states how our source string ("table1") should be interpreted. We use the AdCmdTable constant as our Options parameter because we only need to enter data to our table and in our source string, we only add our table name. If we used a source string like ("SELECT * FROM table1 WHERE NAME = 'Andrew Schools' "), we would have to use the AdCmdText constant in the Options parameter because we actually have a string of SQL that needs to be interpreted. More on SQL (Structured Query Language) and using the AdCmdText in the Options parameter later.

Retrieving Data Using the Recordset ObjectRetrieving Data Using the Recordset Object

• EXAMPLEMethods and Properties: Open, BOF (Beginning of File), EOF (End of File), MoveFirst and MoveNext.

<%<% Set RS = Server.CreateObject("adoDB.recordset") Set RS = Server.CreateObject("adoDB.recordset") RS.Open "table1", DB, adOpenKeySet, adLockReadOnly, RS.Open "table1", DB, adOpenKeySet, adLockReadOnly, adCmdTableadCmdTable If RS.BOF and RS.EOF Then If RS.BOF and RS.EOF Then Response.Write "Sorry, no files!" Response.Write "Sorry, no files!" Else Else RS.MoveFirst RS.MoveFirst While Not RS.EOF While Not RS.EOF Response.Write RS("name")& " " Response.Write RS("name")& " " RS.MoveNext RS.MoveNext wEnd wEnd End If End If%>%>

• Line 2: use a AdLockReadOnly constant instead of a AdLockOptimistic constant since we are not updating the database.

• Line 3: the code checks to see if the database is empty by using the BOF and EOF properties of the Recordset Object.

• Line 6: tells the cursor to move to the Beginning of the file just in case it isn't there.

• Line 7 starts a While...Loop. It will loop until there is no more data in our database.

• Line 8 writes the data to the screen.

• Line 9 tells the cursor to move to the next file in the database.

• Line 10 ends the loop.

• Line 11 ends the If...EndIf statement.

Now since we are opening all of these objects, it's good practice to Now since we are opening all of these objects, it's good practice to close them once we are finished with them. This also saves precious close them once we are finished with them. This also saves precious server memory.server memory.

<%<% RS.Close RS.Close Set RS = Nothing Set RS = Nothing DB.Close DB.Close Set DB = Nothing Set DB = Nothing%>%>

Deleting Data Using the Recordset ObjectDeleting Data Using the Recordset Object

• Example

<% Set RS = Server.CreateObject("adoDB.recordset") RS.Open "SELECT * FROM table1 WHERE name='Mary' ", DB, adOpenStatic, adLockOptimistic, adCmdText If RS.BOF and RS.EOF Then Response.Write "Sorry, no files!“ Else RS.Delete End If RS.Close Set RS = Nothing DB.Close Set DB = Nothing%>

• Line 2 : opens the Recordset Object as shown in previous examples, however, the source string is different. It uses a SELECT statement of SQL which states: find all files with the name = Mary.

• What's with the “ * “? It means All fields. In this statement, you could replace the * with name, since we only have one field we can select from anyway, which is name.

• Once we have found the file(s) with the name(s) = Mary, line 6 deletes the first file using the Delete() method of the RecordSet Object

Deleting Data Using the Recordset ObjectDeleting Data Using the Recordset Object

• Example

<%Dim RSSet RS = Server.CreateObject("adoDB.recordset")RS.Open "SELECT * FROM table1 WHERE name='Kelly' ", DB, adOpenStatic, adLockOptimistic, adCmdTextIf RS.BOF and RS.EOF ThenResponse.Write "Sorry, no files!“ElseRS.MoveFirstWhile Not RS.EOFRS.DeleteRS.MoveNextwEndEnd IfRS.CloseSet RS = NothingDB.CloseSet DB = Nothing%>

Updating Data Using the Recordset ObjectUpdating Data Using the Recordset Object

• Updating data is another very important technique when working with databases and it's almost like adding data to a database.

• Example

<%Set RS = Server.CreateObject("adoDB.recordset")

RS.Open "SELECT * FROM table1 WHERE name='Mary' ", DB, adOpenKeySet,adLockOptimistic,adCmdTextIf RS.BOF and RS.EOF ThenResponse.Write "Sorry, no files!“Else

RS("Name") = "Mary has a little lamb!"

RS.UpDateEnd IfRS.CloseSet RS = NothingDB.CloseSet DB = Nothing%>


• The only thing that's different from adding data then updating data is the lack of the AddNew() method of the Recordset Object and of course we use a SELECT statement of SQL to obtain the file we

wish to update.

lecture note 10: simple database techniques. introduction –database system –access, sql server...

Documents