Hi all,
I have a requirement to add a checkbox to a datagrid, but on some rows.
If I am asking something that's already covered elsewhere, I apologise.
I have tried a couple of things, but not go
Please find below the code of what I am trying to do (flames welcomed ).
I would greatly appreciate feedback on this.
<br />
<br />
Private Function fnPopulateDataGrid()<br />
Try<br />
' Define a dataset the is used to store the retrieved data from SQL<br />
' This dataset is going to be used to hold multiple result sets.<br />
' These result sets are:<br />
' * Country<br />
' * Region<br />
' * Dates - From the diary<br />
' * Merchandisers<br />
' * Account details and Merchandiser claim details<br />
Dim oDS As DataSet<br />
'<br />
' This is the data table used to 'massage' the result sets for output.<br />
' It is our generic 'container' used for the purpose of our results which<br />
' we WANT to have programatic control over.<br />
Dim oTable As DataTable = New DataTable("RESULTS")<br />
'<br />
' Set up columns in the results table (oTable)<br />
' Note how we actually define the column header, and the data type the column will<br />
' be utilising. We could have avoided this, but for type save conversions<br />
' it is much more safer to have explicite types (as we do in database fields for tables).<br />
oTable.Columns.Add("Country", GetType(System.String))<br />
oTable.Columns.Add("Region", GetType(System.String))<br />
oTable.Columns.Add("Diary Date", GetType(System.String))<br />
oTable.Columns.Add("Merchandiser", GetType(System.String))<br />
oTable.Columns.Add("Customer", GetType(System.String))<br />
oTable.Columns.Add("Claim Time", GetType(System.String))<br />
oTable.Columns.Add("Claim Kms", GetType(System.Int32))<br />
oTable.Columns.Add("Approved", GetType(System.Web.UI.WebControls.CheckBox))<br />
'<br />
' New instance of a check box for the approve facilitation.<br />
Dim oCheckBox As CheckBox = New CheckBox<br />
oCheckBox.Visible = True<br />
oCheckBox.Enabled = True<br />
'<br />
' Here is defined each table to be used to map to each result set<br />
' obtained from the data set<br />
Dim oCountryTable As DataTable = New DataTable("Countries")<br />
Dim oRegionTable As DataTable = New DataTable("Regions")<br />
Dim oDiaryDateTable As DataTable = New DataTable("DiaryDates")<br />
Dim oMerchandiserTable As DataTable = New DataTable("Merchandisers")<br />
Dim oCustomerTable As DataTable = New DataTable("Customers")<br />
'<br />
' And now we define our rows to be added into out data table for output<br />
' oRow is a 'generic' row used specifically for the output data table.<br />
' We use this row to populate each column (as defined above).<br />
Dim oRow As DataRow<br />
'<br />
' And now a row defined for each data table in our data set.<br />
' We want to have a data row defined so we can access the details, form<br />
' relationships and use for processing<br />
Dim oCountryRow As DataRow<br />
Dim oRegionRow As DataRow<br />
Dim oDiaryDateRow As DataRow<br />
Dim oMerchandiserRow As DataRow<br />
Dim oCustomerRow As DataRow<br />
'<br />
' The function called returns a DataSet<br />
' Lets call our business facade method to get us our mechandisers passing no parameters <br />
' at this point.<br />
' The parameters are obtained from within the function.<br />
' We eventually call sp_getMerchandiserClaimDetails stored procedure.<br />
' Note: We are using the Microsoft Data Access Blocks, which is a part of the Microsoft Enterprise Blocks<br />
' (Just so that it is a bit more repeatable, and robust - like java - haha).<br />
oDS = fnGetMerchHierData()<br />
'<br />
' Now we have our data set with multiple result sets, we want to store these results into<br />
' a table of their own.<br />
' In this case we want to have a country, Region, Date, Merchandiser and Customer table<br />
' so we can use more efficiently and cleanly going forward.<br />
' We can address each result set in the data set by referencing the index (as we haven't named them<br />
' for efficiency purposes. We name 'things' going forward for readability and supportability<br />
' purposes.<br />
' Once we have our addressed result set from our data set populated from the business facade,<br />
' we explicitely set it to our prior defined data tables so we can use them in an<br />
' object-relational fasshion (as per below).<br />
oCountryTable = oDS.Tables(0)<br />
oRegionTable = oDS.Tables(1)<br />
oDiaryDateTable = oDS.Tables(2)<br />
oMerchandiserTable = oDS.Tables(3)<br />
oCustomerTable = oDS.Tables(4)<br />
'<br />
' Now we define our relationships between our data tables.<br />
' Note how we define it to our Data Set, as it is the primary container for all the tables.<br />
' Note: a data set is an in memory data base (IMDB).<br />
' Note: We hare using 3 parameters in the .Relations.Add method as opposed to 4.<br />
' The 4th parameter is used to overide whether or not a constraint is formed or not.<br />
' By default (by excluding this parameter) it forces a constraint.<br />
' This has been intentionally designed this way to ensure we do not violate any<br />
' integrity rules, and also ensure we have correct data as we populate our data table.<br />
' Note: Because of the way we are forming these relation ships, we actually creating<br />
' Parent -> Child -> Sub-Child -> Sub-Sub-Child -> Sub-Sub-Sub-Child relations<br />
' in the following fasion:<br />
' Country -> Region -> Dates -> Merchandisers -> Accounts serviced and claim details<br />
' First establish a relationship between Country as our parent to child records, Regions.<br />
oDS.Relations.Add("Regions", oCountryTable.Columns("CountryID"), oRegionTable.Columns("CountryID"))<br />
'<br />
' Next, we define a relationship between Region as the parent to child records, Dates.<br />
oDS.Relations.Add("DiaryDates", oRegionTable.Columns("RegionID"), oDiaryDateTable.Columns("RegionID"))<br />
' Next, we define a relationship between Dates as the parent to chile records, Merchandisers.<br />
'oDS.Relations.Add("Merchandisers", oDiaryDateTable.Columns("DiaryDate"), oMerchandiserTable.Columns("DiaryDate"))<br />
oDS.Relations.Add("Merchandisers", oDiaryDateTable.Columns("DiaryRegionID"), oMerchandiserTable.Columns("DiaryRegionID"))<br />
' Finaly, we establish a relationship between Merchandisers as the parent to the child records, the accounts serviced.<br />
oDS.Relations.Add("Customers", oMerchandiserTable.Columns("DiaryID"), oCustomerTable.Columns("DiaryID"))<br />
'<br />
'<br />
' Now the hard yakka. Lets go through our data tables (related).<br />
' Because we now have a relationional IMDB, we can access this in a programatic fashion.<br />
' We start off with our 'base' table, being Countries, and access the child, sub-child records<br />
' as defined in our relationships above.<br />
' Note: For each 'dimension' specified we generically add a row to our data table (oRow within Table)<br />
' so we can accordingly add the dimension specific (column) to the row to represent the detail<br />
' required in a hierarchical fashion. So, in essence we have grouped data into their logical<br />
' relation.<br />
' We cascade 'down' the relations in the order of parent -> child definitions.<br />
' This is: Country -> Region -> Dates -> Merchandisers -> Accounts serviced and claim details.<br />
' Note : To obtain the child details from the parent form, we use the .GetChildRows(SubstituteTableNameHere).<br />
' This utilises the data tables defined above, the relations formed above them, and<br />
' we can be sure that the integrity is maintained by the rules upon forming the relations.<br />
For Each oCountryRow In oCountryTable.Rows<br />
oRow = oTable.NewRow()<br />
oRow("Country") = oCountryRow("Country")<br />
oTable.Rows.Add(oRow)<br />
For Each oRegionRow In oCountryRow.GetChildRows("Regions")<br />
oRow = oTable.NewRow()<br />
oRow("Region") = oRegionRow("Region")<br />
oTable.Rows.Add(oRow)<br />
For Each oDiaryDateRow In oRegionRow.GetChildRows("DiaryDates")<br />
oRow = oTable.NewRow()<br />
oRow("Diary Date") = oDiaryDateRow("DiaryDate")<br />
oTable.Rows.Add(oRow)<br />
For Each oMerchandiserRow In oDiaryDateRow.GetChildRows("Merchandisers")<br />
oRow = oTable.NewRow()<br />
oRow("Merchandiser") = oMerchandiserRow("Merchandiser")<br />
oTable.Rows.Add(oRow)<br />
For Each oCustomerRow In oMerchandiserRow.GetChildRows("Customers")<br />
oRow = oTable.NewRow()<br />
oRow("Customer") = oCustomerRow("Customer")<br />
oRow("Claim Time") = oCustomerRow("ClaimTime")<br />
oRow("Claim Kms") = oCustomerRow("ClaimKms")<br />
'<br />
' Add the Approved value as a checkbox control<br />
If oCustomerRow("Approved").GetType.ToString = "System.DBNull" Then<br />
oCheckBox.Checked = False<br />
Else<br />
oCheckBox.Checked = CBool(oCustomerRow("Approved"))<br />
End If<br />
oRow("Approved") = oCheckBox<br />
'<br />
oTable.Rows.Add(oRow)<br />
Next<br />
Next<br />
Next<br />
Next<br />
Next<br />
'<br />
' We now want to add our data table (oTable) to our result set (oDS) just incase we want to<br />
' further process any details within our data set (oDS).<br />
' so, we have our original result sets from our busines facade, and our 'processed'<br />
' data table (oTable) from this function.<br />
oDS.Tables.Add(oTable)<br />
'<br />
' We now define to our data grid (dgMerchandiserClaimData) the source of where it will be<br />
' getting it's data to present.<br />
' Because we have gone to the effort of 'processing' and generating our own data table (oTable),<br />
' we want to actually use this explicitly.<br />
' We have opted to specify .DefaultView which gets any defined customized<br />
' view of the table which may include a filtered view, or a cursor position. We haven't defined<br />
' any of these customisations for our purposed, but it gives us the ability to do so incase we do.<br />
dgMerchandiserClaimData.DataSource = oTable.DefaultView<br />
'<br />
' Add our checkbox to the grid :S<br />
'dgMerchandiserClaimData.Columns.Add(oApprovedColumn)<br />
'<br />
' Now we actually form the bind from the defined data source to the grid.<br />
dgMerchandiserClaimData.DataBind()<br />
<br />
Catch ex As Exception<br />
Throw New Exception(ex.Message, ex)<br />
End Try<br />
End Function<br />
<br />
Thank you and Kind Regards,
John Zantey
|