Welcome to my database test area. The purpose of this page is to let you play with a test database so that you can learn how to create the necessary Structured Query Language (SQL) commands in your own ASP code. Also included is an outline on the workings of the ADODB.Recordset object - an area which many folks find confusing. Hopefully I will be able to identify the pitfalls and show you the optimum method of achieving your aims.
An Overview of Database Manipulation and SQL.
In most cases, for personal web sites such as those hosted here on Brinkster, you will be dealing with a Microsoft Access database (version 97 or 2000). However, the information herein will largely apply to most other database types, e.g. SQL Server, MySQL, etc. The first thing we need to do when dealing with a database is create an instance of a database object (a technical way of saying "let's tell the ASP engine that we want to be able to connect to a database.") ...
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
Next we need to tell the connection object (conn) which database we wish to connect to and which driver type to use for database control ...
Dim connstr
'Using the Microsoft Access driver
connstr = "DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("/yourwebname/db/testdb.mdb") _
& ";PWD=" & pword & ";"
'Using the Microsoft JET 4.0 driver
connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
& Server.MapPath("/yourwebname/db/testdb.mdb") _
& ";Jet OLEDB:Database Password=" & pword
conn.Open connstr
The 'PWD=' or 'Jet OLEDB:Database Password=' bit at the end is optional - if you set up your Access database to require a password to allow it to be opened then you must include it in your connection string. (For databases holding sensitive information then this action is recommended - it isn't really that difficult for someone to discover the location and name of your database and download it directly. At least with password protection they won't easily be able to do anything with it).
Now that we have established a connection to the database we can start doing some manipulation. The most basic database operation (and the most common) is to Read information from it. This is done most simply using the SELECT statement as follows ...
Dim rs, strSQL, i
strSQL = "SELECT * FROM Table1"
Set rs = conn.Execute(strSQL)
Response.write("<table><tr>")
For i = 0 to rs.Fields.Count - 1
Response.write("<td>" & rs(i).Name & "</td>")
Next 'i
Response.write("</tr>")
Do while Not rs.eof
Response.write("<tr>")
For i = 0 to rs.Fields.Count - 1
Response.write("<td>" & rs(i) & "</td>")
Next 'i
Response.write("</tr>")
rs.MoveNext
Loop
Response.write("</table>")
Set rs = nothing
conn.close
Set conn = nothing
This will read all the information from the database table called 'Table1' into a Recordset with a variable name of 'rs'. (Click Here to see this script in action). A point to note regarding any database transaction which directly uses the 'Execute' method is that any recordset which is created will be classified as 'Forward Only'. This means that you can only use the 'MoveNext' method to step through the recordset - you cannot use any 'Sort' method, or 'MoveFirst' etc.
However, the above statement is not absolutely true - you can use a 'Sort' method for the recordset with the 'Execute' command, but you must include the sort (actually called 'ORDER BY') as part of the 'strSQL' command. To do this, all we do is add the 'ORDER BY' condition to the strSQL command as such ...
strSQL = "SELECT * FROM Table1 ORDER BY FirstName"
(Click Here to see this in action). But now we have the hang of this, you may not wish to retrieve Everything - you may wish only the ID references and First Names. We do this by specifying the database fields we want to retrieve instead of using '*', as shown below. We can also sort the records in reverse order by adding 'DESC' at the end. (Click Here to see this in action).
strSQL = "SELECT ID, FirstName FROM Table1 ORDER BY FirstName DESC"
So now we can retrieve information, either all or targeted fields, and we can sort the order of the results. However we don't always want all of the records from the database table - we sometimes only want to retrieve a selection of records depending on certain criteria. We do this by including the 'WHERE' clause in our 'strSQL' statement, as shown below (Click Here to see this in action) ...
strSQL = "SELECT FirstName, LastName FROM Table1 WHERE FirstName = 'Ian'"
OK. Now that we've mastered reading from the database, the next step is to add records to the database. We will discuss this in Part 2 >>>
|
