October 5, 2007 at 1:20 pm
How do you fill out an order form?
there is an Order(OrderID, CustomerID, Subtotal, Tax, Total), Orderdetail(OrderID, ProductID, Qty, UnitPrice, ExtendedPrice)
How do I get those tow together in the same form, which can be called order, or invoice it doesn't matter as long as I can get them in the same form numberd like order 1, then order 2, ect. is it by stored procedure, or by ado.net, or both ? or is there anywhere I can find information? like a book? or website ?
For example;
Order no. 1, Customer 3,
Product 3, Qty 2, UnitPrice $20.00, ExtendedPrice $40.00
Product 2, Qty 3, UnitPrice $10.00, ExtendedPrice $30.00
Product 4, Qty 2, UnitPrice $5.00, ExtendedPrice $10.00 ---here I can be adding as many as needed
Subtotal $80.00
Tax $5.00
Total $85.00
October 9, 2007 at 7:37 am
Maybe I'm confused by the question, but are you looking for a SELECT statement? Something like:
SELECT o.OrderId
,o.Custermer
,d.ProductId
,d.Qty
,d.Price
,d.ExtendedPrice
FROM dbo.Order o
JOIN dbo.OrderDetail d
ON o.OrderId = d.OrderId
WHERE o.OrderId = 42
Like that? Please forgive me if I just read the question wrong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 10, 2007 at 6:21 pm
i don't know if you want to be able to print an invoice or not this is one way to do it.
1 add your two tables Orders with orderID,CustomerID,CustomerName,CustomerAddress,OrderDate and OrderDetail table with OrderDetailId,OrderID,ProductID,Description,Price,Qty,TaxRate.Then add three expression columns to the OrderDetails table SubTotal (Price*Qty),Tax (SubTotal*TaxRate/100),LineTotal (SubTotal+Tax).That will give you the totals for each line.
2 add another table for a lookup table with a table adapter call it invoice if you want use query builder to add the Orders and OrderDetail tables in the query and select the columns you want to use and at the end of the query add WHERE (CustomerID = @CustomerID) AND (OrderDate = @OrderDate)that will give you the paramaters for the query.After you do that add your expression columns as above + two more 1 TaxTotal (SUM Tax), 2 TotalExcTax (SUM SubTotal), 3 TotalIncTax (SUM LineTotal). Then drag and drop the CustomerID,CustomerName,CustomerName,OrderDate,TaxTotal,TotalExcTax,TotalIncTax on the form as textboxes and then drag and drop the invoice table on the form as a datagridview and remove the columns you used as textboxes.Now you all you have to do is type your customerID and orderdate in the toolbar that should have being add by visual studio to the form and press the button next to the textboxes and you should see all the details of that order with the totals in the textboxes you will have to format the textboxes to show currency $0.00.
There are lot of ways to do this the app i have made has 14 tables and does inventory and ledger for accounts in your case i would use 4 tables Customer for your customer details Orders ,OrderDetails and Products then you can use comboboxes and bind them to CustomerID and Product Description to show all that in the comboboxes.Thats the easy way to do it without writing any code.If you want to get in depth there is a lot of code to write in this sort of app.
hope this helps barry
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply