Selecting related data in multiple DataGrids when clicking a row

[prichards14]

I have spent many hours trying to solve what I thought was a trivial
problem concerning selecting data in DataGrid controls. I need to
select or somehow highlight data in several DataGrid controls when a
row is selected in one DataGrid. Here is a brief description of the
problem:

A portion of an application I am working on is used for scheduling
appointments between students and counsellors. This consists of one
form. The left side represents the schedule of a selected student.
There are five DataGrids, one for each day of the week, Monday through
Friday, arranged one above the other. The right side is similar,
except with five DataGrids for the selected counsellor. One student
may have appointments with multiple counsellors and one counsellor may
have appointments with multiple students. This form is supposed to
provide a simple method for scheduling appointments in blocks of time
that are free for the student and counsellor.

The form allows the user to enter start and end time in text boxes and
select days of the week by checking one or more checkboxes. When the
user clicks the Add button, one record is inserted in an Access
database for each day of the week and contains an autonumber primary
key, start time, end time, day of the week, student key, and counsellor
key. The grids are each populated with their own query that filters
for the day of the week it represents.

The DataGrids are populated by binding a DataSet populated by using the
Fill method of an ADO Connector. The student and counsellor DataGrids
currently contain columns for the autonumber primary key, start time,
and end time.

***What I need to do is allow the user to delete appointments.***

I want the user to select the time slot they want to delete by clicking
the row for that time slot on one of the ten DataGrids. The code
should then change the highlighting or selection of rows in all ten
DataGrids so that matching time slots are marked in some obvious way
for student and counsellor for each day of the week. If a DataGrid
does not contain the selected time slot, all selection or highlighting
for that DataGrid should be obviously removed. Once a time slot is
highlighted in all DataGrids, the user can click the Delete button to
delete all of the records from the Access database represented by the
selections in the DataGrids.

Here is a copy of my code as it stands:

Sub SynchronizeGridsConsumer(ByVal intGrid As Integer)
Dim dg(5) As DataGrid
Dim cm As CurrencyManager
Dim dv As DataView
Dim dr As DataRow
Dim ds As Object
Dim dm As String
Dim intIndexCtr As Integer
Dim intRowCtr As Integer
Dim strStart As String
Dim strEnd As String
Dim blnMatch As Boolean

dg(0) = DataGridMonday
dg(1) = DataGridTuesday
dg(2) = DataGridWednesday
dg(3) = DataGridThursday
dg(4) = DataGridFriday

cm = CType(Me.BindingContext(dg(intGrid).DataSource,
dg(intGrid).DataMember), CurrencyManager)
dv = CType(cm.List, DataView)
dr = dv.Item(cm.Position).Row
strStart = dr.Item(1)
strEnd = dr.Item(2)

For intIndexCtr = 0 To 4
cm = CType(Me.BindingContext(dg(intIndexCtr).DataSource,
dg(intIndexCtr).DataMember), CurrencyManager)
dv = CType(cm.List, DataView)

dg(intIndexCtr).Select(0)
dg(intIndexCtr).UnSelect(0)

blnMatch = False
intRowCtr = 0
Do While intRowCtr < dv.Count
dr = dv.Item(intRowCtr).Row

If dr.Item(1) = strStart And dr.Item(2) = strEnd Then
blnMatch = True
Exit Do
End If

intRowCtr = intRowCtr + 1
Loop

If blnMatch Then
ds = dg(intIndexCtr).DataSource
dm = dg(intIndexCtr).DataMember
dg(intIndexCtr).SetDataBinding(Nothing, Nothing)
dg(intIndexCtr).SetDataBinding(ds, dm)
dg(intIndexCtr).Select(intRowCtr)
cm.Position = intRowCtr
End If
Next
End Sub

I realize I am taking several unnecessary steps. I was desperate and
kept adding more and more code. The behavior is very erratic. It
seems I have to click twice within a row for anything to happen. In
other words, the first click of a row is always ignored. Also, it
seems to mostly work, but not perfectly. Sometimes the wrong rows are
highlited.

First of all, is there an easier or better way for me to handle this?
I really can't think of one. I think this is the way I would expect it
to work if I were the user. Secondly, what am I doing wrong? I keep
changing the code because I have been getting different kinds of "out
of bounds" errors.

I think I should be able to change the selected or highlighted row
through one of the following methods:
- Set the CurrentRowIndex of the DataGrid
- Use the Select() method of the DataGrid
- Set the Position property of the CurrencyManager

Each of these works some of the time, but not consistently. Sometimes
some of them produce errors.
Any help would be greatly appreciated.

Thanks,
-Pat

 

[Andrew L. Van Slaars]

Pat,
At the moment I don't have time o offer a complete solution, but I will
start off with some ideas that might help and get back to this if I have a
chance today or tomorrow.
First, before I forget... the out of bounds exceptions you are getting
may be because when you make your selection, it cannot find the related
record. First thing I would do would be to check the SQL and make sure your
are getting exactly the results you were expecting. If, for example you are
using an outer join where it should be an inner, you may have a record
available to select that does not have any related records. This may be what
you want, in which case no related items in the other grids would be
selected and you would have to check for and trap that error, or write some
code to avoid the error in the first place.
I would start off by making sure that my Primary and Foreign key fields
are included in all of the related grids. I would use a buttonColumn in the
grid to be selected and bind the commandArgument of the button to the key
field. You could use that to then set the selectedIndex value for the other
grids and set the formatting for selected items differently. This would give
you the seperation visually.
If I am way off track about what you are trying to do, I applogogize,
but I will see what I can come up with when I am not at work.
Hope this helps
-Andrew

 

[prichards14]

Andrew,
Thank you for your reply. I guess what is causing all my grief is
trying to "deselect" all rows in a datagrid. If I only "select" rows
in a datagrid by setting its CurrentRowIndex to a valid row number, I
have no out of bounds errors. The problem will occur if I try to
visually show a datagrid has no current row selected by setting
CurrentRowIndex to -1 or 9999 or some invalid value. When I do this,
the arrow symbol disappears from the grid showing no row is currently
selected. This is what I want. However, if I then try to select a row
by setting CurrentRowIndex to a valid row, I get an out of bounds
error. To prevent this from happening, I was trying to remove the data
binding from the DataGrid, and then rebind it and select the row. This
also has its own problems.

So, the part I am having trouble with is visually showing a row is not
selected. If I can show this another way, that would be an acceptable
solution.

Thanks,
-Pat