Update data in Grid on the basis of several tables

H

Horst Mayer

Hello,
I have this query:

SQLQuery = "SELECT P.Last_name ,P.first_name, z.Maßnahme,
confirmTasks.made,
confirmTasks.Wann,confirmTasks.Bemerkung,confirmTasks.Erstellt,Pp.id,p.patientID
,Br" & _
" FROM ((patients AS P INNER JOIN nursing AS
PP ON P.patientID = PP.patientID) " & _
" INNER JOIN ToDoTasks AS Z ON (Pp.id =
Z.id)) " & _
" INNER JOIN confirmTasks ON
(confirmTasks.TaskId = Z.TaskId)" & _
" Where confirmTasks.Vom = '" +
dtpDate.Value.Date + "'" & _
" AND confirmTasks.F = 1" & _
" and (confirmTasks.made = 0 or
confirmTasks.made is null)" & _
" AND P.disabled = 0" & _
" and (confirmTasks.Wann IS NULL)" & _
" ORDER BY P.Last_name ,P.first_name"

I let the data apper in a datagridview in this way:

Dim dt As New DataTable
Dim ds As New DataSet
Dim da As SqlDataAdapter
Dim bs As New BindingSource
Dim SQLQuery As String = ""

ds.Tables.Add(dt)
da = New SqlDataAdapter(SQLQuery, CONNECTIONSTRING)
da.Fill(dt)

Dim cb As New SqlCommandBuilder(da)
bs.DataSource = dt
Me.datagridview .DataSource = bs

Now it is unfortunately not possible to update the db through changed data
in the grid, because there are more tables in the query.
Which way to go? There are only 4 fields in one table (confirmTasks) that
should be updated after the user had made the changes clicking in the column
made.
Any ideas?
Horst
 
G

Gregory A. Beamer

Hello,
I have this query:

SQLQuery = "SELECT P.Last_name ,P.first_name, z.Maßnahme,
confirmTasks.made,
confirmTasks.Wann,confirmTasks.Bemerkung,confirmTasks.Erstellt,Pp.id,p ..
patientID ,Br" & _
" FROM ((patients AS P INNER JOIN
nursing AS
PP ON P.patientID = PP.patientID) " & _
" INNER JOIN ToDoTasks AS Z ON (Pp.id
=
Z.id)) " & _
" INNER JOIN confirmTasks ON
(confirmTasks.TaskId = Z.TaskId)" & _
" Where confirmTasks.Vom = '" +
dtpDate.Value.Date + "'" & _
" AND confirmTasks.F = 1" & _
" and (confirmTasks.made = 0 or
confirmTasks.made is null)" & _
" AND P.disabled = 0" & _
" and (confirmTasks.Wann IS NULL)" & _
" ORDER BY P.Last_name ,P.first_name"

I let the data apper in a datagridview in this way:

Dim dt As New DataTable
Dim ds As New DataSet
Dim da As SqlDataAdapter
Dim bs As New BindingSource
Dim SQLQuery As String = ""

ds.Tables.Add(dt)
da = New SqlDataAdapter(SQLQuery, CONNECTIONSTRING)
da.Fill(dt)

Dim cb As New SqlCommandBuilder(da)
bs.DataSource = dt
Me.datagridview .DataSource = bs

Now it is unfortunately not possible to update the db through changed
data in the grid, because there are more tables in the query.
Which way to go? There are only 4 fields in one table (confirmTasks)
that should be updated after the user had made the changes clicking in
the column made.
Any ideas?
Click to expand...

Rip the data input and update/insert in the tables affected. One way to
do this is to actually pull the data from all of the tables along with
the joined data. You can then relate back to the individual tables.

I have also seen pulling the keys from each table (if updating) and
having a custom sql statement for update for each table. You simply
"hide" the keys from the user (as they are not necessary for his/her
work).

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Top