access with classic asp

puT3

im have tables in access,Customer,Products and Order Table...

Customer Table:

Expand|Select|Wrap|Line Numbers

 
Cust_ICNumber (PK)

Cust_Name

Cust_Address

Cust_PrimaryNumber

Cust_SecondaryNumber

Cust_Company

Cust_CompanyType

Products Table

Expand|Select|Wrap|Line Numbers

 
Product_ID (PK)

Product_Name

Product_Price

Product_Picture

Order Table

Expand|Select|Wrap|Line Numbers

 
Order_ID(PK)

Product_ID(FK)

Order_Quantity

Order_Date

Customer_ICNumber (FK)

Total_Price

I intend to output bill (a classic asp file) from order table which will display Order ID,Product Name,Product Price, Customer Name,Customer Address,Order Date.
I wondering if this sql is correct

Expand|Select|Wrap|Line Numbers

 
SELECT tCustomer.Cust_Name, tCustomer.Cust_Address, tProducts.Product_Name,tOrder.Order_Quantity, tOrder.Order_Date,tOrder.Order_ID, tOrder_Total_Price FROM (((Order AS tOrder LEFT JOIN Customer AS tCustomer ON tOrder.Customer_ICNumber = tCustomer.Cust_ICNumber) LEFT JOIN Products AS tProducts ON tOrder.Product_ID = tProducts.Product_ID);

Another question is do i need to create the relationship in the database when using classic asp...Sorry for this question,i know it is sound silly but i just want to confirm it as im very new at this.

jhardman

You have a pretty complicated SQL statement for someone who is new at this! In order to make sure it does what you want you need to test the query in a query analyzer - I haven't used Access in a while, but you should be able to send this code to access and have access spit out a sample response. Beyond that, an access expert would be better equipped to answer you.

For your other question, the ASP will work whether the relationship is defined or not, but it is a good idea to define the relationship - especially for purposes of adding and deleting records. For example, if the relationship is defined, it may prevent you from deleting the wrong record.

Jared

puT3

Im wondering if there is any way to retrieve record from three different table(table as above). Because I have been trying to use the sql I created and keep getting error.

GazMathias

Does your query work in the Query Designer in Access? If not it should highlight what is wrong.

When you've finished, view the query in SQL view and copy it to your asp page and run it. You should get the same results.

Have you written the HTML / VB Script (I assume) to display this data?

Gaz.

puT3

Im didnt try in on Query Designer yet...Yes i have written the html but im using javascript to display the data

puT3

this is my code for the html/javascript...

Expand|Select|Wrap|Line Numbers

 <%@language="javascript"%>

<html>

<%
 
var DSN = "C:/Inetpub/wwwroot/MyBakery.mdb";

var Conn = Server.CreateObject("ADODB.Connection");

Conn.Provider = "Microsoft.Jet.OLEDB.4.0";

Conn.Open(DSN);
 
sql = "SELECT * FROM Customer INNER JOIN Order ON Customer.Customer_ICNumber = Order.Customer_ICNumber";
 
rs = Server.CreateObject("ADODB.Recordset");

rs.Open(sql,Conn);
 
Response.Write("Delish Cakes and Pastries");
 
out = "<table>";

out+="<tr><td>Name:</td><td>" + rs("Cust_Name") + "</td><td></td><td>Order ID:</td><td>" 

out+= rs("Order_ID") + "</td></tr>";

out+="<tr><td>Address:</td><td>" + rs("Cust_Address") + "</td><td></td><td>Order Date:</td><td>"

out += rs("Order_Date") + "</td></tr>";

out+="<h2>Orders:</h2><p>"

out+="<tr><td><pre>Product</pre></td><td>Quantity</td><td>Price</td></tr>"

out+="<tr><td>" + rs("Product_Name") + "</td><td>"

out+=rs("Order_Quantity") + "</td><td>"

out+=rs("Product_Price") + "</td></tr>";

out+="</table>";
 
Response.write(out);
 
%>
 
</body>

</html>

CroCrew

What is the error your getting?

CroCrew

Hello puT3,

Looks like one of your “main” problems is that you’re using a reserved word as your table name. The word “Order” is a reserved SQL command and should not be used as a table or field name.

Another problem I can see is that you HTML is off too. You have tags out side of their designated usage spots; if you understand what I am saying.

Below is a VB version of your JavaScript version using the table name “Orders” and not “Order”.

Have a look and give it a try.

Expand|Select|Wrap|Line Numbers

 
<% 

    Set adoCon = Server.CreateObject("ADODB.Connection") 

    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("C:/Inetpub/wwwroot/MyBakery.mdb")  

    Set rsCustomers = Server.CreateObject("ADODB.Recordset") 
 
    SQL = "SELECT * FROM Customer a INNER JOIN Orders  b ON (a.Customer_ICNumber = b.Customer_ICNumber)" 

    rsCustomers.CursorType = 2 

    rsCustomers.LockType = 3 

    rsCustomers.Open SQL, adoCon 
 
    Response.Write("Delish Cakes and Pastries")

    Response.Write("<table>")

    Response.Write("<tr><td>Name:</td><td>" & rsCustomers("Cust_Name").value & "</td><td>&nbsp;</td><td>Order ID:</td><td>" & rsCustomers("Order_ID").value & "</td></tr>")

    Response.Write("<tr><td>Address:</td><td>" & rsCustomers("Cust_Address".value & "</td><td>&nbsp;</td><td>Order Date:</td><td>" & rsCustomers("Order_Date").value & "</td></tr>")

    Response.Write("<tr><td colspan='5'><h2>Orders:</h2></td></tr>")

    Response.Write("<tr><td colspan='2'><pre>Product</pre></td><td>Quantity</td><td>Price</td></tr>")

    Response.Write("<tr><td colspan='2'>" & rsCustomers("Product_Name").value & "</td><td>" & rsCustomers("Order_Quantity").value & "</td><td>" & rsCustomers("Product_Price").value & "</td></tr>")

    Response.Write("</table>")

%>

Hope this helps,
CroCrew~

puT3

it helps, thanks for letting me know the error...

access with classic asp

re: access with classic asp

re: access with classic asp

re: access with classic asp

re: access with classic asp

re: access with classic asp

re: access with classic asp

re: access with classic asp

re: access with classic asp

Similar ASP / Active Server Pages bytes

/bytes/development

/bytes/it

/bytes/about