Learn how to a build a cloud-first strategyRegister Now

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

Generate and Update Custom Sort

I have an Access database with table and list of products. I have a windows application (VS 2003 - VB.net) that I have a grid that loads the products from the table. The user wanted the ability to move the records in the grid up or down, basically creating a custom sort order and have this order loaded as the grid loads the products. For instance, always have product B load after product X and product W load in the grid after product B, etc. In order to achieve this, I created an additional table, called Inventory_Sort. In this Inventory_Sort table, I have a column for product name, customer id, and Sort number. When I load the grid, the sql joins the two tables on the product name and use Order By in the sql statement and load the grid by the Sort number. When the grid is loaded, i have two buttons, one for "Up" and "Down". These buttons allow to move the selector up or down and change which record is selected. After the "Up" or "Down" button is selected, I swap the Sort number in the database with the records that I am moving up or down. To assist in understanding, here is some sample code:

Private Sub cmdUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUp.Click
        '********************************************************************************************
        '* This swaps the sort number with the sort number of the product in the above record.                                                         *
        '********************************************************************************************

        Dim CurRow As Integer = grdProducts.Row
        'Verify the the first row is not selected
        If Not grdProducts.Row = 0 Then

            Dim LSortNo As Integer = grdProducts.Columns("Sort").CellValue(grdProducts.Row - 1)
            Dim LCustID As String = grdProducts.Columns("CUSTOMER_ID").CellValue(grdProducts.Row - 1)
            Dim LProdNo As String = grdProducts.Columns("Product_NO").CellValue(grdProducts.Row - 1)
           
            Dim HSortNO As Integer = grdProducts.Columns("Sort").CellValue(grdProducts.Row)
            Dim HCustID As String = grdProducts.Columns("CUSTOMER_ID").CellValue(grdProducts.Row)
            Dim HProdNo As String = grdProducts.Columns("Product_NO").CellValue(grdProducts.Row)
           
            Try
                Sort(LSortNo, HCustID, HProdNo)
                Sort(HSortNO, LCustID, LProdNo)

            Catch ex As Exception
                MessageBox.Show("Error sorting Products.", "Sort Error", MessageBoxButtons.OK)
            End Try
        End If
        End Sub


Private Sub Sort(ByVal Sort_Num As Integer, ByVal Cust_ID As String, ByVal Prod_No As String)
        '********************************************************************************************
        '* This code executes the sql statement that updates the sort orders.                                                                                    *
        '********************************************************************************************

        Dim cmdExecute As OleDbCommand
        Dim sqlString As String

        sqlString = "UPDATE INVENTORY_SORT SET Sort = '" & Sort_Num & _
         "' WHERE Customer_ID = '" & Cust_ID & _
         "' AND Product_No = '" & Prod_No & "'"
       
        cmdExecute = New OleDbCommand(sqlString, dbConnection)
        cmdExecute.ExecuteNonQuery()

    End Sub

I also have code for updating and deleting records from the Inventory_Sort table that are not in the Product table.

Private Sub InsertSortRecords(ByVal Cust_ID As String)
        '********************************************************************************************
        '* This code executes the sql statement that inserts the records in the Sort table the are                                                         *
        '* in the Product table and not in the Sort table.                                                                                                                   *
        '********************************************************************************************

        Dim cmdExecute As OleDbCommand
        Dim sqlString As String

        sqlString = "INSERT INTO Inventory_Sort ( PRODUCT_NO, CUSTOMER_ID ) " & _
                    "SELECT Product.PRODUCT_NO, Product.PACKAGE_CD, Product.CUSTOMER_ID " & _
                    "FROM Product LEFT JOIN Inventory_Sort ON (Product.PRODUCT_NO=Inventory_Sort.PRODUCT_NO) " & _
                    "AND (Product.CUSTOMER_ID=Inventory_Sort.CUSTOMER_ID) AND " & _
                    WHERE (((Inventory_Sort.CUSTOMER_ID) Is Null));"

        cmdExecute = New OleDbCommand(sqlString, dbConnection)
        cmdExecute.ExecuteNonQuery()

    End Sub

    Private Sub DeleteSortRecords(ByVal Cust_ID As String)
        '********************************************************************************************
        '* This code executes the sql statement that deletes the records in the sort table that are                                                        *
        '* not in the Product table.                                                                                                                                                 *
        '********************************************************************************************

        Dim cmdExecute As OleDbCommand
        Dim sqlString As String

        sqlString = "DELETE * FROM Inventory_Sort " & _
            "WHERE Not Exists " & _
            "(SELECT * FROM Product WHERE " & _
            "Inventory_Sort.CUSTOMER_ID = Product.CUSTOMER_ID " & _
            "AND     Inventory_Sort.PRODUCT_NO = Product.PRODUCT_NO)"
         
        cmdExecute = New OleDbCommand(sqlString, dbConnection)
        cmdExecute.ExecuteNonQuery()

    End Sub


Now, I have the Sort column (Sort Number) set to autonumber. I did this so the user would not have to create a sort number. And when the procedure was called to syncronize the tables, a number would be autogenerated. But this is a problem because I can't update an autonumber column. Does anyone know how to do this autonumber another way, or another approach. The fields have to be generated automatically and two products CAN'T have the same sort number, because this would be a problem when I switched the sort numbers. Any Help?
0
Gary2397
Asked:
Gary2397
  • 5
  • 4
  • 2
1 Solution
 
ctm5Commented:
The extreme approach would be to delete the entire table from the database and then recreate it.

A less extreme approach would be to remove the autonumber field and replace it with a number field. Rather than calling the Sort sub after every up or down click, I'd wait until the customer was done fiddling with the sort order and then just loop through everything. As you write out the sort table, you loop through the entries in the grid and auto-increment in the VB code to determine the sort number.

So the first row in the grid would end up having the sort number 0, no matter where it actually appeared in the Access table.

Here's some pseudo code to give you the idea:

For i As Integer = 0 To dgRows.Count - 1  
    sql = "UPDATE MySortTable SET SortNumber = " & i & " WHERE ProductID = " & dgRows.Row(i)
    myCmd.CommandText = sql
    myCmd.ExecuteNonQuery
Next

ctm5
0
 
Gary2397Author Commented:
I see what you are saying and this may be a better method. Now, how would you suggest that I assign sort numbers when I am syncronizing the tables, before the grid is loaded?

Thanks
0
 
SanclerCommented:
I've used an approach similar to that described by ctm5 in the past.  But with a few wrinkles.  First, I used a decimal for my SortNumber field.  Second, when I was re-saving the SortNumber after a user-session, I allocated 1 - not 0 - as the first SortNumber, so my equivalent code would be

    sql = "UPDATE MySortTable SET SortNumber = " & i+1 & " WHERE ProductID = " & dgRows.Row(i)

Third (and this is the reason for the first two) when, during the app's execution, a user changed the order of an item, rather than swapping numbers round I recalculated the new SortNumber for the moved item by taking the mid point between the one before it (or 0, if it was moved to the start of the list) and the one after it (or an arbitrary high number if it was moved to the end of the list).

As to how to "assign sort numbers when I am syncronizing the tables, before the grid is loaded", if I have understood the question aright, you just make a couple of changes to your Access table - removing any key constraint and changing the datatype to Long, or decimal - and load into your app as normal.  But then you save it using ctm5's code (or any appropriate variation on it).

Roger
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ctm5Commented:
I don't quite understand why you want to assign sort numbers when you are loading the grid. You just want to pull them out of the Access table in the proper order, so it all hinges on the SQL you use to do that. Something like:

SELECT * FROM Product INNER JOIN (Product.ProductID = MySortTable.ProductID) ORDER BY MySortTable.SortNumber

Then fill the grid by iterating through the rows returned by the SQL
0
 
Gary2397Author Commented:
I got it. I was just slow on figuring it out. I don't have to populate the sort number when syncronizing the tables. Only populate and save the sort numbers depending on the row the product is in.  Is this what your thinking.
0
 
ctm5Commented:
Yup.
0
 
Gary2397Author Commented:
Oh yeah, ctm5. You said this: "Rather than calling the Sort sub after every up or down click, I'd wait until the customer was done fiddling with the sort order and then just loop through everything." The reason that i call the update sql each time that the up or down button is clicked, is because I refresh and reload the grid. Without doing this, there is no way to show that the rows have been moved. Does this make sense? I don't know of a way to move records up or down in a grid without doing this.
0
 
ctm5Commented:
Calling the database each time to update and reload will be slow. You might consider making an array of the datagrid material that you can manipulate to repopulate the grid rather than going back and forth with the database.

To make the array, build a class named MyProduct, with a member for each item from the database (product_no, package_no, etc.) For each product that you pull from the database, put its info into an instance of the class. Build an array of those instances:
Dim AllMyProducts() as MyProduct

So you have, say, five items in the datagrid. You have five instances of MYProduct making up the array AllMyProducts. You can then address each member of each instance:

AllMyProducts(3).Product_no

Say the user wants to move Item 4 up to the third position. So you create another instance of MyProduct and copy Item 3 into it. Then you overwrite Item 3 in the array with Item 4, then copy the new instance back to Item 4:

Dim MySwapProduct as New MyProduct
MySwapProduct = AllMyProducts(3)
AllMyProducts(3) = AllMyProducts(4)
AllMyProducts(4) = MySwapProduct
MySwapProduct = Nothing

ctm5


0
 
Gary2397Author Commented:
I see your point about it being a slow process. And you have provided me with the solutions that I requested. And I appreciate it. But, wouldn't I have to update and reload the grid each time i click the button so the user can see on the grid that the record moved. If i didn't update, the record would not appear to move. And the user would be all messed up and think that it did not work. I am not displaying the sort number on the grid. I am just using it to Order By in the SQL statement. Without moving the record and updating each time the user clicks, It wouldn't appear to do anything. Do you agree with this.
0
 
SanclerCommented:
Try this.  A form with just two controls on - DataGrid called dg and Button1

    Dim dt As DataTable
    Dim dv As New DataView

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dt = MakeTable()
        dv = dt.DefaultView
        dv.Sort = "SortOrder"
        dg.DataSource = dv
    End Sub

    Private Function MakeTable() As DataTable
        Dim result As New DataTable
        Dim dc As New DataColumn
        dc.ColumnName = "Text"
        dc.DataType = GetType(String)
        result.Columns.Add(dc)
        Dim dc1 As New DataColumn
        dc1.ColumnName = "SortOrder"
        dc1.DataType = GetType(Decimal)
        result.Columns.Add(dc1)
        For i As Integer = 1 To 10
            Dim dr As DataRow = result.NewRow
            dr(0) = "Item" & i.ToString
            dr(1) = i
            result.Rows.Add(dr)
        Next
        Return result
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dt.Rows(0).Item("SortOrder") = 3.5
    End Sub

Very simple but it's just to illustrate that, with the right set up, if the sort order is changed, the grid automatically resorts to comply with the new order.

Roger
0
 
ctm5Commented:
Yes, you have to reload the datagrid. But you can reload from the AllMyProducts array, no?

ctm5
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now