468,514 Members | 1,678 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,514 developers. It's quick & easy.

access with classic asp

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

Customer Table:
Expand|Select|Wrap|Line Numbers
  1. Cust_ICNumber (PK)
  2. Cust_Name
  3. Cust_Address
  4. Cust_PrimaryNumber
  5. Cust_SecondaryNumber
  6. Cust_Company
  7. Cust_CompanyType
  8.  
Products Table
Expand|Select|Wrap|Line Numbers
  1. Product_ID (PK)
  2. Product_Name
  3. Product_Price
  4. Product_Picture
  5.  
Order Table
Expand|Select|Wrap|Line Numbers
  1. Order_ID(PK)
  2. Product_ID(FK)
  3. Order_Quantity
  4. Order_Date
  5. Customer_ICNumber (FK)
  6. Total_Price
  7.  
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
  1. 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);
  2.  
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.
Oct 6 '09 #1
8 2914
jhardman
3,405 Expert 2GB
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
Oct 7 '09 #2
puT3
145 100+
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.
Oct 8 '09 #3
GazMathias
200 Expert 100+
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.
Oct 8 '09 #4
puT3
145 100+
Im didnt try in on Query Designer yet...Yes i have written the html but im using javascript to display the data
Oct 9 '09 #5
puT3
145 100+
this is my code for the html/javascript...

Expand|Select|Wrap|Line Numbers
  1. <%@language="javascript"%>
  2. <html>
  3. <%
  4.  
  5. var DSN = "C:/Inetpub/wwwroot/MyBakery.mdb";
  6. var Conn = Server.CreateObject("ADODB.Connection");
  7. Conn.Provider = "Microsoft.Jet.OLEDB.4.0";
  8. Conn.Open(DSN);
  9.  
  10. sql = "SELECT * FROM Customer INNER JOIN Order ON Customer.Customer_ICNumber = Order.Customer_ICNumber";
  11.  
  12. rs = Server.CreateObject("ADODB.Recordset");
  13. rs.Open(sql,Conn);
  14.  
  15. Response.Write("Delish Cakes and Pastries");
  16.  
  17. out = "<table>";
  18. out+="<tr><td>Name:</td><td>" + rs("Cust_Name") + "</td><td></td><td>Order ID:</td><td>" 
  19. out+= rs("Order_ID") + "</td></tr>";
  20. out+="<tr><td>Address:</td><td>" + rs("Cust_Address") + "</td><td></td><td>Order Date:</td><td>"
  21. out += rs("Order_Date") + "</td></tr>";
  22. out+="<h2>Orders:</h2><p>"
  23. out+="<tr><td><pre>Product</pre></td><td>Quantity</td><td>Price</td></tr>"
  24. out+="<tr><td>" + rs("Product_Name") + "</td><td>"
  25. out+=rs("Order_Quantity") + "</td><td>"
  26. out+=rs("Product_Price") + "</td></tr>";
  27. out+="</table>";
  28.  
  29. Response.write(out);
  30.  
  31. %>
  32.  
  33. </body>
  34. </html>
  35.  
Oct 9 '09 #6
CroCrew
564 Expert 512MB
What is the error your getting?
Oct 9 '09 #7
CroCrew
564 Expert 512MB
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
  1. <% 
  2.     Set adoCon = Server.CreateObject("ADODB.Connection") 
  3.     adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("C:/Inetpub/wwwroot/MyBakery.mdb")  
  4.     Set rsCustomers = Server.CreateObject("ADODB.Recordset") 
  5.  
  6.     SQL = "SELECT * FROM Customer a INNER JOIN Orders  b ON (a.Customer_ICNumber = b.Customer_ICNumber)" 
  7.     rsCustomers.CursorType = 2 
  8.     rsCustomers.LockType = 3 
  9.     rsCustomers.Open SQL, adoCon 
  10.  
  11.     Response.Write("Delish Cakes and Pastries")
  12.     Response.Write("<table>")
  13.     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>")
  14.     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>")
  15.     Response.Write("<tr><td colspan='5'><h2>Orders:</h2></td></tr>")
  16.     Response.Write("<tr><td colspan='2'><pre>Product</pre></td><td>Quantity</td><td>Price</td></tr>")
  17.     Response.Write("<tr><td colspan='2'>" & rsCustomers("Product_Name").value & "</td><td>" & rsCustomers("Order_Quantity").value & "</td><td>" & rsCustomers("Product_Price").value & "</td></tr>")
  18.     Response.Write("</table>")
  19. %> 
  20.  
Hope this helps,
CroCrew~
Oct 9 '09 #8
puT3
145 100+
it helps, thanks for letting me know the error...
Oct 10 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Bilal Iqbal | last post: by
49 posts views Thread by Yannick Turgeon | last post: by
55 posts views Thread by AnandaSim | last post: by
6 posts views Thread by Sisnaz | last post: by
7 posts views Thread by Allison | last post: by
9 posts views Thread by Classic-Car-World Ltd | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.