Need help!!!!!

[rgkimball]

Trouble updating data

I am completely frustrated… Trying to help a friend.



Here is what I have been trying to do.. I am a noob at visual studio.



I am setting up an inventory database using VB as the front end. I have setup an accounts table, product table. These I can view edit, delete, and ad records to fine.



I am trying to make an invoice. I have set up the following tables:



tblInvoice

InvoiceNum

Primary Key

AccountNum

Foreign Key For Accounts table

Date



Total

Total cost of items





tblInvDetails

Invoice Number

Foreign Key from tblinvoice

QtyOrdered

Number of Items ordered

Item description

From Product table

Item price

From Product Table

Total Cost

QtyOrdered * Item Price





So far I have a form that I can get Account information filled in from accounts, date Picker, item selector, invoice number.



I can pick an item select quantity click a view button to view the following in textbox:

Invoice Num QtyOrdered Product Item Price total item cost

















All is well with that here is where the problem comes. I want to create a button to add items to tblInvDetails.



Once I figure this out I will work on totaling the Total items cost and, updating the Tblinvoice info and printing an invoice.



I can not get past this until I figure out the update.



Please help…

 

[Schenz]

I think you may need to change some thinking on this.

I would recommend using a DataTable (or DataSet) to store the Line Items in, then once you have the order completed there you can submit the whole thing to the database as a transaction. This will allow you to maintain data integrety, and also rollback anything should some kind of error occur.

Also a quick note:

tblInvDetails:
You do not really need the Total Cost Column because you can either perform the calculation in you SQL Statement to get the data, or perform the calculation once you pull the data out of the database. It is duplicate data (you already have price and quantity, do you really need a column that multiplies them? - EG: SELECT qtyOrdered * ItemPrice as TotalCost from tblInvDetails)

I also question the need for a Description and Price column in the Invoice Details Table if the information is really coming from the Products Table. Just store the ProductID and pull the appropriate infor from that table by SQL.

tblInvoice:
Total Column is again in question. You already have this data available with the associated Invoice Detail Items.