Question Datagridview with columns both bound/unbound, updating how not to affect unbound cols

[robtyketto]

Greetings,

I have a datagridview which is created progmatically and all but two of the columns (both DataGridViewCheckBoxColumns) are bound columns to a datatable.

One of the checkboxcolumns is used to select row(s) for the purpose of deletion, the code to delete the rows, removes the datarows from the database and the datagridview source datatable (See code below).

'Loop around each row in datagridview 
            'For Each booking As DataGridViewRow In DataGridView1.Rows
            For i As Integer = DataGridView1.Rows.Count - 1 To 0 Step -1

                Dim num As Integer = (From bookings As DataGridViewRow In DataGridView1.Rows _
                              Select bookings _
                              Where bookings.Cells("selectColumn").Value = "True").Count

                'Get bookings date and endtime to sue to check if booking is in past, present or future
                Dim bookingStartDateTime As New Date(DirectCast(DataGridView1.Rows(i).Cells("bookDate").Value, Date).Year, _
                                                   DirectCast(DataGridView1.Rows(i).Cells("bookDate").Value, Date).Month, _
                                                   DirectCast(DataGridView1.Rows(i).Cells("bookDate").Value, Date).Day, _
                                                   DirectCast(DataGridView1.Rows(i).Cells("startTime").Value, DateTime).Hour, _
                                                    DirectCast(DataGridView1.Rows(i).Cells("startTime").Value, DateTime).Minute, 0)

                'Is cell value of selected is TRUE and name the same as login
                If (DataGridView1.Rows(i).Cells("name").Value.Equals(bookersName) AndAlso DataGridView1.Rows(i).Cells("selectColumn").Value = "True" _
                    AndAlso Date.Now < bookingStartDateTime) Then

                    'Remove from database
                    Dim deleteBooking As New bookingsSession
                    If deleteBooking.bookingDeletion(cboRoom.SelectedValue, DataGridView1.Rows(i).Cells("bookDate").Value, _
                            DataGridView1.Rows(i).Cells("startTime").Value, DataGridView1.Rows(i).Cells("endTime").Value) = True Then

                        'Remove booking from datagridview via datatable
                        bookingsdata.Tables("bookings").Rows.Remove(DirectCast(DataGridView1.Rows(i).DataBoundItem, DataRowView).Row)
                        bookingsdata.Tables("bookings").AcceptChanges()

                    End If

                ElseIf (Not DataGridView1.Rows(i).Cells("name").Value.Equals(bookersName) AndAlso DataGridView1.Rows(i).Cells("selectColumn").Value = "True") Then

                    'Highlight which bookings cannot be removed as not made by the user
                    MessageBox.Show("Warning: You are unable to delete the booking made on " _
                    & "the " & DirectCast(DataGridView1.Rows(i).Cells("bookDate").Value, DateTime).Date _
                     & " at " & CType(DataGridView1.Rows(i).Cells("starttime").Value, DateTime).ToString("HH:mm") _
                     & " to " & CType(DataGridView1.Rows(i).Cells("endtime").Value, DateTime).ToString("HH:mm") _
                     & " as booking was made by someone else.", "Warning: Unable to cancel booking", _
                    MessageBoxButtons.OK)

                    DataGridView1.Rows(i).Cells("selectColumn").Value = "False"
                ElseIf (DataGridView1.Rows(i).Cells("name").Value.Equals(bookersName) AndAlso DataGridView1.Rows(i).Cells("selectColumn").Value = "True" _
                    AndAlso Date.Now > bookingStartDateTime) Then
                    MessageBox.Show("Cannot delete a booking for one in the past")
                End If

            Next  'Retrieve next datgridviewrow for expection

The issue I'm having is where I remove a datarow and accept the datatable changes the unbound "select" column has all it's values reset to "false" when the datagridview refreshes.

One idea I had was to was to include the select column in the datatable as boolean, but as SQLServer database backend I believe it may be difficult to store as boolean datatypes don't exist.

Does anyone have some possible solutions to this problem?

Thanks
Rob

 

[cjard]

Make the column as a boolean in the datatable. ThHere is no requirement that a datatable has to look anything like the database table you linked it to. The column names can be different, and not all of the db columns have to be present in teh datatable, or vice versa

As an example
suppose you have a db table with 100 columns
And you write the SQL into a tableadapter(TA)
SELECT pk, name, age FROM massive_person_table
You get a DT with 3 columns, and a TA with INSERT/UPDATE query that affect just 3 columns
Then you add 10 more local columns for whatever purpose and their names are nothing like the db

The TA will ignore them all; the I/U queries still only reference 3 columns! Take a look at the PARAMETERS collection of the insert query.. see the SOURCECOLUMN property? That's how the TA knows what column to hit for the value to send to the DB - this is how we can totally unlink a DT from a DB

Good eh?