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
Rank 1
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
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 NextEnd SubDo not hesitate to contact us if you have further questions or issues.
Best wishes,
Julian Benkov
the Telerik team