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