how to create and fill some gridviews at runtime?

[MrSnrub]

I have a SQL query that returns about 5-10 records. For each one of these records I want to query another table using values in that row as parameters and create/populate a GridView. Can I create these GridViews and SQL connections programmatically in a for loop? I mean, I can't just plop ten GridViews and SQLConnections on my page at design time because I'm not certain how many rows will be returned in the first query. What should I do?

[Ribeyed]

Hi,

you can dynamically populate your GridView with loops no problem. Here is an example of creating and adding rows in your code behind:

Set a connection:

 Dim blnAlreadyOpen As Boolean
        Dim ex As Exception
        'Test to see if connection is already open
        Try
            'If connection open returns true, if false raises error
            blnAlreadyOpen = IIf(SQLCon.State = ConnectionState.Open, True, False)
        Catch
            'Error caught connection status set to closed
            blnAlreadyOpen = False
        End Try
        Try
            'If connection NOT open, open it.
            If blnAlreadyOpen = False Then
                'connect string

                SQLCon = New SqlConnection("Data Source=" & strServer & ";Initial Catalog=" & strDatabase & ";user id=" & strUsername & ";password=" & strPassword)
                SQLCon.Open()
            End If

            objThisCommand.CommandTimeout = 360
            objThisCommand.Connection = SQLCon

Populate a dataset with your data:

 Dim objUsernameParam As SqlParameter
        Dim objOrderParam As SqlParameter
        Dim objDataAdapter As New SqlDataAdapter
        Dim objDataset As New Data.DataSet("ENVELOPE")
        Try
            objDataset.Tables.Add(New DataTable("Table1"))
            ' Set the stored procedure to execute...
            objThisCommand.CommandText = "proc_StoredProcedure"
            objThisCommand.CommandType = CommandType.StoredProcedure
            ' Add the required parameter, clearing the list first...

            objThisCommand.Parameters.Clear()
            objUsernameParam = objThisCommand.Parameters.Add("@Param1", SqlDbType.VarChar, 20)
            objOrderParam = objThisCommand.Parameters.Add("@Param2", SqlDbType.VarChar, 20)
            objUsernameParam.Value = Param1
            objOrderParam.Value = Param2
            ' Get the data set by executing the stored procedure...
            objDataAdapter.SelectCommand = objThisCommand
            CloseConnection()
            objDataAdapter.Fill(objDataset.Tables("Table1"))

Now your data is in a datatable in a dataset. You can loop through the datatable and then either use that to run a further query or databind that to your GridView.

Dim TableRow As System.Web.UI.WebControls.TableRow
Dim TableCell As System.Web.UI.WebControls.TableCell
Dim DropDownList As System.Web.UI.WebControls.DropDownList

For each DataTableRow in  Dataset.Datatable(0).rows

'here you can either add to your grid or use the data to run another query. 

            TableCell = New System.Web.UI.WebControls.TableCell
            DropDownList = New System.Web.UI.WebControls.DropDownList
            DropDownList.ID = "ddl" 
            DropDownList.Font.Size = FontUnit.Point(7)
            DropDownList.TabIndex = 24
            TableCell.CssClass = "tblPCColumn"
            TableCell.Controls.Add(DropDownList)
            TableRow.Cells.Add(TableCell)

            TableCell = New System.Web.UI.WebControls.TableCell
            TextBox = New System.Web.UI.WebControls.TextBox
            TextBox.ID = "txtbox" 
            TextBox.Columns = 24
            TextBox.Font.Size = FontUnit.Point(7)
            TextBox.TabIndex = 25
            TextBox.MaxLength = 200
            TableCell.CssClass = "tblPCColumn"
            TableCell.Controls.Add(TextBox)
            TableRow.Cells.Add(TableCell)

             GridView.Rows.Add(TableRow)

Next

I know its raw code but its just to give you an idea on how to achieve this.


regards

Ribs