• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75855
  • Last Modified:

VB .NET DataGrid, DataView or DataSet for Column selection

Apologies if this appears as a duplicate question.  I thought I'd submitted it already, but my orginal question seems to have vanished.

I'd like to know how I should control which columns are displayed in my datagrid.  Is it at the DataGrid, DataView or DataSet level.

I believe it is at the DataGrid level, but am not sure how to achieve it.

I have a DataGrid1 with its datasource set to DataView1 which is in turn a view of DataSet1.  If DataView1 is the default view for DataSet1 that has ColA, ColB and ColC, how do I set up the DataGrid to just show ColA and ColC?
  • 4
  • 4
  • 3
  • +2
1 Solution
The easiest way is to do it in SQL when you ar ecreating a dataset

sSQL = "select colA, ColC from Table"

dg.DataSource = ds

Now you will have colA and ColC in your dataagrid

Another way of doing it is to hide columns

'we have cols 0-3 (4 columns). Show only the 1st col
      For i = 1 To 3
            dtUserProject.Columns(i).ColumnMapping = MappingType.Hidden
MarkNethercottAuthor Commented:

I thought the idea of DataViews was to give different views of the same DataTable so that you kept the load on the database server to a minimum.  

I'm comfortable with modifying the query, so that I can restrict the columns that I'm calling in the first place, but thanks for changing the DataTable properties, but would really like to achieve this at the DataView/DataGrid level.

In the meantime, I've found the following @ http://www.dotnet247.com/247reference/msgs/10/52769.aspx

You must first declare an new DataGridTableStyle object.
Then set the .Mappingname = your Datasets tablename.
Add the datagridtablestyle object to the grid by using
Declare a GridColumnStyleCollection
Set GridCSCObject = DataGrid.TableStyles(index of your created
Then you can add and remove columns by using
Ex: MyGridColumnStyleCollectionObject.RemoveAt(ColumnIndex) hides the column
at position ColumnIndex.

Hope u got the idea..

Fredrik Pradeep.k

I wonder if this is the best way to control it?
I think the best way is to create different dataviews using different SQL. If you don't have any other requirements you don't need to apply table styles. But that is a matter of taste. You can do it either way
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

As mentioned above its more a matter of style. I like to change the Command.Text properties of the Adapter object to send another SQL command that creates the exact view I need on the fly. This way only the data thats needed is held into memory and not the big load of data that you would normally hold in a dataset for viewing purposes. It also makes it easier when updating the dataset as well.

Just my $.02
I Agree with ScrptMasta
are you using visual studio?

If yes, if you do not want to do it at the SQL Query level, you can change the columns in the data grid.

just right click the datagrid -> property builder -> columns

uncheck "create columns automatically at run time"

then just add columns as needed, I am guessing that you will mostly just be using the bound column, make the "datafield" the column that you want the data to display.  You can also modify how the data looks from here.

And if you are adding a Hyperlink column you can specify one column as the text, and another as the data.

Try that
It is true only for WEB datagrid. There is no such thing for Win forms DG
iboutchkine is correct on a win form you have a couple of choices as to similar controls, personally I use a ListView or a TreeView.

Also I would like to add to the comment about SQL load performance. You have to understand that when using a DataSet w/ADO.NET you are working with "disconnected" data. The SQL connection is open just long enough to run the command, fill the set and then close. It doesn't hold an active connection open while you work on the data. Unless you’re loading tons of info into your DataSet I wouldn't worry too much about performance. If it is of most importance then consider using a stored view within SQL and calling that instead of sending a SQL command via the Adapter. You will need to use the Command.Type method instead of Command.Text if you go this route. However it’s what you should be doing if performance is your major concern.

Are you sure that you even need the use of more then the default view from the DataSet ? Meaning you have a DataSet and you need to create multiple DataTables or Views from the initial DataSet ? If not then you shouldn't be using a DataView as your DataSource.

You are aware that you can hold more then one table from your database within a DataSet correct? You will just need multiple adapter objects to create multiple tables in the DataSet and you will have to fill and update each one separately.
MarkNethercottAuthor Commented:
Thank you all for your comments.  

At the moment, the database is quite small - there are only about 30 tables with limited data that will grow to about 40 tables.  However, it's possible that some of the tables will get quite large  - 500,000 to 4,000,000 rows and I wanted to build the prototype using best practices and grapple with some of the more involved techniques that are required.  I can always simplify things once I've got the perspective of doing it the 'hard' way.  From what I'd read and tested, it seems that the most complicated way of working with data (in a disconnected manner) is;

SQL Data <-DataAdapter-> DataSet(s) <-DataView Manager-> DataView <-TableStyles-> DataGrid  (I know this is not 'technically' accurate but the sense is true)

And I was trying to work out how to get the last filter in the chain under control, which was why I wanted to understand 'how' to control the view at the DataGrid end of the chain.  Now that I've read about Table Styles and built some prototypes all is much clearer.  As always with .NET, it's the knowledge of the keywords that helps you navigate - and the 'hidden' suggestion was great.

I was aware of the approaches that have been suggested, but have changed my weighting of them based on the comments received.

I don't feel that my orginal question was answered, but the comments from iboutchkine & ScrptMasta have been really useful, so unless I hear otherwise, I was going to split the points evenly.

How you control you DataGrid column design will depend on how its setup from the begining. Are you binding at design time or run time ? Seeing as how there are also multiple ways of controlling the data to and from the grid, this might boil dowm to a matter of style as well.

What I do is consider things class by class or form by form. If you have a form where the databound controls are always going to be the same and will always show the same exact data then there is no reason not to bind your controls at design time. However if you know that the form contains controls which might show data one way ay times and then another way at others obviously you will need to bind them at run-time for flexability.

As far as how to control your columns see if any of these pages help.

DataGrid Columns

Creating Custom Columns for the ASP.NET Datagrid

DataGrid Control (Windows Forms)
Also here is the link to the complete System.Data namespace. If you read from the top of the class it may help to put all into perspective a little better.

System.Data Namespace
MarkNethercottAuthor Commented:
Thanks for the additonal comments ScrptMasta,

I found the following link the most useful of the ones you suggested;

Creating Custom Columns for the ASP.NET Datagrid

Which explains a lot of the background & logic as a basis for creating custom columns.  This is useful for using the columns generally.

The additional links that are referenced www.datagridgirl.com (which also references http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp#q708q) provide good additonal resources.
Already tried that?


Private Sub TestAndRemove(ByVal colToRemove As DataColumn)
    Dim cols As DataColumnCollection
    ' Get the DataColumnCollection from a DataTable in a DataSet.
    cols = DataSet1.Tables("Orders").Columns
    If cols.Contains(colToRemove.ColumnName) Then
    End If
 End Sub

This solved my problem, after I had found your question on my way to look for possible solutions.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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