This is a migrated thread and some comments may be shown as answers.

How to loop through each cell and set value?

2 Answers 283 Views
GridView
This is a migrated thread and some comments may be shown as answers.
James
Top achievements
Rank 1
James asked on 28 Jul 2011, 11:14 PM

I could use a little help if you can. I am new to Telerik and a relatively new programmer, so please bear with me as I try to explain my goal.

Essentially what I am trying to do is pivot records from a "View" in my database to produce a full year calendar using a radGridView as the container for the output. The radGridView is bound to a seperate SQL Server table. There are 12 records in the table and 33 columns.This table is only used as a template for the radGridView so all fields are null except for the two where I have set the month name and month #. The radGridView is to be used for visualization only and is not meant to allow for direct editing of records. The table I am using for the template has;
* 12 rows
    - One row for each month ( RowIndex + 1 = month of the year)
* 33 Columns
    - One column for the name of the month ( .ColumnIndex 0)
    - One column for each possible day of the month ( .ColumnIndex 1 - 31). The day columns are named 1, 2, ..... ,31 
    - The last column in the grid ( .ColumnIndex 32) has the month number. Used for sorting only.

What I am trying to do is;

  • Loop through each cell where the column index is between 1 and 31;
  • Query a view in my database (There is a date field in the View where I have parted the day, month and year  from the date into seperate columns)
  • Return a record, if it exists, where
    • day =  .ColumnIndex
    • month = Rowindex + 1
    • Year = Textbox2.Text
    • GPID = Textbox1.Text
    • Set the Cell value to the string returned from the database

The "View" I am querying has 6 columns; GPID, AttDate, aDay, aMonth, aYear, Incidents

The following code works except that it sets each cell value in the columns to the first matching record it finds in the database.

 

Private Sub radGridView1_CellFormatting()
        Dim selectedIndex As Integer = Me.RadGridView1.Rows.IndexOf(Me.RadGridView1.CurrentRow)
        Dim selectedcolumn As Integer = Me.RadGridView1.CurrentCell.ColumnIndex
        Dim EE As Integer
        Dim vbDay As Integer 'Day of the month, also the name of the column
        Dim vbMonth As Integer ' Month of the year, also row index + 1
        Dim vbYear As Integer 'Year being generated
        EE = CInt(TextBox1.Text) ' Employee id #
        vbYear = CInt(TextBox2.Text) 'Year for calendar
  
        For Each row As GridViewDataRowInfo In RadGridView1.Rows
            If Me.RadGridView1.CurrentCell.ColumnIndex > 0 and Me.RadGridView1.CurrentCell.ColumnIndex < 32 Then
                vbDay = selectedcolumn
                vbMonth = selectedIndex + 1
                row.Cells("1").Value = GetEntry(EE, vbDay, vbMonth, vbYear) 'Testing with only a couple of columns.
                row.Cells("2").Value = GetEntry(EE, vbDay, vbMonth, vbYear) 
                      ' ......
            End If
        Next
    End Sub
  
    Public Function GetEntry(ByVal EE As Integer, ByVal vbDay As Integer, ByVal vbMonth As Integer, ByVal vbYear As Integer)
        Dim CalEntry As String
        Dim connstring As String = ("Data Source=MyComputer\SQLEXPRESS;Initial Catalog=MAPPSQL;Persist Security Info=True;User ID=myuser;Password=mypass")
        Dim SelectSQLInc As String = ("Select Incidents From View_AttCombined Where (GPID like '" & EE & "') and (aDay like '" & vbDay & "') and (aMonth like '" & vbMonth & "')and (aYear like '" & vbYear & "')")
        Dim MySDA As New SqlDataAdapter
        Dim DBconn As New SqlConnection(connstring)
        Dim myCom As New SqlClient.SqlCommand(SelectSQLInc, DBconn)
        Dim MyDS As New DataSet
        DBconn.Open()
        MySDA.SelectCommand = myCom
        MySDA.Fill(MyDS)
        DBconn.Close()
        Try
            CalEntry = MyDS.Tables(0).Rows(0).Item(("Incidents").ToString)
        Catch ex As Exception
            CalEntry = "" 'Return nothing if no matching record is found.
        End Try
  
        Return CalEntry
  
    End Function

 
What would I change to loop through each cell one at the time and set the value to the value returned from GetEntry?

Thanks for the help. Let me know if I have totally confused you and need to clarify myself.

 

2 Answers, 1 is accepted

Sort by
0
James
Top achievements
Rank 1
answered on 29 Jul 2011, 02:20 PM
I figured it out. I Had a couple of issues to address.
The main problem was I was only looking up the value for the currently selected cell and then applying that value to all cells in the columns listed in my code.

The code below will loop through each cell in the range set and call a query to my database for each;
Private Sub radGridView1_CellFormatting()
    Dim EE As Integer
    Dim vbDay As Integer 'Day of the month, also the name of the column
    Dim vbMonth As Integer ' Month of the year, also row index + 1
    Dim vbYear As Integer 'Year being generated
    EE = CInt(TextBox1.Text) ' Employee id #
    vbYear = CInt(TextBox2.Text) 'Year for calendar
    
    For Each rowInfo As GridViewRowInfo In RadGridView1.Rows
        For Each cellInfo As GridViewCellInfo In rowInfo.Cells
            If cellInfo.ColumnInfo.Index > 0 And cellInfo.ColumnInfo.Index < 32 Then
                vbDay = CInt(cellInfo.ColumnInfo.Name)
                vbMonth = CInt(cellInfo.RowInfo.Index + 1)
                cellInfo.Value = GetEntry(EE, vbDay, vbMonth, vbYear)
            End If
        Next
    Next
End Sub
0
Julian Benkov
Telerik team
answered on 02 Aug 2011, 01:56 PM
Hi James,

I am glad to hear that you have solved your issue.

I have only one suggestion regarding the used API in your implementation. Please use the ChildRows collection instead of the Rows collection. The Rows collection presents raw source data without any data operation applied, while ChildRows presents the current view of rows with applied filtering, sorting, grouping. Please take this difference into consideration. Here is a modified version of your code snippet:

Private Sub radGridView1_CellFormatting()
    Dim EE As Integer
    Dim vbDay As Integer 'Day of the month, also the name of the column
    Dim vbMonth As Integer ' Month of the year, also row index + 1
    Dim vbYear As Integer 'Year being generated
    EE = CInt(TextBox1.Text) ' Employee id #
    vbYear = CInt(TextBox2.Text) 'Year for calendar
     
    For Each rowInfo As GridViewRowInfo In RadGridView1.ChildRows
        For Each cellInfo As GridViewCellInfo In rowInfo.Cells
            If cellInfo.ColumnInfo.Index > 0 And cellInfo.ColumnInfo.Index < 32 Then
                vbDay = CInt(cellInfo.ColumnInfo.Name)
                vbMonth = CInt(cellInfo.RowInfo.Index + 1)
                cellInfo.Value = GetEntry(EE, vbDay, vbMonth, vbYear)
            End If
        Next
    Next
End Sub

Do not hesitate to contact us if you have further questions or issues.

Best wishes,
Julian Benkov
the Telerik team
Explore the entire Telerik portfolio by downloading the Ultimate Collection trial package. Get now >>
Tags
GridView
Asked by
James
Top achievements
Rank 1
Answers by
James
Top achievements
Rank 1
Julian Benkov
Telerik team
Share this question
or