Trouble converting Access database
-
A total newbie to SQL/ASP, I decided to check out the free shoppingcart at http://www.urlogy.com/asp/ashopkart.asp. I've got SQL Server up and running, but I ran into some problems converting the Access database to SQL.
The tables converted fine, but there are 4 queries that don't and I have no idea how to fix them
Hope someone can help me with this, here are the 4 queries I got out of the database:qryOrderInfo
SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems,
customers.*
FROM customers INNER JOIN (orders INNER JOIN (products INNER JOIN oitems ON
products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid) ON customers.custID =
orders.ocustomerid
qryOrders
SELECT products.*, orders.*, oitems.*, customers.*
FROM customers INNER JOIN (orders INNER JOIN (products INNER JOIN oitems ON
products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid) ON customers.custID =
qryProdsCategory
SELECT products.*, products.ccategory, categories.catdescription
FROM categories INNER JOIN products ON categories.categoryID = products.ccategory
WHERE (((products.ccategory)=[theCategory]));
qryProduct
SELECT products.*, products.catalogID, products.catalogID AS shownID
FROM products
WHERE (((products.catalogID)=[prodID]));
If someone can help me with this I'll be forever grateful.
-
CREATE PROC qryOrderInfo AS
SET NOCOUNT ON
SELECT orders.*,
products.ccode,
products.cname,
products.cprice,
oitems.numitems,
customers.*
FROM customers INNER JOIN orders
ON customers.custID = orders.ocustomerid
INNER JOIN oitems
ON orders.orderid=oitems.orderId
INNER JOIN products
ON oitems.catalogID = products.catalogID
qryOrders
CREATE PROC qryOrders AS
SET NOCOUNT ON
SELECT products.*,
orders.*,
oitems.*,
customers.*
FROM customers INNER JOIN orders
ON customers.custId =orders.custID
INNER JOIN oitems
ON orders.orderID = oitems.orderID
INNER JOIN products
ON oitems.catalogID = oitems.catalogid
qryProdsCategory
CREATE PROC qryProdsCategory @theCategory Int AS
SET NOCOUNT ON
SELECT products.*, products.ccategory, categories.catdescription
FROM categories INNER JOIN products
ON categories.categoryID = products.ccategory
WHERE products.ccategory=@theCategory]
qryProduct
CREATE PROC qryProduct @prodId Int AS
SET NOCOUNT ON
SELECT products.*,
products.catalogID AS shownID
FROM products
WHERE products.catalogID=@prodID
the [prodId] in the original access suggests that it is a parameter.
I have removed some of the fields because they were superfluous.
The SET NOCOUNT ON gives better performance.
Execute the stored procedures using the ADO COMMAND object.
In general, try to avoid SELECT table.* commands.
Prefix your table names with dbo. i.e. SELECT p.* FROM dbo.products AS P
I wouldn't try and do everything in one query as qryOrders is trying to do, but would have several separate queries to lighten the load.
Have fun
-
Thanks for the help!!
-
I hope that you understand that David.Poole transfered your Access queries to SQL Stored procedures and And Add parametes to it (so you send it) ..
search on how to call a Stored procedure that has parameters from ASP
Alamir Mohamed
Alamir_mohamed@yahoo.com -
Last night I ran into a new problem with the checkout page. Does anyone here have SQL Server? I've been posting in 4 different forums all day, and no one can come up with a solution. So if anyone here have SQL server, I can send you my files, and you'll have a look at it? Please, I'm getting pretty desperate here.
-
paste your problem here
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply