This is by no means an exhaustive help for ASP database connections, but merely provided as a brief explanation of various commands and objects which this Custom Dialogue implements. See additional resources at the bottom of this page for more robust tutorials and documentation.
Typical database connections via Active Server Pages use three objects, the Connection object, for setting up the initial connection, the Command object, which facilitates a request to a database, and the Recordset object, which enables control over data manipulation and display.
The Connection object's Open method initializes a database connection. The Open method accepts three arguments, the ConnectionString, User, and Password. Typically, the ConnectionString is the database's DSN, but can be made up of inidividual parameters describing the connection. User and Password are needed if the connection in question requires them.
Note that this custom dialogue assumes you are using a DSN for a ConnectionString.
The Command Object queries a database. Its main properties include ActiveConnection, CommandText, and CommandType.
The ActiveConnection denotes which connection the command is querying, typically the connection
just set up by the Connection Object. CommandText is generally a SQL statement or Stored Procedure.
The CommandType describes what kind of command is being issued, whether it is a SQL statement, Stored
Procedure, or others.
Note that if a Stored Procedure is being called, the Command Object's CreateParameter method must be
invoked, and this returns the Parameters collection. The Parameters collection has various properties
and methods, describing and creating the necessary parameters for a Stored Procedure.
When a Command returns data, this data may be contained in a RecordSet Object. The RecordSet Object has many useful properties and methods (too many to describe here), but the three arguments needed to create the object are Source, ActiveConnection, CursorType, LockType, and Options. The Source is a Command Object. Since the Command Object of this custom dialogue already denotes the ActiveConnection, we can leave this argument blank. CursorType describes how we'll be browsing the RecordSet, either forward-only, dynamically, or other options available. LockType denotes what kind of locking scheme we use when opening the RecordSet. Options describes the type of query represented by Source.
There are several RecordSet methods worthy of note here. They include: MoveNext; move to the next record, AddNew; add new records, Update; save any changes made to the RecordSet, Delete; to eliminate a particular record. Note that depending upon the properties you used to open the RecordSet, some or all of these methods will not be available.
Note: It is recommended that you use the default values of the custom dialogue unless you are familiar with the distinctions between these properties.
Bear in mind the limitations imposed by the type of the recordset-the cursor type. In a recordset opened with the default cursor type of adOpenForwardOnly, we you can only move forward . If you want to be able to move around in a recordset ie.use move,update...etc, you need to open it with a cursor type of adOpenStatic, adOpenDynamic or adOpenKeySet instead.
MS Text Driver requires that you include schema.ini. Schema.ini determines what is used as a delimiter and sets other parameters such as the path to the file in use, number of rows counted, whether or not to use the first row as column headers and the characterset.
Note: that the differences between MDB files and other file based databases is that MDB files need to have the file name ("anyfile.mdb") as the DBQ value. Other databases, such as Comma Delimited (CSV text files), FoxPro (DBF), Paradox (DB), MS Excel (.xls) use their respective directory name as the DBQ value.
Be sure to note how we specify the DBQ as the directory name, NOT as the .txt filename!Example:
[data.txt] ColNameHeader=True Format=Delimited(*) MaxScanRows=25 CharacterSet=OEM
Here is an example of how a text file should look.(data.txt):
ID*Name*Price 1*"Elvis Presley Diamond Vinyl"*"$40.00" 2*"Bo Didley original on vinyl"*"$75.00" 3*"HoneyCombs"*"$1.50" 4*"Monitors"*"$15.00" 5*"Jack Scott"*"$35.00" 6*"Moms Mabeley"*"$25.00" 8*"house"*"$15.00"
Make a connection a serve up the records as below:
Be sure to note how we specify the DBQ as the directory name, NOT as the .txt filename!
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq="e:\users\users\domain\wwwroot\db\";" & _
"Extensions=asc,csv,tab,txt;" & _
"Persist Security Info=False"
' Get only the Name and Price fields
Dim sql
sql = "SELECT Name,Price FROM data.txt ORDER BY ID DESC"
set rs = oConn.Execute(sql)
'Print out the contents of our recordset
Do While Not rs.EOF
If rs.Fields(0).value <> "" then
Response.Write "Name: " & rs("Name")
Response.Write "<BR>Price: " & rs("Price")
Response.Write "<HR>"
end if
rs.MoveNext
Loop
'Close our recordset and connection
rs.close
set rs = nothing
oConn.close
set oConn = nothing
<%
Dim sScriptDir
sScriptDir = Request.ServerVariables("SCRIPT_NAME")
sScriptDir = StrReverse(sScriptDir)
sScriptDir = Mid(sScriptDir, InStr(1, sScriptDir, "/"))
sScriptDir = StrReverse(sScriptDir)
Dim sPath, sDSN
sPath = Server.MapPath(sScriptDir) & "\"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"DBQ=" & sPath & "matcher.xls;" & _
"DefaultDir=" & sPath & ";"
Dim sql
sql = "SELECT * FROM Matcher "
Note: you can also designate which sheet
"Select * from [SheetName$]"
set rs = Conn.Execute(sql)
'Print out the contents of our recordset
Do While Not rs.EOF
If rs.Fields(0).value <> "" then
Response.Write "Home: " & rs("HomeTeam") &" "& rs("HomeScore")
Response.Write "
Visitors: " & rs("VisitTeam") &" " & rs("VisitScore")
Response.Write "
"
end if
rs.MoveNext 'Move to the next record
Loop
'Close our recordset and connection
rs.close
set rs = nothing
conn.close
set conn = nothing
%>
15 seconds, http://www.15seconds.com/
ZDNet ASP Developer, http://www.zdnet.com/devhead/filters/0,9429,2133219,00.html
ASP Developer Network, http://www.aspdeveloper.net/
ASP Today, http://www.asptoday.com/
I also recommend Active Sever Pages 2.0 by WROX Press. WROX has a section of their site for ASP, located at http://www.wrox.com/Consumer/Default.asp?Category=ASP.
Copyright Keith Collison, 1999
kcollison@erols.com