How to update datagrid when it displays lookup table values

[dbuchanan]

Hello,

(Windows forms - SQL Server)

I fill my datagrid with a stored procedure that includes relationships
to lookup tables so that users can see the values of the combobox
selections rather than the key value that are stored in the table.

It works well if the comboboxes are selected when the row is created.

I fails to update the lookup table values if the combobox values are
inserted at a later time or if the combobox selections are changed
later.

The only work-around I know is to clear the form, datset, datagrid and
reload everything.
Here is the stored procedure
\\
CREATE PROCEDURE
dbo.usp_047MSen _sel_ByDevi_Typ eConnVolt
(
@fkDevice char(36),
@cmVarchar06 varchar(50) --pkComponentId for Cylinder
)AS SET NOCOUNT ON;
SELECT
c.pkComponentId ,
c.fkDevice,
c.fkComponentTy pe,
c.ComponentDesc ription,
c.QuanSharingCo nfiguration,
c.QuanConfigure d,--
c.QuanActuation s,
c.cmVarchar01,
c.cmVarchar02,
c.cmVarchar03,
c.cmVarchar04,
c.cmVarchar05,
c.cmVarchar06,
c.cmNumeric01,
c.cmNumeric02,
c.cmNumeric03,
c.cmNumeric04,
c.cmNumeric05,
c.cmNumeric06,
c.cmNumeric07,
c.cmBit01,
c.cmBit02,
c.cmBit03,
c.cmBit04,
c.cmBit05,
c.cmBit06,
c.cmBit07,
c.cmBit08,
c.cmBit09,
c.cmBit10,
c.cmBit11,
c.cmSmallint01,
v.Voltage, '<<from lookup table
c.cmSmallint02,
c.cmSmallint03,
c.cmSmallint04,
st.SensorType, '<<from lookup table
c.cmSmallint05,
sc.SensorConnec tion, '<<from lookup table
c.cmUserNote,
c.cmCreatedOn,
c.cmCreatedBy,
c.cmEditedOn,
c.cmEditedBy,
c.cmrowversion
FROM tbl040Cmpt c
left outer JOIN lkp104Voltage v ON c.cmSmallint01 =
v.pkVoltageId
left outer JOIN lkp302SensorTyp e st ON c.cmSmallint04 =
st.pkSensorType Id
left outer JOIN lkp103SensorCon nection sc ON c.cmSmallint05 =
sc.pkSensorConn ectionId
WHERE
(c.fkDevice = @fkDevice)
-- fkDevice = 'fc063d9f-62f1-4905-913a-e41f2471f2a7'
AND c.cmVarchar06 = @cmVarchar06 -- pkComponentId - the
-- AND c.cmVarchar06 = 'c6236862-dc3d-4e9d-a688-f57809fcb348' --
pkComponentId - the
AND c.fkComponentTy pe = 7 -- Motion Sensor
ORDER BY c.ComponentDesc ription

GO
//

Here is the example of the update code
\\
Call UpdateSource2(d al.da040CmptFrm , "tbl040Cmpt ")
//

and

\\
Protected Sub UpdateSource2(B yVal dA As SqlDataAdapter, ByVal tbl As
String)

Dim dsDataChanges As New CLIP.dsTables

dsDataChanges = CType(_dataSet1 .GetChanges, CLIP.dsTables)

If (Not (dsDataChanges) Is Nothing) Then
Try
dA.Update(dsDat aChanges, tbl)

_dataSet1.Merge (dsDataChanges)
_dataSet1.Accep tChanges()

Catch ex As Exception
MessageBox.Show ("Exception Message: " & vbCrLf & ex.Message)
MessageBox.Show ("Exception Source: " & vbCrLf & ex.Source)
MessageBox.Show ("Exception StackTrace: " & vbCrLf & ex.StackTrace)
End Try
End If
End Sub
//

Is there a better way?

Thank you,
dbuchanan

[Cor Ligthert [MVP]]

DBuchanan,

A lot of text, however your "fails" text does at least me not say much.

Know that if something is a stored procedure or a SQL text string does not
append any thing in behaviour to a procedure in AdoNet (as both are written
well of course).

Cor

"dbuchanan" <db*********@ho tmail.com> schreef in bericht
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

Hello,

(Windows forms - SQL Server)

I fill my datagrid with a stored procedure that includes relationships
to lookup tables so that users can see the values of the combobox
selections rather than the key value that are stored in the table.

It works well if the comboboxes are selected when the row is created.

I fails to update the lookup table values if the combobox values are
inserted at a later time or if the combobox selections are changed
later.

The only work-around I know is to clear the form, datset, datagrid and
reload everything.
Here is the stored procedure
\\
CREATE PROCEDURE
dbo.usp_047MSen _sel_ByDevi_Typ eConnVolt
(
@fkDevice char(36),
@cmVarchar06 varchar(50) --pkComponentId for Cylinder
)AS SET NOCOUNT ON;
SELECT
c.pkComponentId ,
c.fkDevice,
c.fkComponentTy pe,
c.ComponentDesc ription,
c.QuanSharingCo nfiguration,
c.QuanConfigure d,--
c.QuanActuation s,
c.cmVarchar01,
c.cmVarchar02,
c.cmVarchar03,
c.cmVarchar04,
c.cmVarchar05,
c.cmVarchar06,
c.cmNumeric01,
c.cmNumeric02,
c.cmNumeric03,
c.cmNumeric04,
c.cmNumeric05,
c.cmNumeric06,
c.cmNumeric07,
c.cmBit01,
c.cmBit02,
c.cmBit03,
c.cmBit04,
c.cmBit05,
c.cmBit06,
c.cmBit07,
c.cmBit08,
c.cmBit09,
c.cmBit10,
c.cmBit11,
c.cmSmallint01,
v.Voltage, '<<from lookup table
c.cmSmallint02,
c.cmSmallint03,
c.cmSmallint04,
st.SensorType, '<<from lookup table
c.cmSmallint05,
sc.SensorConnec tion, '<<from lookup table
c.cmUserNote,
c.cmCreatedOn,
c.cmCreatedBy,
c.cmEditedOn,
c.cmEditedBy,
c.cmrowversion
FROM tbl040Cmpt c
left outer JOIN lkp104Voltage v ON c.cmSmallint01 =
v.pkVoltageId
left outer JOIN lkp302SensorTyp e st ON c.cmSmallint04 =
st.pkSensorType Id
left outer JOIN lkp103SensorCon nection sc ON c.cmSmallint05 =
sc.pkSensorConn ectionId
WHERE
(c.fkDevice = @fkDevice)
-- fkDevice = 'fc063d9f-62f1-4905-913a-e41f2471f2a7'
AND c.cmVarchar06 = @cmVarchar06 -- pkComponentId - the
-- AND c.cmVarchar06 = 'c6236862-dc3d-4e9d-a688-f57809fcb348' --
pkComponentId - the
AND c.fkComponentTy pe = 7 -- Motion Sensor
ORDER BY c.ComponentDesc ription

GO
//

Here is the example of the update code
\\
Call UpdateSource2(d al.da040CmptFrm , "tbl040Cmpt ")
//

and

\\
Protected Sub UpdateSource2(B yVal dA As SqlDataAdapter, ByVal tbl As
String)

Dim dsDataChanges As New CLIP.dsTables

dsDataChanges = CType(_dataSet1 .GetChanges, CLIP.dsTables)

If (Not (dsDataChanges) Is Nothing) Then
Try
dA.Update(dsDat aChanges, tbl)

_dataSet1.Merge (dsDataChanges)
_dataSet1.Accep tChanges()

Catch ex As Exception
MessageBox.Show ("Exception Message: " & vbCrLf & ex.Message)
MessageBox.Show ("Exception Source: " & vbCrLf & ex.Source)
MessageBox.Show ("Exception StackTrace: " & vbCrLf & ex.StackTrace)
End Try
End If
End Sub
//

Is there a better way?

Thank you,
dbuchanan

[Bart Mermuys]

Hi,

"dbuchanan" <db*********@ho tmail.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

Hello,

(Windows forms - SQL Server)

I fill my datagrid with a stored procedure that includes relationships
to lookup tables so that users can see the values of the combobox
selections rather than the key value that are stored in the table.

It works well if the comboboxes are selected when the row is created.

I fails to update the lookup table values if the combobox values are
inserted at a later time or if the combobox selections are changed
later.

The only work-around I know is to clear the form, datset, datagrid and
reload everything.
Here is the stored procedure
\\
CREATE PROCEDURE
dbo.usp_047MSen _sel_ByDevi_Typ eConnVolt
(
@fkDevice char(36),
@cmVarchar06 varchar(50) --pkComponentId for Cylinder
)AS SET NOCOUNT ON;
SELECT
c.pkComponentId ,
c.fkDevice,
c.fkComponentTy pe,
c.ComponentDesc ription,
c.QuanSharingCo nfiguration,
c.QuanConfigure d,--
c.QuanActuation s,
c.cmVarchar01,
c.cmVarchar02,
c.cmVarchar03,
c.cmVarchar04,
c.cmVarchar05,
c.cmVarchar06,
c.cmNumeric01,
c.cmNumeric02,
c.cmNumeric03,
c.cmNumeric04,
c.cmNumeric05,
c.cmNumeric06,
c.cmNumeric07,
c.cmBit01,
c.cmBit02,
c.cmBit03,
c.cmBit04,
c.cmBit05,
c.cmBit06,
c.cmBit07,
c.cmBit08,
c.cmBit09,
c.cmBit10,
c.cmBit11,
c.cmSmallint01,
v.Voltage, '<<from lookup table
c.cmSmallint02,
c.cmSmallint03,
c.cmSmallint04,
st.SensorType, '<<from lookup table
c.cmSmallint05,
sc.SensorConnec tion, '<<from lookup table
c.cmUserNote,
c.cmCreatedOn,
c.cmCreatedBy,
c.cmEditedOn,
c.cmEditedBy,
c.cmrowversion
FROM tbl040Cmpt c
left outer JOIN lkp104Voltage v ON c.cmSmallint01 =
v.pkVoltageId
left outer JOIN lkp302SensorTyp e st ON c.cmSmallint04 =
st.pkSensorType Id
left outer JOIN lkp103SensorCon nection sc ON c.cmSmallint05 =
sc.pkSensorConn ectionId
WHERE
(c.fkDevice = @fkDevice)
-- fkDevice = 'fc063d9f-62f1-4905-913a-e41f2471f2a7'
AND c.cmVarchar06 = @cmVarchar06 -- pkComponentId - the
-- AND c.cmVarchar06 = 'c6236862-dc3d-4e9d-a688-f57809fcb348' --
pkComponentId - the
AND c.fkComponentTy pe = 7 -- Motion Sensor
ORDER BY c.ComponentDesc ription

GO
//

Here is the example of the update code
\\
Call UpdateSource2(d al.da040CmptFrm , "tbl040Cmpt ")
//

and

\\
Protected Sub UpdateSource2(B yVal dA As SqlDataAdapter, ByVal tbl As
String)

Dim dsDataChanges As New CLIP.dsTables

dsDataChanges = CType(_dataSet1 .GetChanges, CLIP.dsTables)

If (Not (dsDataChanges) Is Nothing) Then
Try
dA.Update(dsDat aChanges, tbl)

_dataSet1.Merge (dsDataChanges)
_dataSet1.Accep tChanges()

Catch ex As Exception
MessageBox.Show ("Exception Message: " & vbCrLf & ex.Message)
MessageBox.Show ("Exception Source: " & vbCrLf & ex.Source)
MessageBox.Show ("Exception StackTrace: " & vbCrLf & ex.StackTrace)
End Try
End If
End Sub
//

Is there a better way?
From the sp i gathered you have following tables:

master: tbl040Cmpt (cmSmallint01, cmSmallint04, cmSmallint05)
lk1: kp104Voltage (pkVoltageId, Voltage)
lk2: lkp302SensorTyp e (pkSensorTypeId , SensorType)
lk3: lkp103SensorCon nection (pkSensorConnec tionId, SensorConnectio n)

I'll show you how to do it for one lookup, you can do the same for the other
lookups. Instead of resolving foreign keys inside the stored procedure
using joins, you should only get the foreign keys and do the lookup using
relations and expression columns in .NET.

Fill DataSet with tbl040Cmpt and kp104Voltage:

DataSet ds = new DataSet();
tbl040CmptAdapt er.Fill( ds, "tbl04Cmpt" ) ' no join query
kp104VoltageAda pter.Fill( ds, "kp104Volta ge" )
Add relation between kp104Voltage(=p arent) and tbl040Cmpt(=chi ld):

ds.Relations.Ad d( "pk104Voltage_t bl04Cmpt", _
ds.Tables("kp10 4Voltage").Colu mns("pk104Volta ge"), _
ds.Tables("tbl0 4Cmpt").Columns ("cmSmallint01" ) )
Hide the foreign key column:
ds.Tables("tbl0 4Cmpt").Columns ("cmSmallint01" ).ColumnMapping = _
MappingType.Hid den
Then add an expression column:

ds.Tables("tbl0 4Cmpt").Columns .Add( "Voltage", _
typeof(string), "Parent(pk104Vo ltage_tbl04Cmpt ).Voltage" )
That's it, now you have a "Voltage" column (lookup,readonl y) that will
change if the hidden cmSmallint01 field is changed.

There is however a bug with Updating DataTables that have expression
column(s), you need to remove the expression columns before and re-insert
them after a da.Update.

hth,
greetings
Thank you,
dbuchanan

[dbuchanan]

Bart,

Thank you so much for your reply and the tip on the bug too.

BTW 1: Is any there a bug list anywhere?

BTW 2: Do you know if this bug is fixed in .NET 2.0

--
dbuchanan