Computers & ProgrammingAJAXFrontend Development

AJAX and ASP with SQL Data

The XMLHttpRequest object is used to send and receive data between a web browser and web server. Up to now, we have been retrieving data from data sources that remain relatively static, such as text or XML files.

In this tutorial, we will learn how to use the same Ajax concepts that we have learned so far and pull data from your database. We will be using an ASP file to handle the server-side scripting and MySQL as our database.

You can replace ASP with any other server-side scripting language that you are familiar with such as PHP, or ASP.NET. The back-end data source can be swapped for MS Access, as well as MSSQL. The concept remains the same.

HTML Example

<!DOCTYPE html>
<html>
<head>
    <script type="text/javascript">
        function showEmployee(str) {
            if (str==""){
                document.getElementById("div1").innerHTML="Select an Employee for more details!";
                return;
            }
            var xhr = false;
            if (window.XMLHttpRequest) {
                // IE7+, Firefox, Chrome, Opera, Safari
                xhr = new XMLHttpRequest();
            } else {
                // IE5/IE6
                xhr = new ActiveXObject("Microsoft.XMLHTTP");
            }
            if (xhr) {
                xhr.onreadystatechange = function () {
                    if (xhr.readyState == 4 && xhr.status == 200) {
                        document.getElementById("div1").innerHTML = xhr.responseText;
                    }
                }
                xhr.open("GET", "/demo/ajax_dbquery.asp?q="+str, true);
                xhr.send(null);
            }
        }
    </script>
</head>
<body>
    <div>
        <select name="employees" onchange="showEmployee(this.value)">
            <option value="">Select an Employee:</option>
            <option value="3">Frank Ford</option>
            <option value="1">John Smith</option>
            <option value="4">Lisa Stark</option>
            <option value="2">Sally Smart</option>
        </select>
        <div id="div1">Select an Employee for more details!</div>
    </div>
</body>
</html>

ASP Example

<% Response.AddHeader "Cache-Control", "no-cache" %>
<% Response.AddHeader "Cache-Control", "no-store" %>
<% Response.AddHeader "Cache-Control", "must-revalidate" %>
<% Response.AddHeader "Pragma", "no-cache" %> 
<% Response.AddHeader "Expires", "Sat, 14 Jan 2012 01:00:00 GMT" %>
<% 
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
%>

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top