Dynamic RadGrid From MS SQL

[M Patrick]

I had to create a table from a MS SQL statement. The table was a product listing whose Items could be added to a shopping cart.

The table creation has to occur in the Page Init event. You should read elsewhere as to why this is a requirement.

 

Imports Telerik.Web.UI
Imports System.Data.SqlClient


Private Sub wfTelerikGrid_Init(sender As Object, e As EventArgs) Handles Me.Init
        DefineGridStructure()
End Sub

Private Sub DefineGridStructure()
        Dim grid As New RadGrid()
        grid.ID = "RadGrid1"
        Dim CoConn As New System.Data.SqlClient.SqlConnection
        Dim ParmString As String = String.Empty

        CoConn.ConnectionString = PublicVariables.WSConnInfoStringP
        ParmString = "SELECT IDNo, ItemNo, ItemDesc, Points, '' as Qty FROM WSProductDtl where WSProductIDNo = 5 "
        '
        Dim dataadapter As New SqlDataAdapter(ParmString, CoConn)
        dataadapter.SelectCommand.Parameters.Clear()
        dataadapter.SelectCommand.Parameters.AddWithValue("@IDNo", 5)
        'dataadapter.SelectCommand.Parameters.AddWithValue("@Line_No", LineNoInteger)
        'dataadapter.SelectCommand.Parameters.AddWithValue("@userID", Environment.MachineName.ToString.Trim.ToUpper & "\" & Main.GetUserName)
        Dim dt As New DataTable
        '
        CoConn.Open()
        dataadapter.Fill(dt)
        CoConn.Close()
        CoConn.Dispose()
        '
        grid.DataSource = dt


        'grid.DataSourceID = "SqlDataSource1"

        'grid.Skin = "Vista"
        grid.Width = Unit.Percentage(100)
        grid.PageSize = 15
        grid.AllowPaging = True
        grid.PagerStyle.Mode = GridPagerMode.NextPrevAndNumeric
        grid.AutoGenerateColumns = False

        'Add Customers table
        grid.MasterTableView.Width = Unit.Percentage(50)
        grid.MasterTableView.DataKeyNames = New String() {"IDNo"}
        Dim boundColumn As New GridBoundColumn()
        boundColumn.DataField = "IDNo"
        boundColumn.HeaderText = "WSProductDtl IDNo"
        boundColumn.Visible = False
        grid.MasterTableView.Columns.Add(boundColumn)
        boundColumn = New GridBoundColumn()
        boundColumn.DataField = "ItemNo"
        boundColumn.HeaderText = "Item No"
        boundColumn.HeaderStyle.Width = Unit.Percentage(20)
        boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Left
        grid.MasterTableView.Columns.Add(boundColumn)
        boundColumn = New GridBoundColumn
        boundColumn.DataField = "ItemDesc"
        boundColumn.HeaderText = "Item Description"
        boundColumn.HeaderStyle.Width = Unit.Percentage(50)
        boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Left
        grid.MasterTableView.Columns.Add(boundColumn)
        boundColumn = New GridBoundColumn
        boundColumn.DataField = "Points"
        boundColumn.HeaderText = "Points"
        boundColumn.HeaderStyle.Width = Unit.Percentage(15)
        boundColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Right
        boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right
        grid.MasterTableView.Columns.Add(boundColumn)
        Dim templateColumnName As String = "Quantity"
        Dim templateColumn As New GridTemplateColumn()
        templateColumn.ItemTemplate = New MyTemplate(templateColumnName)
        templateColumn.HeaderText = templateColumnName
        templateColumn.HeaderStyle.Width = Unit.Percentage(15)
        templateColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Right
        templateColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right
        grid.MasterTableView.Columns.Add(templateColumn)
        ' Add the grid to the placeholder
        Me.PlaceHolder1.Controls.Add(grid)
    End Sub

Private Class MyTemplate
        Implements ITemplate
        Protected textBox As TextBox
        Private colname As String
        Public Sub New(ByVal cName As String)
            colname = cName
   End Sub
   Public Sub InstantiateIn(ByVal container As System.Web.UI.Control) Implements ITemplate.InstantiateIn
            textBox = New TextBox()
            textBox.ID = "QtyTextBox"
            container.Controls.Add(textBox)
   End Sub
End Class

 One the table was created, I needed to step through the rows and read various cell values. Below is the code to do that.

Private Sub SubmitButton_Click(sender As Object, e As EventArgs) Handles SubmitButton.Click

        Dim RadGrid1 As RadGrid = CType(PlaceHolder1.FindControl("RadGrid1"), RadGrid)

        If Not (RadGrid1 Is Nothing) Then

            For Each item As GridDataItem In RadGrid1.MasterTableView.Items
                Dim QtyTextBox As TextBox = item.FindControl("QtyTextBox")
                Dim QtyString As String = QtyTextBox.Text
                Dim cell As TableCell = DirectCast(item("ItemNo"), TableCell)
                Dim ItemNoString As String = cell.Text
                Dim WSProductIDNoString As String = item.GetDataKeyValue("IDNo").ToString()
                '

            Next

        End If
End Sub

Perhaps I should have added  a Add to Cart button on every row. Since I didn't know how to do that I elected to use the single submit button.

I had a difficult time piecing this code together. I wanted to post it so it would be available in a single place. If there are more efficient ways of accomplishing this task, please feel free to comment.

 

 

 

 

[Eyup]

Hello Pat,

Thank you for sharing your approach with our community. I hope it may prove helpful to other developers as well.

Regards,
Eyup
Telerik