[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Is there a technique for inserting and deleting lines from an array in a fast manner in VB or SQL

We have a peculiar situation and we are trying different approach to solve it.  

We have 2 VB apps that is a invoice form with a Header (Clients or Vendors info), Details (inventory items to sell or purchase) & Footer (subtotal, discounts, tax, freight and Total transaction).   Both apps works exactly the same with difference that one for Sales Invoices for Clients and the other is for Purchase Order (PO) placed for Vendors (suppliers).

The problem that this presenting is when we delete or insert a line.  The apps has to recalculate in order to present the line correctly within the Detail Grid.

Is there a better way of working with this?
0
rayluvs
Asked:
rayluvs
  • 12
  • 8
4 Solutions
 
rayluvsAuthor Commented:
To be more specific, the VB is 2005 and the SQL server is 2005.  

The problem is in the Detail for of the Apps (we are looking for a VB algorithm or a SQL algorithm to help in the problem).

Any invoice or PO can have from 400 lines to 900+ lines.  Since we use a sequence number that has to appear on the actual Invoice or PO printing form, when we modify the Grid by deleting or inserting a line, the apps recalculate each lines so it can maintain the sequence (the sequence number is the exact Line value of the Grid).

For example, the Grid has 900 lines and we need to delete line 679:

-      … (lines above 1 - 675)
-      LineNo676, ItemNo, ProductName, Sequence676
-      LineNo677, ItemNo, ProductName, Sequence677
-      LineNo678, ItemNo, ProductName, Sequence678
-      LineNo679, ItemNo, ProductName, Sequence679  <-- line to delete
-      LineNo680, ItemNo, ProductName, Sequence680
-      LineNo681, ItemNo, ProductName, Sequence681
-      … (lines below 682 - 900)

Steps taken to execute modification of Grid:
      - First we determine the line to delete within the grid
      - We then delete that line
      - Then we recalculate from that forward the correct sequence (if not recalculated,
        it will display a missing sequence between 678 and 680)
      - Finally displayed on the Grid

So the problem is when we update the Grid, since the apps has to recalculate in order to present the line correctly within the Detail Grid, it takes a long time.

What method should we consider to optimize this type of update?

Thank you very much (I place the Algorithm zone since maybe the problem is within an algorithm to be consider).
0
 
CodeCruiserCommented:
Where does the sequence come from? Do you recalculate it in grid or in datatable? Datatable would be much quicker than grid.
0
 
rayluvsAuthor Commented:
The sequence is from same item within the detail of the invoice.  If the invoice has 100 lines, then the sequence is from 1 to 100.  The calculation of the sequence number happens in the grid.  That is why it take time to recalculate the grid.

We never thought of using database, how would that work?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CodeCruiserCommented:
You can use an in memory data table as the datasource of the grid. It should speed up the process as it does not have to worry about the UI.
0
 
rayluvsAuthor Commented:
You mean don't use a data grid and use an array?
0
 
CodeCruiserCommented:
No. Array is not same as DataTable. Datatable is the storage and DataGridView is the frontend to represent that stored data.
0
 
rayluvsAuthor Commented:
OK.  So you mean do the calculation in the Datatable , then pass the result to the DataGridView ?
0
 
CodeCruiserCommented:
Yup.
0
 
rayluvsAuthor Commented:
In order to fully unserstand, here would the logic flow of the  process proposed as we understand it:

1. First create a DataTable with the sequence number, same as displayed on
    the DataGridView, so we can have the  exact same lines in both DataGridView and
     DataTable.
2. When a User delete or inserts a new Line in the DataGridView, also do the delete or
    inserts a new Line in the DataTable.
4. Then recalculate the sequence column within the DataTable, from the point of
    insert/delete until the end of the Lines.
5. Finally, copy the sequence column to the DataGridView.

Is this correct?
0
 
CodeCruiserCommented:
No.

1) Populate the Datatable with appropriate data
2) Set it as data source of the grid
3) Any changes made to grid by user will automatically propagate to the datatable
4) Recalculate the sequence numbers in datatable and grid will automatically show the new sequence numbers.
0
 
rayluvsAuthor Commented:
I think it is like that.  We'll check today.

Based on your experience, using the steps recommended in a 900 lines, how long you think will take to recalculate sorting the sequence?  (just an estimate)
0
 
CodeCruiserCommented:
I think it would be much faster than current implementation.
0
 
rayluvsAuthor Commented:
Ok will try and let you know
0
 
rayluvsAuthor Commented:
Sorry for the delay.  We do have it that way: changes made to grid by user will automatically propagate to the datatable.

What we discovered is that even though the programmer has a datatable and a grid, it is not Binding the control to source.  When asked, the programmer states that when is binded, there are more validation to be done that when not binded.

Please provide your expertise opinion.
0
 
CodeCruiserCommented:
I would bind the grid to datatable and do all manipulation on datatable or would atleast try that.
0
 
rayluvsAuthor Commented:
Ok, even though it's a bit heavy in coding, we're going to try somehow extract part of the datatable and grid and try binding.

We know you don't have the info or details of our code, but any suggestion on how to go about it testing this recommendation?

Thanx
0
 
rayluvsAuthor Commented:
Our programmer left for vacation and wasn't able to try your suggestion.  Would it be possible that you may guide me into doing an actual binding the grid to datatable so we can test the manipulation on datatable so we can see the speed improves?
0
 
rayluvsAuthor Commented:
Thanx
0
 
rayluvsAuthor Commented:
CodeCruiser, just to give an update, it worked: bind the grid to datatable and do all manipulation on datatable.  We tested over 3000 lines and not only update data fast, we thik we can save the datatabel directly to the sql table (we're ther now,  but will place question for assistance).

Again thanx!
0
 
CodeCruiserCommented:
Glad it worked :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now