ASP Database Access Using ADO

Tuesday, September 18, 2012

tags ASP

While ASP as a server-side scripting language is very useful, without access to a Database, you will be generally limited to serving static content. Using ADO, we can easily connect to a variety of data sources such as Microsoft Access, Microsoft SQL Server, MySQL, etc... You can connect with or without a Data Source Name (DSN). Using a DSN provides you with an easy way to manage the data source connection and also provide you with access to sources other than the database platforms previously mentioned.

In the example below, we are connecting to a data source and query a table called employees. The results are simply displayed within a table element. Depending on how you connect to the data source will determine the value you assign to the variable "cstr". In the example below we are connecting to a MySQL database, using a non-DSN connection string. However, if you connect via DSN, the string may look something like this:

cstr = "DSN=dsn_name;UID=user_name;PWD=password;Database=database_name"

or such as this if the username and password is stored in the DSN.

cstr = "DSN=dsn_name"

Example

<% 
Dim oConn, oRs
Dim qry, cstr
Dim db_name, db_username, db_userpassword
Dim db_server

db_server = "dbserver.com"
db_name = "databaseName"
db_username = "dbUserName"
db_userpwd = "dbPassword"
tablename = "employees"
fieldname1 = "empName"
fieldname2 = "empTitle"
fieldname3 = "empOffice"
q = request.querystring("q")

cstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpwd

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open cstr

qry = "SELECT * FROM " & tablename & " WHERE empID = " & q

Set oRS = oConn.Execute(qry)
response.write("<table>")
if not oRS.EOF then
    while not oRS.EOF
        response.write ("<tr><td>Name:</td><td>" & oRs.Fields(fieldname1) & "</td></tr>")
        response.write ("<tr><td>Title:</td><td>" & oRs.Fields(fieldname2) & "</td></tr>")
        response.write ("<tr><td>Office:</td><td>" & oRs.Fields(fieldname3) & "</td></tr>")
        oRS.movenext
    wend
    oRS.close
end if
response.write("</table>")
Set oRs = nothing
Set oConn = nothing
%>


Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

Beginning ASP Databases Sams Teach Yourself Active Server Pages 3.0 in 21 Days ASP In A Nutshell