hi there
i am building a one page website that i need query data from an access database (visual studio 2008 c# xml).
I need to do a sql query displaying customers with possible bookings if any. I have been able to display all customers details but i am finding it hard to join the data and show booking associated with customer . i dont know what i am doing wrong
CUSTOMERS TABLE
name
first name
cust id
BOOKINGS
time
table
cust id
i have included the home page that successfully displays the customer details and also at the bottom the .ashx file it calls to do the query. so you have an idea what i am trying to do.
Index.html
<script language="JavaScript" type="text/JavaScript">
<!--
var req;
function getUsers()
{
var url = "Users.ashx"
req = new XMLHttpRequest();
req.open("GET", url, true);
req.onreadystatechange = getUsersCallBack;
req.send(null);
}
function getUsersCallBack()
{
if (req.readyState == 4) {
if (req.status == 200) {
var xmlDoc = req.responseXML;
var users = xmlDoc.getElementsByTagName("customer");// gets all customers element
for (i=0;i<users.length;i++){
var customerID = xmlDoc.getElementsByTagName("customerID")[i].childNodes[0].nodeValue;
var firstname = xmlDoc.getElementsByTagName("firstname")[i].childNodes[0].nodeValue;
var lastname = xmlDoc.getElementsByTagName("lastname")[i].childNodes[0].nodeValue;
var address = xmlDoc.getElementsByTagName("address")[i].childNodes[0].nodeValue;
var postcode = xmlDoc.getElementsByTagName("postcode")[i].childNodes[0].nodeValue;
var phone = xmlDoc.getElementsByTagName("phone")[i].childNodes[0].nodeValue;
var email = xmlDoc.getElementsByTagName("email")[i].childNodes[0].nodeValue;
var customerinfo = "<p><b>" + customerID + "</b><br/>" + firstname + " " + lastname + "</b><br/>" + address + "</b><br/>" + postcode + "</b><br/>" + phone + "</b><br/>" + email + "</p>";
document.getElementById("userlist").innerHTML += customerinfo
}
}
}
}
-->
</script>Users.ashx
<%@ WebHandler Language="C#" Class="Customers" %>
using System;
using System.Web;
using System.Data.OleDb;
using System.Text;
using System.Xml;
public class Customers : IHttpHandler {
public void ProcessRequest (HttpContext context) {
OleDbConnection conn;
OleDbCommand comm;
OleDbDataReader dr;
conn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = |DataDirectory|gcurestaurants.mdb");
comm = new OleDbCommand("Select customerID, firstname, lastname, address, postcode, phone, email from Customers", conn);
conn.Open();
dr = comm.ExecuteReader();
context.Response.ContentType = "text/xml";
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
settings.OmitXmlDeclaration = true;
settings.NewLineOnAttributes = true;
XmlWriter writer = XmlWriter.Create(context.Response.OutputStream, settings);
writer.WriteStartDocument();// starts a new document
writer.WriteStartElement("customers");// used to add a new element to the document
while (dr.Read())
{
writer.WriteStartElement("customer");
writer.WriteElementString("customerID", dr[0].ToString());
writer.WriteElementString("firstname", dr[1].ToString());
writer.WriteElementString("lastname", dr[2].ToString());
writer.WriteElementString("address", dr[3].ToString());
writer.WriteElementString("postcode", dr[4].ToString());
writer.WriteElementString("phone", dr[5].ToString());
writer.WriteElementString("email", dr[6].ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Close();
dr.Close();
conn.Close();
}
public bool IsReusable {
get {
return false;
}
}
}cheers
