DBConnect.asp

<%

the example that
 ' shows how to access a database with ASP. This is a give or take 9 step way to get
 ' information from a table in a database. It will never get easier than this.


 ' Before we start, let's get some basic stuff out of the way.


 ' First we need to declare some constants that are needed to communicate to
 ' the database. These are by no means the only ones you could use. If you want
 ' the entire list of ADO constants for vbscript, you can get it here:
 ' http://www.aspEmporium.com/aspEmporium/inc/adovbs.inc

 ' I chose some constants that will allow us to access the recordcount
 ' property to get an exact count of records. You should use a static or keyset cursor
 ' to get a record count. Forward only and dynamic cursors will most
 ' likely return - 1 with the recordcount property. I chose a static cursor: "adOpenStatic"

 ' then I chose the ReadOnly lock type since we aren't going to modify any
 ' of these records.

CONST adOpenStatic = 3, adLockReadOnly = 1, adCmdText = &H0001


 ' These are the variables we will be using in our code.
 ' Variables don't have to be declared unless you call the OPTION EXPLICIT command
 ' Declaring option explicit is usually a good idea and is rumored to increase the
 ' execution speed of ASP code.
 ' I will not be using option explicit for this example so variable declaration 
 ' is strictly optional but I have declared everything below anyway.

Dim CONN_STRING, CONN_USER, CONN_PASS		' database connection string
Dim myRs		' ADODB.Recordset Object
Dim myConn		' ADODB.Connection Object
Dim strSQL		' SQL string
Dim i			' standard looping variable
Dim c1			' holds the current color of our table fields (used for html display)


 ' 1.) The connection string:
 '     The connection string usually contains the data 
 '     source you are connecting to and the method of 
 '     connection.

 ' valid connection string examples:

 ' ODBC with an access database:
 ' 	CONN_STRING = "DBQ=C:\database.mdb;Driver={Microsoft Access Driver (.mdb)};"

 ' ODBC using a system DSN:
 ' 	CONN_STRING = "DSN=system_dsn_name;"

 ' OLEDB with an access database:
 ' 	CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database.mdb;"

 ' ODBC with SQL Server:
 ' 	CONN_STRING = "driver={SQL Server};server=db_server_name;uid=user_name;pwd=password;database=database_name;"

 ' OLEDB with SQL Server
 ' 	CONN_STRING = "Provider=SQLOLEDB;Data Source=db_server_name;Initial Catalog=database_name;"
 ' 	CONN_USER = "user_ID"
 ' 	CONN_PASS = "user_password"


 ' My conn string looks exactly like this:
CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("/aspEmporium/myData.mdb") & ";"

 ' But since I have declared the exact same conn string in the global.asa
 ' as an application variable, I will use that one instead for consistency.
 ' This next variable overwrites the previous conn string with whatever is 
 ' in the global.asa in the application variable dbConn. You can comment
 ' this next line out:

CONN_STRING = Application("dbConn")
CONN_USER =  Application("dbUsr")
CONN_PASS =  Application("dbPass")

 ' Now we have a conn string, so let's use it to open the specified database



 ' 2.) Call the necessary components that establish a connection and recordset
 '     to the db specified in the conn string. Here we use the createobject method
 '     of the server object, one of the six built in objects of ASP.

Set myConn = Server.CreateObject("ADODB.Connection")
Set myRs = Server.CreateObject("ADODB.Recordset")



 ' 3.) Open the database by calling the open method of the ADODB.Connection object.
 '     Right here is where we use our conn string that has been conviently stored in a variable
 '     called CONN_STRING

myConn.Open CONN_STRING, CONN_USER, CONN_PASS

 ' Now we're in the database! There's still a couple of steps to go before we can actually take useful
 ' data from a table in the db though. 



 ' 4.) Before we can go further, we need an SQL statement. SQL stands for Structured Query Language and
 '     is the way you will almost always talk to the database to tell it what to do. Let's make an
 '     easy sql statement and save it in the variable strSQL:

strSQL = "SELECT exampleID, exampleName, exampleDesc, examplePathName, " & _
	 "exampleLanguage, firstCreatedDate, lastModifiedDate, isNew, isUpdated " & _
	 "FROM examples WHERE exampleLanguage = 'jscript' ORDER BY IsNew DESC, " & _
	 "IsUpdated DESC, ExampleName;"

 ' sql is a language in itself but it's easy enough if you practice.
 ' Other valid sql statements:
 ' 	select examples with more than 5000 clicks:
 '		strSQL = "SELECT * FROM examples WHERE clicks > 5000;"
 '	select examples containing the word database in the field exampleDesc:
 '		strSQL = "SELECT * FROM examples WHERE exampleDesc LIKE '%database%';"
 '	select examples 4 through 7:
 '		strSQL = "SELECT * FROM examples WHERE exampleID BETWEEN 3 AND 8;"
 '	select examples that have no value in the exampleVotes field:
 '		strSQL = "SELECT * FROM examples WHERE exampleVotes IS NULL;"



 ' 5.) Our sql statement is in place and our db connection is open so now it's time to open the
 '     recordset object. The recordset contains database records that are gathered based on the above sql
 '     statement. When we open the recordset, we will be taking every column and record from the 
 '     examples table where the exampleLanguage field is equal to 'jscript'. 
 '     When we open the recordset, we need to reference the sql statement (strSQL), 
 '     the open adodb.connection (myConn) and a few ado constants, which we declared at the beginning
 '     of this example.

myRs.Open strSQL, myConn, adOpenStatic, adLockReadOnly, adCmdText


 ' Now the recordset object has been populated with data from our database!
 ' let's find out how many records fit our sql criteria by calling the
 ' recordcount property of the recordset:

response.write "<p>" & myRs.RecordCount & " records found!</p>" & vbCrLf

 ' if we use the wrong type of ado cursor, the 
 ' recordcount property will return -1, which is useless.
 ' In this case, we will get a valid number representing the
 ' total number of records returned.



 ' 6.) Now we must display the results with some kind of html layout.
 '     Let's make a table for the results:

response.write "<table width=""100%"" cellpadding=3 cellspacing=2 border=1 "
response.write "style=""font-family:helvetica, MS Sans Serif;font-size:10pt;"">" & vbCrLf
response.write vbTab & "<tr>" & vbCrLf



 ' 7.) Use the count property of the Fields collection to make a simple For Next Loop
 '     that displays field names. The field names come from the Name property of the
 '     fields collection. Some other properties you can get from the fields collection
 '     include Type to get the datatype of a field or Size to get the char size of a field.

c1 = "#FFFFFF"
For i = 0 to myRs.Fields.Count -1     ' the -1 is here because we started at 0
	response.write vbTab & vbTab & "<th bgcolor=""" & c1 & """>" & myRs.Fields(i).Name & "</th>" & vbCrLf
	 ' let's change the color for each field of the table. Don't worry,
	 ' it will look pretty cool when we're done and it's better than doing nothing.
	if c1 = "#FFFFFF" then c1 = "#DDDDDD" else c1 = "#FFFFFF"
NEXT

response.write vbTab & "</tr>" & vbCrLf

 ' now we can get the actual data but first, move to the 1st record
 ' by calling the MoveFirst method of the recordset object.
 ' A lot of databases will automatically move to the first record but
 ' some don't and it's usually better to take nothing for granted.

 ' but before we do that, let's check for no records being returned. In the event
 ' of no records fitting our sql criteria, a special boolean value is set to true.
 ' Actually 2 are but we only need to look for one called BOF (explained below 
 ' in more detail - step 8):

If myRs.BOF then 

 ' the above line could also be written as - If myRs.BOF = True Then

	response.write "<td colspan=""" & myRs.Fields.Count & """>No Records Found!</td>"

Else

	 ' move to the first record. If myRs.BOF is true, this line will throw an error because
	 ' there is no record to move to. The error will be something like this:
	 ' "Either BOF or EOF is true or the Application requires a current record."
	 ' It's usually a good idea to plan for a no records returned (BOF = True) property
	 ' because your user's definitely don't want to see the unformatted ASP error when
	 ' they are searching for information and none is found with their criteria.

	myRs.MoveFirst

End If

 ' you could also call MoveLast to get the last record, MoveNext 
 ' to get the next record or MovePrevious to get the previous record. 
 ' We will use the MoveNext method in a loop below to get all 
 ' the records fitting our sql criteria, but I'm getting a little 
 ' ahead of myself...



 ' 8.) now we make a do loop that will display results for each record
 '     BOF and EOF are special db properties that are boolean values. 
 '     Basically if either is True, there are no records that fit our criteria.

 '     BOF means record before the fact or something bizarre like that.
 '     myRs.BOF will be True only when no results are found after the sql statement is run.
 '     If one or more results are found, myRs.BOF will be False

 '     EOF means record after the fact. If records are found, the next record after 
 '     the last result matching the sql criteria will set myRs.EOF to True

 '     Basically what that means is after the last example in the examples table
 '     is displayed, myRs.EOF will be set to true and our loop will stop running
 '     and proceed to the line directly proceeding the Loop command below.

 '     if we called the movefirst method and then the moveprevious method, myRs.BOF would return true
 '     if we called the movelast method and then the movenext method, myRs.EOF would return true.

Do While NOT myRs.BOF and NOT myRs.EOF
	 ' the previous statement can also be written as:
	 ' 	Do While myRs.BOF = False AND myRs.EOF = False
	 ' or a different way, like this:
	 ' 	Do Until myRs.EOF OR myRs.BOF
	 ' which is the same as writing:
	 ' 	Do Until myRs.EOF = True OR myRs.BOF = True

	response.write vbTab & "<tr>" & vbCrLf

	 ' let's make another for next loop to gather each record's data.
	 ' We will use the Fields collection just like we did above to get the
	 ' field names but we will use the Data property. Specifying nothing
	 ' automatically assumes the Data property by default so we don't have to 
	 ' type as much below:

	c1 = "#FFFFFF"
	For i = 0 to myRs.Fields.Count -1
		if isnull(myRs.Fields(i).value) then
			response.write vbTab & vbTab & "<td bgcolor=""" & c1 & """><null></td>" & vbCrLf
		else
			response.write vbTab & vbTab & "<td bgcolor=""" & c1 & """>" & myRs.Fields(i) & "</td>" & vbCrLf
		end if
		if c1 = "#FFFFFF" then c1 = "#DDDDDD" else c1 = "#FFFFFF"
	NEXT

	response.write vbTab & "</tr>" & vbCrLf
	
	 ' now we move to the next record and repeat the loop until
	 ' we run out of records matching the criteria (EOF).

	myRs.Movenext
Loop



 ' 9.) now the data has been retrieved but we aren't done yet.
 '     it's time to clean up by freeing memory resources and
 '     closing our database.

 ' first we explicitly close the database recordset and connection.
 ' This should free up the server memory immediately.

myRs.Close
myConn.Close

 ' then we will reset the variables by using the Nothing command
 ' Some components will throw an error if they are used again in the
 ' same page without being reset explicitly. It's always a good idea
 ' to set component variables to nothing when done using them unless 
 ' they are declared in the global.asa as objects with session scope.
 ' In that case, setting the values to nothing would result in the global.asa
 ' definition of the objects being lost until the session.abandon method
 ' is called. But such is not the case in this example...

Set myRs = Nothing
Set myConn = Nothing

 ' and don't forget to close that table!
response.write "</table>" & vbCrLf

 ' done!
%>

</body>
</html>