Question add items to the invoice
- Thread starter user name
- Start date
You need two separate tables: one for the invoice and one for the items in the invoice. The Invoice table would contain columns for all the values that appear just once per invoice, e.g. invoice number and customer reference. The InvoiceItem table would contain columns for all the values that appear once per item, e.g. product reference and quantity. The InvoiceItem table would also have a column for invoice ID, which is what relates a group of items to a specific invoice. There are examples of this type of relationship in database samples all over the web. Microsoft's own Adventureworks database samples includes such relations. Note that the relation between customers and invoices is basically the same.
Maybe you already knew this much and you just didn't explain yourself clearly. Do you already have the schema sorted and it's the actual saving of the data that's the issue? If so then the trick is that you need to save the parent record first so that the database can generate the ID, then you can put that ID into all the child records. You can do that manually if you want but, with a little bit of code, it can be done automatically for you.
Then you can simply use a single DataSet containing both DataTables and a DataRelation. When you add the parent record to the DataTable it will generate a temporary ID, which you can use in the child records. When you save the data, the parent data is saved first and the generated ID retrieved and updated in the parent table, which is automatically propagated to the child records before they are saved. Here's an example of how:
Retrieve Access AutoNumber Value After Insert
Maybe you already knew this much and you just didn't explain yourself clearly. Do you already have the schema sorted and it's the actual saving of the data that's the issue? If so then the trick is that you need to save the parent record first so that the database can generate the ID, then you can put that ID into all the child records. You can do that manually if you want but, with a little bit of code, it can be done automatically for you.
Then you can simply use a single DataSet containing both DataTables and a DataRelation. When you add the parent record to the DataTable it will generate a temporary ID, which you can use in the child records. When you save the data, the parent data is saved first and the generated ID retrieved and updated in the parent table, which is automatically propagated to the child records before they are saved. Here's an example of how:
Retrieve Access AutoNumber Value After Insert
You're obviously doing it wrong but as we have no idea what you're doing there's no way we can know what's wrong with it.
I apologise if I have been mistaken but, given the size of your attachment, I'm rather certain that it contained binary files, which is against forum rules, so I have deleted it. If you do want to attach a project then please ensure that you have deleted all the bin and obj folders first. That said, attaching a zipped project should be a last resort. Your first option should be to post the relevant code and only the relevant code, directly in your post, wrapped in the appropriate formatting tags, i.e. [xcode=vb]your code here[/xcode]. That's a lot easier for us than downloading an attachment, unzipping it, opening the project in VS and then trawling through it hoping to find where the problem might be. The easier you make it for us, the more likely we will help you this time and in the future.
ok sir
i used the followinf codes
1- add new invoice code
2- add new item code
3- delete invoice code
4- delete item code
5- print invoice code
6- save invoice code
sorry i am still learning
my problem is when i add a new invoice all old items can be seen in the datagrid and printed too
i used the followinf codes
1- add new invoice code
Me.Validate() Me.CustomerBindingSource.AddNew() Me.TableAdapterManager.UpdateAll(Me.InvoicedbDataSet).
2- add new item code
Me.Validate() Me.itemBindingSource.AddNew() Me.TableAdapterManager.UpdateAll(Me.InvoicedbDataSet).
3- delete invoice code
Me.Validate() Me.customerBindingSource.removecurrent() Me.TableAdapterManager.UpdateAll(Me.InvoicedbDataSet).
4- delete item code
Me.Validate() Me.itemBindingSource.removecurrent() Me.TableAdapterManager.UpdateAll(Me.InvoicedbDataSet).
5- print invoice code
Dim rpt As New invoicerpt
rpt.Load(Application.StartupPath & "\invoicerpt.rpt")
rpt.SetDataSource(Me.InvoicedbDataSet)
rpt.RecordSelectionFormula = "{customer.id}=" & IDTextBox.Text
print.CrystalReportViewer1.ReportSource = rpt
print.Show()
.6- save invoice code
On Error Resume Next Me.Validate() Me.ItemsBindingSource.EndEdit() Me.TableAdapterManager.UpdateAll(Me.InvoicedbDataSet) Me.Validate() Me.CustomerBindingSource.EndEdit() Me.TableAdapterManager.UpdateAll(Me.InvoicedbDataSet).
sorry i am still learning
my problem is when i add a new invoice all old items can be seen in the datagrid and printed too
You're going to have to provide more detail. What EXACTLY are you doing, what EXACTLY do you expect to happen and what EXACTLY does happen?
exactily i am TRYING to make simple invoice application on VB.net and Msaccess 2007
i did almost everything with you help but now i have 3 problems
1- when i click add invoice
it gives me a new ID but it keeps the previous invoice items and even when i print it comes on the report
and when i delete invoice it does not delete related items
the other problems i am trying to solve them
one with calculating total for all items and the other one appears in report
it shows all column and raws
i did almost everything with you help but now i have 3 problems
1- when i click add invoice
it gives me a new ID but it keeps the previous invoice items and even when i print it comes on the report
and when i delete invoice it does not delete related items
the other problems i am trying to solve them
one with calculating total for all items and the other one appears in report
it shows all column and raws
Similar threads
- Solved
- Question
- Question
