[RESOLVED] checkbox does not appear in datagrid

[ultimate_girl]

I need help with my checkbox. I am currently developing a program that lists all the data in the database and displays it in a datagrid. I plan to have a checkbox as the first column of the datagrid, if the box is checked, it means that that particular record has been edited. I looked over at some of threads available on this subject and it still doesn't work! All the data are displayed correctly but no checkbox!

What am I doing wrong?

Here's my code :

Public Sub PnF_Fill(ByVal dadaptPFCity As SqlClient.SqlDataAdapter, ByVal strFlag As String)

Try
Dim dgts_PnF As New DataGridTableStyle
Dim bcol1 As New DataGridBoolColumn
Dim acol1 As New DataGridTextBoxColumn
Dim acol2 As New DataGridTextBoxColumn
Dim dsPNF1 As New DataSet


dadaptPFCity.Fill(dsPNF1, "PLAN_N_FORECAST")
Dim dtPNF As New DataTable
Me.dg_Forecast.DataSource = dsPNF1.Tables("PLAN_N_FORECAST")
dtPNF = dsPNF1.Tables("PLAN_N_FORECAST")
dgts_PnF.MappingName = "PLAN_N_FORECAST"
bcol1.MappingName = "PLAN_N_FORECAST"
bcol1.HeaderText = "Add/Edit"
bcol1.Width = 40
dgts_PnF.GridColumnStyles.Add(bcol1)
acol1.MappingName = "Buss_Code"
acol1.HeaderText = "Business Line"
acol1.Width = 80
dgts_PnF.GridColumnStyles.Add(acol1)
acol2.MappingName = "Chan_Code"
acol2.HeaderText = "Currency Type"
acol2.Width = 80
dgts_PnF.GridColumnStyles.Add(acol2)

Me.dg_Forecast.TableStyles.Add(dgts_PnF)

[oreo]

this might help you. http://msdn.microsoft.com/vbasic/usi...stDataGrid.asp - shows the basics of the data grid control as well as inserting a checkbox in the datagrid.

[ultimate_girl]

this is what I don't get from all the examples that I see. From what I understand, MappingName is supposed to correspond to a particular column in the database right? Since I will be using the checkbox as sort of a flag on whether it has been edited or not, what do I assign the MappingName to?

[jmcilhinney]

Just add an extra column to your DataTable and map to that. I don't think you can show some bound data and some unbound, so that column needs to be part of the DataSource.

[ultimate_girl]

thanks jmcilhinney! uhmm...how do I do that?

[jmcilhinney]

DataTable.Columns.Add is overloaded. Look it up in the help and you'll find your answer.

[ultimate_girl]

thanks! it worked! but here are a couple more questions :

1. the default are all checked, how can I make it default to unchecked?
2. when I assigned dg_Forecast.Readonly = False, I was still unable to uncheck the checkboxes. How can I make that particular column editable?
3. since the datagrid is now editable, I want the user to be able to edit the existing rows, making the datagrid writeable enables the user to add rows to the datagrid. Is there a way to prevent that?

[jmcilhinney]

Your problem is that you don't actually want that column to be editable. What you want is for that field to be set in code whenever any other field in the same row is edited. I think you'll need to iterate over all the rows and set that field to False so that it displays as unchecked in the grid. You would then want to set it to True whenever the user edits that row.

I'm not 100% sure, but I believe that the AllowDelete, AllowEdit and AllowNew properties of the DataTable's DefaultView will determine whether you can delete, update or add rows in the grid.

[ultimate_girl]

hmm...so its better to have it automatically check itself if the user edits that row rather than have the user check the checkbox if he edits the row? because I was thinking: what if the user edits some of the rows but changes his mind about one of the row but forgets the original values, so instead of cancelling the whole thing, he could just uncheck the checkbox so that whatever changes he made for that row will not be saved while the rest of the checked rows will be saved, what do you think?

[ultimate_girl]

with regards to your solution for the checkbox being unchecked. can you give me a sample code on how to loop it so as to set the checkbox to false? thanks!

[jmcilhinney]

This is seeming less and less like a good idea to me. You shouldn't really be relying on the user to check a box to indicate that a row has been edited. What if they forget to check it and then the row doesn't get updated when they wanted it to. Also, it is supposed to be the RowState property of the DataRow object that controls whether how it gets dealt with when updating the database. Have you decided how you are going to use your checkbox column to override that behaviour? If you are going to have to uncheck the box for every row then all rows will become modified at that point. It is probably all possible but it will be messy and the words "ill advised" are coming more strongly to mind.

[ultimate_girl]

hmm...I see your point. I guess it is a bad idea to always assume that the user will act the way you expect them to. So you check the RowState to see whether that row has been edited or not?

[jmcilhinney]

Correct. It starts off as Unchanged, then becomes Modified for edited rows. There is also Added and Deleted, which are self-explanatory, and a few other values. If you want to revert a row to its original state you can call RejectChanges, so if a user wants to unedit a row you can let them select it and provide a button that will call RejectChanges on the selected row.

[ultimate_girl]

a more question...I know it no longer applies to the topic but what the hey..

1. is it possible to enable/disable selected columns in the datagrid?

[jmcilhinney]

I would think it would be possible through ColumnStyles but I've never done it so I don't know how.

[ultimate_girl]

the checkbox is turning to be a lot more trouble than i expected. i already made it to the part when I made a loop to uncheck the checkboxes, but now, whenever i try to click on the checkbox an error message about an inbound array pops up. anyway, this will be more on a design question. is there any way to determine if that particular datagrid row has been edited without resorting to the checkbox?

[jmcilhinney]

Like I said, use the RowState of the underlying DataRow. Keep in mind that each row in a DataGrid actually corresponds to a DataRowView object in the DataTable's DefaultView. You should be able to use the DataRowView's RowVersion property also, but it is a little confusing to read about and I've never used it before, so it might require a little bit of experimentation. I think that the RowVersion should be Current if the row has been edited and Original if it hasn't been. If you want to use the RowState property of the corresponding DataRow, assuming that you have bound the grid to a DataTable you would do something like this:

If DirectCast(Me.DataGrid1.DataSource, DataTable).DefaultView(Me.DataGrid1.CurrentRowIndex).Row.RowState = DataRowState.Modified Then
    MessageBox.Show("The current row has been edited.")
End If

[ultimate_girl]

thanks! it worked! now, is there anyway to call the original values of the datagrid just in case you changed your mind? assuming, you haven't saved the changes to the database yet.

[jmcilhinney]

See post #13.

[ultimate_girl]

oops sorry, what i really meant was is there any way (besides saving each cell to a variable) to sort of save the original value? i'm going to use it for the audit trail. Sort of a previous value changed to the current value.

[jmcilhinney]

The DataTable class has events. You can use these events to record which fields have changed and what values they have changed from and to. As for getting the original from a DataRow object on demand, I don't know of a simple method.

[ultimate_girl]

hmm..that's too bad...

one last question :

I've tried your suggestion with regards to DateRowState and then reused some code to update my database and I've been getting an error message : THERE IS NO ROW AT POSITION 0 around the < Dim editRow As DataRow = DsCurrConv1.Tables("CURRENCY_CONV").Rows(RowNum) > part, which is strange because there is a value in Row 0.

Here's my code :

Private Sub dg_Plan_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dg_Plan.CurrentCellChanged
RowNumP = Me.dg_Plan.CurrentRowIndex
End Sub

Private Sub btn_Save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Save.Click
:
:
Me.dg_Plan.CurrentRowIndex = 0
intRow = CType(Me.dg_Plan.DataSource, DataTable).Columns.Count
Do While dg_Plan.CurrentRowIndex <= intRow - 1 And boolFinish = False
RowNumP = dg_Plan.CurrentRowIndex
If DirectCast(Me.dg_Plan.DataSource, DataTable).DefaultView(Me.dg_Plan.CurrentRowIndex).Row.RowState = DataRowState.Modified Then
Dim editRow As DataRow = DsPnF1.Tables("PLAN_N_FORECAST").Rows(RowNumP)
editRow.BeginEdit()
:
:
end sub

[jmcilhinney]

As I've mentioned before, you shouldn't use the CurrentRowIndex of the grid as an index into the DataTable because the rows do not necessarily correspond. If you know for a fact that the grid has not been sorted or filtered then it is safe, but I'd recommend always getting the row via the DefaultView anyway just to be on the safe side. That way you won't get any nasty surprises if you ever do allow the grid to be sorted or filtered and forget to change that code.

As to your actual problem, if the table definitely contains rows then I have no explanation. It's time for some old fashioned debugging with breakpoints and the Watch window I think.

[ultimate_girl]

i have two datagrids that has data coming from only one table. DataGrid1 has data where plan_flag = "P" and the other plan_flag ="F", will DefaultView work with that?

[jmcilhinney]

When you say one table, do you mean a single DataTable object is acting as the DataSource for bith DataGrids, or you have filled two DataTables from the same database table using two queries?

[ultimate_girl]

one datatable two datagrids. I used a loop on this one, filling the first datagrid then the second one. is this wrong?

[jmcilhinney]

That goes some way to explaining a few things. I assumed that you had simply bound the DataGrid to the DataTable. Is there a particular reason you are not using data binding? Also, why is it that you need two grids?

[ultimate_girl]

I don't know how to use databinding I'm basically copying codes from books, sites, etc. The database has a flag column: P for Plans and F for Forecast. I have a datagrid containing Plans and the other Forecast.

[jmcilhinney]

I'd suggest that you use two queries to get two DataTables in a DataSet. The you simply assign the DataSet to the DataSource property of each DataGrid and set the DisplayMember property of each DataGrid to the correct table name and you're done. This is what I was talking about when I said that any changes you make in the grids will then be reflected in the bound tables. Once you done all your editing in the grids you simply call Update on the DataAdapters and all the changes are committed back to the database. Here's some basic sample code. If you choose to use it you can flesh it out a bit yourself.

Dim myConnection As New SqlConnection("connection string here")
Dim plansAdapter As New SqlDataAdapter("SQL query here", myConnection)
Dim forecastsAdapter As New SqlDataAdapter("SQL query here", myConnection)
Dim myDataSet as New DataSet
 
'Fill the two tables.
myConnection.Open()
plansAdapater.Fill(myDataSet, "Plans")
forecastsAdapter.Fill(myDataSet, "Forecasts")
myConnection.Close()
 
'Bind the tables to the appropriate grids.
Me.plansGrid.DataSource = myDataSet
Me.plansGrid.DisplayMember = "Plans"
Me.forecastsGrid.DataSource = myDataSet
Me.forecastsGrid.DisplayMember = "Forecasts"
 
'Do editing here...
 
'You would need to create the appropriate commands for the adapters, either yourself or using an SqlCommandBuilder.
 
'Commit changes back to the database.
myConnection.Open()
plansAdapater.Update(myDataSet, "Plans")
forecastsAdapter.Update(myDataSet, "Forecasts")
myConnection.Close()

That's basically all there is to it. If you want to use this method, and I suggest you do, you should have a look at the help topics for the DataSource and DisplayMember properties, as well as some general stuff on data binding.

[ultimate_girl]

I have a question...in the plansAdapter.Fill(myDataSet, "Plans"), "Plans" is the name of the database right? I am currently using one database for both plans and forecasts named "PLAN_N_FORECAST" will the code above work as well?

[ultimate_girl]

another thing...there was no DisplayMember option in the DataGrid property, will
"Me.dg_Plan.DataSource = dsPNF1.Tables("PLAN_N_FORECAST")" be fine?

[jmcilhinney]

To answer your first question, "Plan" refers to the TableName property of the DataTable that you want to Fill, which does not have to be the same as the name of the table in the database. You said you have two grids: one for plans and one for forecasts. It is therefore appropriate to have two DataTables named "Plans" and "Forecasts". You do not have to have added these DataTables to the DataSet yourself. If you specify a DataSet and a TableName when you call Fill, the DataTable will be created if one with that name does not already exist.

To answer your second question, it is actually the DataMember property you want. DisplayMember relates to controls like ComboBox, ListBox, etc. My apologies. Having said that, it is definitiely OK to bind to the DataTable directly, rather than through the DataSet.

[ultimate_girl]

am i right in assuming that i am going to use just one DataSet?

[ultimate_girl]

sorry to be probably asking you a simple question, but when do I incorporate the DataGridTable Style? is it before the DataSource? or after? or during? or what? am confused here because the datagrid column headers and stuff are not in effect.

[jmcilhinney]

Yes, you only need one DataSet. You actually don't need to use any DataSet at all if you don't want to. It doesn't really serve a purpose here except to house the DataTables. You could just declare two DataTables and use them independently of a DataSet, binding them directly to the DataGrids. That is actually what I would do myself in this case. I only use a DataSet if I have more than three DataTables or I need to include DataRelations. There several overloads of Fill, one of which takes a DataTable argument.

[jmcilhinney]

I've never used styles on a DataGrid so I can't speak from experience. I would think that you would apply the TableStyles after you Fill the DataTables but before you set the DataSource. Unless anybody tells you different, try that and see if it works. If it doesn't, you can always just move the whole block of code later easily enough.

[ultimate_girl]

I appreciate your help and patience with me...I just feel like I want to redo the whole thing but I don't have the time. That darn thing is due today. Anyway, as posted in a new thread, only one of the datagrid was able to effectively use the DataGridTableStyle. Is there are rule somewhere that I can only use a function once? Because I placed the setting of DataGridTableStyle in one function that outputs a DatagridTableStyle variable.

[ultimate_girl]

ok i figured out where i went wrong. my current problem now is where to place the Plan.Defaultview.allownew = false because it is currently not working....

[ultimate_girl]

I'm closing this thread now since it is no longer in relation to the thread title. Thanks for all the help jmcilhinney!

[jmcilhinney]

You may not need this anymore but for interest's sake at least, if you bind a DataGrid to a DataSet and then set the DataMember property of the DataGrid to a table name, you cannot use the AllowNew property of the table's DefaultView to prevent the user entering a new row in the grid. You can, however, if you bind directly to the DataTable itself.