The following example demonstrates how to build a grid view dynamically at runtime.

 

' Get the connection string, In this case it's comming from the web.config file

Dim

strCon As String = System.Configuration.ConfigurationManager.ConnectionStrings("con").ConnectionString

Dim con As New SqlConnection(strCon)

Dim cmd As New SqlCommand("[SEL_Results]", con)

cmd.CommandType = Data.CommandType.StoredProcedure

Dim yourParameter As SqlParameter = cmd.Parameters.Add("@yourParameter", Data.SqlDbType.VarChar)

yourParameter.Value = theValue

'Setting the dataAdapter to the sqlCommand.

Dim da As New SqlDataAdapter(cmd)

Dim ds As New DataSet

Try

      con.open()

      da.Fill(ds)

Catch ex As Exception

Finally

      con.Close()

End Try

 

Dim dc As DataColumn

If ds.Tables.Count > 0 Then

      'Building all the columns in the table.

      For Each dc In ds.Tables(0).Columns

            Dim bField As New BoundField

            'Initalize the DataField value.

            bField.DataField = dc.ColumnName

            'Initialize the HeaderText field value.

            bField.HeaderText = dc.ColumnName

            'Add the newly created bound field to the GridView.

            grdView.Columns.Add(bField)

      Next

End If

'Setting the dataSource of the grid here.

grdView.DataSource = ds.Tables(0)

grdView.dataBind()

To add sorting and paging to the gridView take a look at this article written by StrongTypes:

http://forums.asp.net/thread/1177923.aspx

Enjoy!

 

Crouchin9Tiger:

The following example demonstrates how to build a grid view dynamically at runtime.

 

' Get the connection string, In this case it's comming from the web.config file

Dim

strCon As String = System.Configuration.ConfigurationManager.ConnectionStrings("con").ConnectionString

Dim con As New SqlConnection(strCon)

Dim cmd As New SqlCommand("[SEL_Results]", con)

cmd.CommandType = Data.CommandType.StoredProcedure

Dim yourParameter As SqlParameter = cmd.Parameters.Add("@yourParameter", Data.SqlDbType.VarChar)

yourParameter.Value = theValue

'Setting the dataAdapter to the sqlCommand.

Dim da As New SqlDataAdapter(cmd)

Dim ds As New DataSet

Try

      con.open()

      da.Fill(ds)

Catch ex As Exception

Finally

      con.Close()

End Try

 

Dim dc As DataColumn

If ds.Tables.Count > 0 Then

      'Building all the columns in the table.

      For Each dc In ds.Tables(0).Columns

            Dim bField As New BoundField

            'Initalize the DataField value.

            bField.DataField = dc.ColumnName

            'Initialize the HeaderText field value.

            bField.HeaderText = dc.ColumnName

            'Add the newly created bound field to the GridView.

            grdView.Columns.Add(bField)

      Next

End If

'Setting the dataSource of the grid here.

grdView.DataSource = ds.Tables(0)

grdView.dataBind()

To add sorting and paging to the gridView take a look at this article written by StrongTypes:

http://forums.asp.net/thread/1177923.aspx

Enjoy!

 

i dont get this sample? why not just pass the dataset to the gridview? why not just customize the view for the data?

this approach seems like alot of overhead to me.

When you say why not just pass the gridView a dataset do you mean in the design view?  

Giving it a bit more thought perhaps I should have quantified my reason behind building a dynamic grid view.

If you have more than one procedures filling the grid view. Like if you were going to build a generic grid you used in a user control that had a different stored procedure with a different amount of columns with earch stored procedure you passed or a search grid that took a basic and advanced stored procedure; In which case you would substitute the portion a the top of my example with something like this.

dim sSQL as string

If type = "basic" then

   sSQL = "EXEC basicSearch  " & txtSearch.Text 

else

   sSQL = "EXEC advancedSearch " & txtTitle.Text & "," & ddlStatus.SelectedValue ... and so on.

end if

dim cmd as new SqlCommand(sSQL, con)

 

I hope this clears up any confusion anyone might have had.

Crouchin9Tiger:

Giving it a bit more thought perhaps I should have quantified my reason behind building a dynamic grid view.

If you have more than one procedures filling the grid view. Like if you were going to build a generic grid you used in a user control that had a different stored procedure with a different amount of columns with earch stored procedure you passed or a search grid that took a basic and advanced stored procedure; In which case you would substitute the portion a the top of my example with something like this.

dim sSQL as string

If type = "basic" then

   sSQL = "EXEC basicSearch  " & txtSearch.Text 

else

   sSQL = "EXEC advancedSearch " & txtTitle.Text & "," & ddlStatus.SelectedValue ... and so on.

end if

dim cmd as new SqlCommand(sSQL, con)

 

I hope this clears up any confusion anyone might have had.

 

why not a basic gridview? then implement the Strategy-Factory Pattern combo to pass the necessary data to the grid since one your example you are not doing additional formatting to the data other than creating bound columns.

the data shouldnt be a requirement to implement a grid like your example since there is no formatting and because the gridview control can automatically adjust its columns bases on the data you pass to it.

That is interesting, but how well does it keep up with things liked editing, adding, and deleting rows?

I'm not sure what you mean by basic gridView and Strategy-Factory Patterns could you expain a little further?

vcsjones:

That is interesting, but how well does it keep up with things liked editing, adding, and deleting rows?

You would have to create the template columns at run-time as well. 

Crouchin9Tiger:

Giving it a bit more thought perhaps I should have quantified my reason behind building a dynamic grid view.

If you have more than one procedures filling the grid view. Like if you were going to build a generic grid you used in a user control that had a different stored procedure with a different amount of columns with earch stored procedure you passed or a search grid that took a basic and advanced stored procedure; In which case you would substitute the portion a the top of my example with something like this.

dim sSQL as string

If type = "basic" then

   sSQL = "EXEC basicSearch  " & txtSearch.Text 

else

   sSQL = "EXEC advancedSearch " & txtTitle.Text & "," & ddlStatus.SelectedValue ... and so on.

end if

dim cmd as new SqlCommand(sSQL, con)

 

I hope this clears up any confusion anyone might have had.

The GridView already supports a boolean property named "AutoGenerateColumns".
When set to True, the GridView itself will automatically generate a grid column for each column in your datasource.

Completely forgot about that... That is a much easier way. Thanks for your input!

Based on mbanavige's feedback I have made an adjustment to the code I had earlier posted:

 

 Get the connection string, In this case it's comming from the web.config file

Dim strCon As String = System.Configuration.ConfigurationManager.ConnectionStrings("con").ConnectionString

Dim con As New SqlConnection(strCon)

dim sSQL as string

If type = "basic" then

   sSQL = "EXEC basicSearch  " & txtSearch.Text 

else

   sSQL = "EXEC advancedSearch " & txtTitle.Text & "," & ddlStatus.SelectedValue ... and so on.

end if

dim cmd as new SqlCommand(sSQL, con)

cmd.CommandType = Data.CommandType.StoredProcedure

'Setting the dataAdapter to the sqlCommand.

Dim da As New SqlDataAdapter(cmd)

Dim ds As New DataSet

Try

      con.open()

      da.Fill(ds)

Catch ex As Exception

Finally

      con.Close()

End Try

'Setting the dataSource of the grid here.

grdView.DataSource = ds.Tables(0)

grdView.AutoGenerateColumns = true

grdView.dataBind()

 

Much cleaner! Thanks again.

Great peice of code, How would I make these col editable? ie text box...

I will be writing a follow up in regards to that bit of functionality.  Just not sure when I will have the time yet.. 

lconley:

Great peice of code, How would I make these col editable? ie text box...

it's built-in...

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.autogenerateeditbutton.aspx