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
Help with Code Tags
ASP.NET Syntax (Toggle Plain Text)
1.
<script language="JavaScript" type="text/JavaScript">
2.
<!--
3.
var req;
4.
5.
function getUsers()
6.
{
7.
var url = "Users.ashx"
8.
9.
req = new XMLHttpRequest();
10.
req.open("GET", url, true);
11.
req.onreadystatechange = getUsersCallBack;
12.
req.send(null);
13.
}
14.
15.
function getUsersCallBack()
16.
{
17.
if (req.readyState == 4) {
18.
if (req.status == 200) {
19.
var xmlDoc = req.responseXML;
20.
var users = xmlDoc.getElementsByTagName("customer");// gets all customers element
21.
for (i=0;i<users.length;i++){
22.
var customerID = xmlDoc.getElementsByTagName("customerID")[i].childNodes[0].nodeValue;
23.
var firstname = xmlDoc.getElementsByTagName("firstname")[i].childNodes[0].nodeValue;
24.
var lastname = xmlDoc.getElementsByTagName("lastname")[i].childNodes[0].nodeValue;
25.
var address = xmlDoc.getElementsByTagName("address")[i].childNodes[0].nodeValue;
26.
var postcode = xmlDoc.getElementsByTagName("postcode")[i].childNodes[0].nodeValue;
27.
var phone = xmlDoc.getElementsByTagName("phone")[i].childNodes[0].nodeValue;
28.
var email = xmlDoc.getElementsByTagName("email")[i].childNodes[0].nodeValue;
29.
var customerinfo = "<p><b>" + customerID + "</b><br/>" + firstname + " " + lastname + "</b><br/>" + address + "</b><br/>" + postcode + "</b><br/>" + phone + "</b><br/>" + email + "</p>";
30.
document.getElementById("userlist").innerHTML += customerinfo
31.
32.
}
33.
34.
}
35.
}
36.
}
37.
-->
38.
</script>
<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>
Help with Code Tags
ASP.NET Syntax (Toggle Plain Text)
1.
Users.ashx
2.
3.
4.
<%@ WebHandler Language="C#" Class="Customers" %>
5.
6.
using System;
7.
using System.Web;
8.
using System.Data.OleDb;
9.
using System.Text;
10.
using System.Xml;
11.
12.
public class Customers : IHttpHandler {
13.
public void ProcessRequest (HttpContext context) {
14.
15.
OleDbConnection conn;
16.
OleDbCommand comm;
17.
OleDbDataReader dr;
18.
conn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = |DataDirectory|gcurestaurants.mdb");
19.
comm = new OleDbCommand("Select customerID, firstname, lastname, address, postcode, phone, email from Customers", conn);
20.
conn.Open();
21.
dr = comm.ExecuteReader();
22.
23.
context.Response.ContentType = "text/xml";
24.
25.
XmlWriterSettings settings = new XmlWriterSettings();
26.
settings.Indent = true;
27.
settings.OmitXmlDeclaration = true;
28.
settings.NewLineOnAttributes = true;
29.
30.
31.
XmlWriter writer = XmlWriter.Create(context.Response.OutputStream, settings);
32.
writer.WriteStartDocument();// starts a new document
33.
writer.WriteStartElement("customers");// used to add a new element to the document
34.
35.
36.
37.
while (dr.Read())
38.
{
39.
writer.WriteStartElement("customer");
40.
writer.WriteElementString("customerID", dr[0].ToString());
41.
writer.WriteElementString("firstname", dr[1].ToString());
42.
writer.WriteElementString("lastname", dr[2].ToString());
43.
writer.WriteElementString("address", dr[3].ToString());
44.
writer.WriteElementString("postcode", dr[4].ToString());
45.
writer.WriteElementString("phone", dr[5].ToString());
46.
writer.WriteElementString("email", dr[6].ToString());
47.
writer.WriteEndElement();
48.
}
49.
50.
51.
52.
writer.WriteEndElement();
53.
writer.WriteEndDocument();
54.
writer.Close();
55.
dr.Close();
56.
conn.Close();
57.
58.
}
59.
60.
public bool IsReusable {
61.
get {
62.
return false;
63.
}
64.
}
65.
}