Issue when manipulating tables in Microsoft SQL 2005

Wait, we are going to include the process of savings and the errors that is occurring.

What database?

By looking at your question, I am assuming that you have a Header Invoice Table and a Detailed Invoice Table while one row of record is on the Header and unlimited on the Detailed Invoice Table, am I right?

Possibly you are using a button to hit save, and only you would attempt to update the database when this button is hit and that's why if something happens, all data lost.

So, what you have to do is to update database, whenever there's a new "Detailed" transaction going on instead of updating in bulk.

Sorry for the delay we want to give you guys the logic flow of the SELECT, UPDATES, DELETE.... will post it....

As for the tables, yes we have a header and details, but the problem occurs when multiple users updates the detail...

We almost there with info...

Here is the process the "save" button does.  Since it's a super long code, we tried to place a description of the logic flow and the areas where the tables are being worked with:

1. Veriffy status:
   - does a for/next thru lines >> exceute SQL statement(s): SELECT
2. Update screen secuence number:
   - does a for/next thru lines >> updates datagrid
3. Update Invoices Tables:
   - does a for/next thru lines >> exceute SQL statement(s): UPDATE/INSERT
4. Delete Invoice Lines process:
   - compares if to delete running for/next thru lines >> exceute SQL statement(s): SELECT
   - If delete, >> does a for/next thru lines >> exceute SQL statement(s): DELETE
5. Update screen secuence number:
   - does a for/next thru lines >> exceute SQL statement(s): UPDATE
6. Add Invoice Lines Process:
   - compare masters with invoice >> does a for/next thru lines >>  exceute SQL statement(s): SELECT
   - if add, >> does a for/next thru lines >> exceute SQL statement(s): INSERT
7. Update screen secuence number:
   - does a for/next thru lines >> exceute SQL statement(s): UPDATE
8. Update sub-lines of each lines >> does a for/next thru lines >> exceute SQL statement(s): SELECT/UPDATE/INSERT
9. Update Invoice on Screen >> does a for/next thru lines >> updates datagrid

Hope this helps to better advice us.

We are using a grid.  The grid represents the actual invoice details in the screen that the users add, modify or delete the lines they are working on.  The purpose of the grid is because when clicking the save button the routine has to go through each line to see if there are any modifications done in order to update the data within the grid  and also within the database.

Is interesting your comment "...grid control that can take all lines at once, and then save in one hit".  we are using the tool DevExpress for our grids. By what you're saying is there a way we "can take all lines at once, and then save in one hit"?

This is very interesting.  Can you give more info in this type of technique. Thank you

Regarding your other questions, we are using foreign key. I don't think we are using cluster or indexing.

Please advise.

Hi sorry for the delay.

Can you elaborate more on "...but getting SQL to do that at the set level will be a whole lot faster/smarter than doing it line-by-line."

We really would like to make it faster.

don't follow.

I think that is what the apps do, process the 3 statements at the "save" button (we'll check tomorrow).

Also we going try setup Table & some data sample.

Finally, you mention "better indexing strategy", can you elaborate a bit (seems this could be an option to go)

I don't think we have indexes.  

But how indexes can help when insert/update/delete?  I understand that index are great for searching and reports, but for updating table what is the benefit?