Did you find this useful?
Socialize it today.


Ajax and ASP with SQL Data

Thursday, July 26, 2012

In the previous articles, we have learned that the XMLHttpRequest object is used to send and receive data with between a web browser and web server. Up to now, we have been retrieving data from data sources that remain relatively static, such as a text or XML file. 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.


ajax asp

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
%>


Demo


Select an Employee for more details!


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

Bulletproof Ajax The Complete Reference Ajax HTML CSS and JavaScript Editor