ADO Connection Object ~ Naija Photo Vibes

Table Of Contents[ Show ]

1. Syntax
2. Connection Strings
   1. Microsoft Access
      1. DSN-less
      2. System DSN
      3. OLE DB
   2. MS SQL
      1. DSN-less
      2. System DSN
      3. OLE DB
   3. MySQL
      1. DSN-less
      2. System DSN
   4. Example

ADO Connection Objects are an incredibly important tool in the world of data access and manipulation. They provide a powerful and convenient way to connect to a variety of data sources, such as SQL Server, Oracle, and Access. With ADO Connection Objects, users can easily open and close database connections, execute SQL queries, and manipulate data without having to write any code.

ADO Connection Objects are incredibly versatile and are used extensively in web applications, desktop applications, and web services. They provide a number of properties and methods that simplify data access and manipulation, making them the preferred choice for many developers. They also offer high levels of reliability and security, making them a secure and reliable choice for data access.

Overall, ADO Connection Objects offer a great range of features that allow developers to access and manipulate data with ease. With their powerful capabilities and extensive range of features, they are the go-to choice for many developers when accessing data from a variety of sources.

Syntax

<%
Set oConn=Server.CreateObject("ADODB.connection")
%>

Connection Strings

The ADO Connection object provides a convenient way to access databases. It requires a connection string to connect to the data source, which contains information such as the server name, database name, user name, and password. Connection strings can be written in either the ODBC or OLEDB format, and can also include optional parameters for additional customization and security. It is important to note that connection strings are case-sensitive and must be formatted correctly for the application to make a successful connection.

Using the ADO Connection object provides a simple way to connect to a data source, but it is important to understand the connection string format and the optional parameters available to ensure a successful connection. With the right information and a properly formatted connection string, the ADO Connection object can provide reliable access to databases.

It is possible that one of the connection strings listed below could be used to access the same data source. However, it is important to remember to alter details such as the database name, server name, database location, Data Source Name (DSN), etc… before attempting to use the connection string.

Microsoft Access

DSN-less

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:mydatabase.mdb"
%>

System DSN

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "DSNname"
%>

OLE DB

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=c:mydatabase.mdb"
%>

MS SQL

DSN-less

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "DRIVER={SQL Server};SERVER=ServerName;UID=USER;PWD=password;DATABASE=mydatabase"
%>

System DSN

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "DSN=MyDSN;UID=user;PWD=password;DATABASE=mydatabase"
%>

OLE DB

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=mydatabase"
%>

MySQL

DSN-less

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "Driver={MySQL ODBC 3.51 Driver};SERVER=ServerName;DATABASE=mydatabase;UID=username;PWD=password"
%>

System DSN

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "DSN=MyDSN"
%>

Also Read: How Hackers Use These 6 Hacking Techniques to Access Someone’s Facebook Account

Example

To begin, we will create a DNS-less connection to a MySQL database table called “Employees.” We will then execute a SQL select query and capture its results in a recordset. To finish, we will extract the information from the recordset and display it in a table.

<!DOCTYPE html>
<html>
<head>
    <title>My Page</title>
</head>
<body>
    <% 
    Dim oConn, oRS, datasource, sql
    Set oConn=Server.CreateObject("ADODB.Connection")
    Set oRS = Server.CreateObject("ADODB.recordset")
    datasource = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
    sql = "SELECT empName, empTitle FROM employees"
    oConn.Open datasource
    oRS.Open sql, oConn
    %> 
    <table style="width:500px;">
        <tr>
            <%for each x in oRS.Fields
            Response.Write("<th>" & x.name & "</th>")
            next%>
        </tr>
        <%do until oRS.EOF%>
        <tr>
            <%for each x in oRS.Fields%>
            <td><%Response.Write(x.value)%></td>
            <%next
            oRS.MoveNext%>
        </tr>
        <%loop
        oRS.close
        oConn.close
        Set oRS=nothing
        Set oConn=nothing
        %>
    </table>
</body>
</html>

You can access the data source and gather the data into a recordset after creating an instance of the Connection object and opening the connection. Close the active connection and tidy up your objects once you’ve finished working with the data.

ADO objects should be opened just before use and closed as soon as you are finished. While other logic is running, this frees up resources. Additionally, it’s just good programming technique.

<%
oConn.close
Set oConn=nothing
%>