Adding multiple items to one Invoice

[DL1600]

Any ideas on how to add multiple items or transactions to one Invoice No? I do not have the Invoice No set as the primary key in the database because when I went to add a second line onto the invoice it says that I can not have a dup key, so I made the line no the primary key. But, I have to rig it to accept it. (See code). Also, the invoice no is always saying -1, how can I get the number to change so that I may finish this program.

Try
            Me.InvoicesBindingSource.EndEdit()
            Me.InvoicesTableAdapter.Update(Me.CloseoutsDataSet.Invoices)
            InvoicesBindingSource.AddNew()
            InvoiceNoTextBox.Text = Label1.Text
        Catch ex As Exception
            MessageBox.Show("No New Line Has Been Entered!")
        End Try

See? I have to rig it, but like I said, the Invoice No is not updating, it is always being saved as (-1) which is ticking me off.

[szlamany]

Usually in an invoicing system you have two tables - INVOICE HEADER and INVOICE DETAIL.

The HEADER has a primary key of INVOICE NUMBER.

The DETAIL can have a compound primary key - made of two fields - INVOICE NUMBER and LINE NUMBER.

Any values that are common to all lines (like vendor maybe) go into the HEADER record.

All values specific to a line go into the DETAIL record.

[DL1600]

Ah, I see, and how does the detail header link to the header? I am new to Relations? Is there a GUI way to do it rather then programmatically?

[szlamany]

The linking is through the fact that the invoice number is common between the "parent" header row and the "child" detail rows.

We don't use bound controls - so I'm unsure how to get multiple rows added with that method.

What is your backend database?

[DL1600]

Local Dataset (.sdf) is our back end database.

[szlamany]

That's MS SQL CE (compact edition) by the SDF extension you mention.

There are many limitations to CE - we use it for Pocket PC apps.

We don't do this - but I believe you can create and manage the database, tables and schema from within Visual Studio.

[DL1600]

Yes, I am aware of that, however, how would I update both Header and Details so that each table will reflect the information? I don't want Invoice No 1 (example) to reflect the details of Invoice No 2.

[szlamany]

We don't use bound controls here.

We would do:

Insert into InvHeader values (1,'abc',...)

And based on the success of that insert we would

Insert into InvDetail values (1,1,...)
Insert into InvDetail values (1,2,...)

One insert for each line.

We "code" all our SQL statements - INSERT's, UPDATE's and DELETE's based on what the user is doing in the GUI.

For SELECT's we would

Select x,y,z From InvHeader Left Join InvDetail on InvDetail.InvNo=InvHeader.InvNo

Again - I'm not familiar with bound controls so you'll have to wait for someone else to come along with more info.

[DL1600]

How come when I Add an Invoice the Invoice No is "-1" even when save is pressed? How do I fix that?