Avatar of jana
jana
Flag for United States of America asked on

Issue when manipulating tables in Microsoft SQL 2005

We have a VB Invoice application where the detail lines are unlimited.  Up to now the max of detail lines are a bit over 700 lines.  

Users can create an invoice, save it and later on another user modifies it.  One specific invoice can be worked by at least 4 user ate different stages.  But no 2 users can work with the same invoice.  However, at least 7 users have each an invoice working with it.

The problem we have is when user are saving the invoice.

We had issues of the process stops or takes too long, but most of the times, it just get uninterrupted and the user looses their entry that can be at least 20 to 50 lines.

So we are looking for EE insight on proper process of saving so the apps stops being interrupted or takes too long.
Microsoft SQL Server.NET ProgrammingVisual Basic.NETMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
David Todd

8/22/2022 - Mon
jana

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

What database?
adriankohws

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
jana

ASKER
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...
jana

ASKER
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.
ASKER CERTIFIED SOLUTION
David Todd

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jana

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jana

ASKER
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.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jana

ASKER
don't follow.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jana

ASKER
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)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jana

ASKER
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?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.