Solved

Repetitive Dataset Sorting

Posted on 2004-10-04
13
249 Views
Last Modified: 2010-04-23
I have a Dataset that draws everything from one table.  I'll call this dataset_test.

In dataset_test is the table "TestTBL"

In the TestTBL table are the columns "ColFirst", "ColLast", and "ColID"

I have 4 combo boxes.  One sits on top, the other three are below.  These three lower ones all occupy the same space.  The options in the first combo box are:
First Name, Last Name, Employee ID

Depending on which is chosen, one of the three lower boxes is made visible (and the other two invisible).  These three are directly datasource and datamember'd to ColFirst, ColLast, and ColID in the TestTBL table in the dataset_test dataset.  However, each time one of the three options is chosen in the first combo box, I want the newly visible combo box to be sorted alphabetically, such that when last name is chosen, the last names appear in the lower combo box alphabetically (the default), when First Name is chosen, rather than they appear in the same order as the last names (due to the databindings), the dataset is reordered for first names, and the same for Employee IDs.

How do I do this?

JP
0
Comment
Question by:gleznov
  • 7
  • 6
13 Comments
 
LVL 4

Expert Comment

by:eozz_2000
ID: 12221592
Use a dataView to the table and you can set the Sort property according to the comboBoxes.
0
 

Author Comment

by:gleznov
ID: 12225564
any example code?

JP
0
 

Author Comment

by:gleznov
ID: 12225603
I made a dataview using the GUI controls and used the properties to set it to my table.  But  what code will cause it to sort by a certain column?

JP
0
 
LVL 4

Expert Comment

by:eozz_2000
ID: 12226666
In sort property of the dataView, put something like this:

"Name DESC", to order by name in descending order
0
 

Author Comment

by:gleznov
ID: 12226708
OK, maybe step me through this whole process and give me some code?  I'm just not getting it.

JP
0
 

Author Comment

by:gleznov
ID: 12229557
Anybody?

JP
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 4

Expert Comment

by:eozz_2000
ID: 12232523
In this example I have tree radio buttons: rbProductName, rbProductID and rbUnitsInStock, one datagrid, one dataAdapter (daProducts), and one dataSet (dsGeneral), I have worked with the Products table of Northwind (SQL Server):

Dim dv As DataView

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  Me.daProducts.Fill(Me.dsGeneral.Products)
  dv = New DataView(Me.dsGeneral.Products)
  With dv
    .AllowDelete = False
    .AllowEdit = False
    .AllowNew = False
  End With
  Me.DataGrid1.DataSource = dv
End Sub

Private Sub rbProductName_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbProductName.CheckedChanged
  If Me.rbProductName.Checked Then
    Me.dv.Sort = "ProductName ASC"
  End If
End Sub

Private Sub rbProductID_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbProductID.CheckedChanged
  If Me.rbProductID.Checked Then
    Me.dv.Sort = "ProductID ASC"
  End If
End Sub

Private Sub rbUnitsInStock_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbUnitsInStock.CheckedChanged
  If Me.rbUnitsInStock.Checked Then
    Me.dv.Sort = "UnitsInStock ASC"
  End If
End Sub
0
 
LVL 4

Expert Comment

by:eozz_2000
ID: 12232526
Sorry because I answer to late but I went to work, I hope this could help you :)
0
 

Author Comment

by:gleznov
ID: 12236511
I feel like I'm close now, but something's not clicking.  Here's what I've got:

In the open area where you define public variables, I have:

    Public dv As DataView


In my form_load routine I have:

        Dim da As New SqlClient.SqlDataAdapter("select * from Custodians order by last_name", SqlConnection1)
        da.Fill(DataSet_Cust1, "Custodians")

        ' Setup Dataview
        dv = New DataView(DataSet_Cust1.Custodians)
        With dv
            .AllowDelete = False
            .AllowEdit = False
            .AllowNew = False
        End With

        cmbSearch.Items.Add("First Name")
        cmbSearch.Items.Add("Last Name")
        cmbSearch.Items.Add("Employee ID")
        cmbSearch.SelectedIndex = 1
        cmbSearch.Refresh()


And finally, this sub is where I need the sorting done - when a different selection is made (First Name, Last Name, Employee ID), it shows the corresponding combo box.  

    Private Sub cmbSearch_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSearch.SelectedValueChanged
        If cmbSearch.Text = "First Name" Then
            cmbFirst.Visible = True
            cmbLast.Visible = False
            cmbID.Visible = False
            SOption = 0
            dv.Sort = "First_Name"
        ElseIf cmbSearch.Text = "Last Name" Then
            cmbFirst.Visible = False
            cmbLast.Visible = True
            cmbID.Visible = False
            SOption = 1
            dv.Sort = "Last_Name"
        Else
            cmbFirst.Visible = False
            cmbLast.Visible = False
            cmbID.Visible = True
            SOption = 2
            dv.Sort = "Employee_ID"
        End If
    End Sub


Finally, I bound each of those combo boxes to their respective field in the dataview's custodians table.

Still, when I switch to first name or employee ID, nothing's different - they're still in the same order of last_name asc.  

JP
0
 
LVL 4

Expert Comment

by:eozz_2000
ID: 12236898
let me try, I respond you at night :)
0
 
LVL 4

Accepted Solution

by:
eozz_2000 earned 500 total points
ID: 12244719
Well, I tried with this code and works:

    Public dv As DataView

    Private Sub frmDataView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim da As New SqlClient.SqlDataAdapter("select * from Custodians order by LastName", Me.conn)
        Try
            da.Fill(Me.dsGeneral.Custodians)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' Setup Dataview
        dv = New DataView(Me.dsGeneral.Custodians)
        With dv
            .AllowDelete = False
            .AllowEdit = False
            .AllowNew = False
        End With
        Me.DataGrid1.DataSource = Me.dv

        cmbSearch.Items.Add("First Name")
        cmbSearch.Items.Add("Last Name")
        cmbSearch.Items.Add("Employee ID")
        cmbSearch.SelectedIndex = 1
        cmbSearch.Refresh()
    End Sub
 
Private Sub cmbSearch_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSearch.SelectedValueChanged
  If cmbSearch.Text = "First Name" Then
    cmbFirst.Visible = True
    cmbLast.Visible = False
    cmbID.Visible = False
    'SOption = 0
    dv.Sort = "FirstName"
   ElseIf cmbSearch.Text = "Last Name" Then
     cmbFirst.Visible = False
     cmbLast.Visible = True
     cmbID.Visible = False
     'SOption = 1
     dv.Sort = "LastName"
   Else
     cmbFirst.Visible = False
     cmbLast.Visible = False
     cmbID.Visible = True
     'SOption = 2
     dv.Sort = "EmployeeID"
  End If
  Me.cmbFirst.DataSource = Me.dv
  Me.cmbFirst.DisplayMember = "FirstName"
  Me.cmbLast.DataSource = Me.dv
  Me.cmbLast.DisplayMember = "LastName"
  Me.cmbID.DataSource = Me.dv
  Me.cmbID.DisplayMember = "EmployeeID"
End Sub

My table is:

CREATE TABLE Custodians(
  FirstName NVARCHAR(20),
  LastName NVARCHAR(20),
  EmployeeID INT IDENTITY(1,1) PRIMARY KEY
)

And when I select a value in the cmbSearch the dataView is ordered like I want, and the values in the others combos is ordered too.

I hope this could help you :)
0
 
LVL 4

Expert Comment

by:eozz_2000
ID: 12245264
Where are you? :)
0
 

Author Comment

by:gleznov
ID: 12247874
Thanks eozz.  Unfortunately this morning my project has been turned on its heels and I have to redo development in ASP.NET or VB.NET webforms.  Thanks for the help!

JP
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now