SQL datasource columns for dynamic gridview

[nowandever29]

All,

In VS 2008 I have a gridview attached to a SQL 2008 datasource. It works perfectly, of course. I have an area on the form to let the user select the columns that are shown. I've tried updating the select statement, but when the user removes columns they don't want, the gridview throws an error because it was expecting the columns in the datasource.

So I've tried dynamically removing all of the columns in the grid view, and adding them back in accorind to what the user selected, but it's asking for a "system.web.ui.webcontrols.datacontrolfield", but I can't figure out how to create a bound datacontrolfield to the data source.

Another way I thought of handling this is to simply hide the columns in the dataview, but I can't figure out how todo that either.

Any help would be greatly appreciated!

 

[ErnstJan]

Hi,

I use the following code in VS2005 to format a datagridview to the wishes of the user.

With dgvOverzicht
   .DataSource = dsGegevens.Tables("Overzicht")
   .AutoGenerateColumns = False
   .AllowUserToResizeColumns = True

   For Each varColumn As DataColumn In dsGegevens.Tables("Overzicht").Columns
      varRow = dsInstellingen.Tables("Instelling").Select("KolomNaam = '" & varColumn.ColumnName & "'")
      If varRow.Length > 0 Then
           With .Columns(varRow(0).Item("KolomNaam").ToString)
              .Visible = varRow(0).Item("Keuze")
              .ReadOnly = True
              .HeaderText = varRow(0).Item("KolomTitel")
              .DisplayIndex = varRow(0).Item("KolomLocatie")
              .Width = varRow(0).Item("KolomBreedte")
            End With
       End If
    Next

Table "Instelling" holds the fieldname and the settings for that kolom.

Hope this helps you to get your project to work.

Greetings,
Ernst Jan

 

[nowandever29]

That's just the kind of thing I need, but the problem is that in VS 2008, the datasource doesn't appear to expose a tables collection (why the heck not?!?!?)

So I need to know how to do it in VS 2008. But seriously, thank you ErnstJan!

 

[Sorwen]

I don't know what effect this has on the original data if you save back to the table, but to just remove a column you can do:

DataGridView1.Columns.RemoveAt(ColumnNumber)

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[nowandever29]

I found a solution for it.

I change the selectcommand on the SQLDataSource to get only the fields that the user selects and rerun the select statement.

I changed the definition of the gridview to autogenerate the columns, and took out the pre-defined columns. So after I rerun the select statement, I just run a databind on the gridview, and it re-generates the columns.

Thanks for everyone's help!