Did you find this useful?
Socialize it today.


Ajax and ASP.NET with SQL

Monday, July 30, 2012

In this article, we are going to look at how to implement an Ajax solution that uses an ASP.NET page to pull data from a back-end database. In this tutorial, we have a table stored in a MySQL database. You can replace the ASP.NET code on this page with any other server-side scripting languages that you are familiar with such as ASP or PHP. You can refer to the other tutorials in this series for more help using a different server-side scripting language. In addition, the back-end data source does not have to be MySQL. You can modify the database connection in the example for accessing other database platforms. The concept remains the same.


ajax asp.net

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.aspx?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.NET Example

Imports System.Data.Odbc

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        
    Response.AppendHeader("Cache-Control", "no-cache")
    Response.AppendHeader("Cache-Control", "no-store")
    Response.AppendHeader("Cache-Control", "must-revalidate")
    Response.AppendHeader("Pragma", "no-cache")
    Response.AppendHeader("Expires", "Sat, 14 Jan 2012 01:00:00 GMT")
        
    Dim db_svr As String = "dbserver.com"
    Dim db_name As String = "databaseName"
    Dim db_un As String = "dbUserName"
    Dim db_upwd As String = "dbPassword"
    Dim tablename As String = "employees"
    Dim fieldname1 As String = "empName"
    Dim fieldname2 As String = "empTitle"
    Dim fieldname3 As String = "empOffice"
    Dim q As String = Request.QueryString("q")
    Dim dbQuery As String = "SELECT * FROM " & tablename & " WHERE empID = " & q
        
        
    Dim connStr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_svr & ";DATABASE=" & db_name & ";UID=" & db_un & ";PWD=" & db_upwd
    Dim dbconn As OdbcConnection = New OdbcConnection(connStr)

    Dim dbCommand As New OdbcCommand
    dbCommand.Connection = dbConn
    Try
        dbconn.Open()
        dbCommand.CommandText = dbQuery
        Dim reader = dbCommand.ExecuteReader()
        Response.Write("<table class='tblClear'>")
        Do While reader.Read() = True
            Response.Write("<tr><td style='width:100px;'>Name:</td><td>" & reader(fieldname1) & "</td></tr>")
            Response.Write("<tr><td style='width:100px;'>Name:</td><td>" & reader(fieldname2) & "</td></tr>")
            Response.Write("<tr><td style='width:100px;'>Name:</td><td>" & reader(fieldname3) & "</td></tr>")
        Loop
        dbconn.Close()
        Response.Write("</table>")

    Catch ex As Exception
        Response.Write("An error occured trying to access the database.")
    End Try
End Sub


In this demo, we use a front-end HTML web page that loads data from an ASP.NET (.aspx) page via Ajax. When a user selects an item in the dropdown list, the selection is sent to the ASP.NET page using Ajax and the receiving page uses the information passed in the query string. The value of the query string parameter is sent to the database and the results are sent back to the HTML page on the XmlHttpResponse.responseText property.

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

The Complete Reference Ajax Bulletproof Ajax Ajax for Dummies