asp.net c# access database

[davidcrossan]

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 + "&nbsp;" + 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

[hericles]

Hi,
Are you getting the booking info from the same database? Your SQL query could simply use a JOIN on the two tables (Customers and Bookings) as they both have customer_id as a column.
I couldn't see any location where you had tried to draw out the booking info though...
Hericles

[Mohtshm]

it is very simple i think u need to work with ur query like this
("Select customerID, firstname, lastname, address, postcode, phone, email from Customers where customerID in (select customerID from bookings
) ")

And try to avoid join use in query
u could also put some condition in subquery as well for bookings table