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

yourParameter.Value = theValue

Finally

      con.Close()

End Try

'Setting the dataSource of the grid here.

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.

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.

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.

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)

'Setting the dataAdapter to the sqlCommand.

Dim da As New SqlDataAdapter(cmd)

Dim ds As New DataSet

Try

      da.Fill(ds)

Catch ex As Exception

Finally

      con.Close()

End Try

'Setting the dataSource of the grid here.

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

There is a problem with the AutoGenerateColumns feature.  When selected to true the GridView will populate, however, the GridView Column collection will not.

For example: With AutoGenerateColumns = true; GridView.Columns().Count will equal 0

So if you need to reference a column or need to know the count of  columns you can't.  I'm having to deal with this very issue in a web application I'm developing.  I need to control the width of the column according to displayed data and its wrap property.  The only way I could obtain this control was to build the bound fields dynamically as explained in the original posting.  Only then was I able to get the GridView Column collection to populate and work as though it was statically bound to a data control.

This is an example on the same line, if you want to give a custom header or formatting to dynamic gridview at run time.

http://www.vishwamohan.com/ShowArticle.aspx?ArticleID=25

might help:

http://www.codeproject.com/useritems/dynamic_Columns_in_Grid.asp

I'm not sure if the following will work for a GridView, but it works for a DataGrid:

If you'd rather not build the whole DataGrid by hand, prefering to let VS2005 do the grunt work with the drag'n'drop it does so well, but want to adjust a dataGridItem's attributes conditionally, you can do that as well.

For example, say you're displaying ProductName, UnitPrice and UnitsInStock from the Products table of the Northwind database in a DataGrid.  You want to change the text color of the cells to gray if the Discontinued field is true, and you want to change the background color of the item's row cells to red if the UnitsInStock is less than 10 as a visual cue to the operator to reorder more of those units.

So, with your datagrid (dgProducts) built on the web page into BoundColumns whose DataFields are ProductName, UnitPrice and UnitsInStock (in that order) and with Discontinued loaded into a fourth BoundColumn (hidden with Visible="false"), you can make those individual changes in the sub that loads your data, immediately after you bind the dataset to the grid's datasource, as follows:

dgProducts.DataSource = ds
dgProducts.DataBind()

Dim itm As DataGridItem
For Each itm In dgProducts
   If itm.Cells(3).Text = False Then
      itm.Cells(0).ForeColor = Drawing.Color.Gray
      itm.Cells(1).ForeColor = Drawing.Color.Gray
      itm.Cells(2).ForeColor = Drawing.Color.Gray
   End If
   If CInt(itm.Cells(2).Text) < 10 Then
       itm.Cells(2).BackColor = Drawing.Color.Red
   End If
Next

I don't know how useful that is to anyone else, but it sure helped me.  I'll have to play around and see if there's a way to do this with a GridView; so far I haven't found the DataView's equivalent to a DataGridItem.

Hi,

I am looking forward to build a complete gridview (with row style) as custom control .

e.g:

<asp:GridView ID="gridViewLoanDetails" runat="server" CssClass=Label AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" ForeColor="Black" GridLines="Vertical" AllowPaging="True" PageSize="30">

<Columns>

<asp:BoundField DataField="month" HeaderText="Month" />

<asp:BoundField DataField="scheduledBalance" HeaderText="Scheduled Balance" />

<asp:BoundField DataField="interest" HeaderText="Interest" />

<asp:BoundField DataField="principalPayed" HeaderText="Principal Payed" />

<asp:BoundField DataField="emi" HeaderText="EMI" />

<asp:BoundField DataField="apr" HeaderText="APR" />

</Columns>

<FooterStyle BackColor="#CCCCCC" />

<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />

<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />

<HeaderStyle BackColor="Black" Font-Bold="True" Font-Size="Small" ForeColor="White" />

<AlternatingRowStyle BackColor="#CCCCCC" />

</asp:GridView>

Thanks, 

Sorry its working