im have tables in access,Customer,Products and Order Table...
Customer Table: -
Cust_ICNumber (PK)
-
Cust_Name
-
Cust_Address
-
Cust_PrimaryNumber
-
Cust_SecondaryNumber
-
Cust_Company
-
Cust_CompanyType
-
Products Table -
Product_ID (PK)
-
Product_Name
-
Product_Price
-
Product_Picture
-
Order Table -
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 -
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.
8  3224 
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
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.
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.
Im didnt try in on Query Designer yet...Yes i have written the html but im using javascript to display the data
this is my code for the html/javascript... - <%@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>
-
What is the error your getting?
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. -
<%
-
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> </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> </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~
it helps, thanks for letting me know the error...
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bilal Iqbal |
last post by:
Hi.
Currently i have database MS Access. In the database there are some
Reports already designed. i am developing interface in VB and linking to
MS Access database.
My Problem is to access/Link...
|
by: Yannick Turgeon |
last post by:
Hello,
We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The...
|
by: AnandaSim |
last post by:
I just had a google through this NG but have not seen mention of Erik
Rucker's blog entry and the new Jet:
http://blogs.msdn.com/access/archive/2005/10/05/477549.aspx
mentioned by Mike...
|
by: Sisnaz |
last post by:
In classic ASP I could specify a relative path (server side mapping) or
absolute path to an Access database file. I can connect to an access file
with no problems using the absolute path of...
|
by: Allison |
last post by:
Hi -- we are a small manufacturing looking for a multi-user database to
take customer orders (nothing too complicated, with 3 users total). We
think we should be using Access, but are wondering...
|
by: Lyle Fairfield |
last post by:
http://samples.gotdotnet.com/QuickStart/howto/default.aspx?url=/quickstart/howto/doc/adoplus/ADOPlusOverview.aspx
**** begin quote ****
ADO.NET Overview
ADO.NET is an evolution of the ADO data...
|
by: Classic-Car-World Ltd |
last post by:
Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:
Product No, order,...
|
by: InnoCreate |
last post by:
Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on...
|
by: JonC |
last post by:
I am developing an Access database and need to be able to print a form
with various selections made as it appears on screen. The standard way
of doing this would be to create a report based on the...
|
by: Dave |
last post by:
I work at a small private company that has been using VB 6.0 as a
front end for Access databases for 8 years. It looks as though we
will be migrating to Vista in the not-too-distant future. My...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
