|
ASP > Database articles: • How do I get a RecordSet (a bunch of records) from a database using ASP?
• Once I have my RecordSet (a bunch of records) from my database, how do I loop through the records and display them using ASP?
Read
|
Return to index of articles
How do I get a RecordSet (a bunch of records) from a database using ASP?
Category: ASP
Category: Database
There are two steps involved in getting a recordset (collection of records) from
your database, whether it's Oracle, SQL Server or lowly Microsoft Access (which
this site uses): first you create an ADO connection to the database, then you get your recordset.
<%
' declare the variable
dim Conn
' create Conn, our variable which holds the ADO connection
Set Conn = Server.CreateObject("ADODB.Connection")
' to open the connection, use one of these two
options:
'option 1 - if you have set up a DSN on your server via Data Sources
(ODBC) or equivalent to connect to a server-based database such as SQL Server
or Oracle
Conn.Open "DSNNAME"
'option 2 - if you have a database in a file on your web server e.g. a Microsoft
Access MDB file
strPath = server.MapPath("/directoryName/filename.mdb") ' enter
the appropriate dir/file names
Conn.ConnectionString = "DBQ=" + strPath + ";Driver={Microsoft
Access Driver (*.mdb)};"
Conn.Open
%>
At this point you have a variable Conn which is an ADO connection to your
database. Next you have to create your recordset and get some data:
<%
dim rsName, strSQL
SET rsName = Server.CreateObject ("ADODB.Recordset")
' strSQL contains our SQL select statement, something you hopefully know how
to compose
' shown here are some typical things you will have in there such as the tableName,
fieldNames, comparison operators, an ORDER BY field, etc.
strSQL = "select * from tableName WHERE fieldName1='whatever' AND fieldName2
LIKE '%text%' ORDER BY fieldName3 ; "
rsName.Open strSQL, Conn
%>
So at this point you have a recordset full of data from your database, or
an empty recordset. Normally you would check to see if the recordset is empty
- if so, return an error or message, and if not, loop through the data and
display it.
9/1/2003
|
