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("
| Name: | " & oRs.Fields(fieldname1) & “ |
| Title: | " & oRs.Fields(fieldname2) & “ |
| Office: | " & oRs.Fields(fieldname3) & “ |
