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_TypeConnVolt
(
@fkDevice char(36),
@cmVarchar06 varchar(50) --pkComponentId for Cylinder
)AS SET NOCOUNT ON;
SELECT
c.pkComponentId,
c.fkDevice,
c.fkComponentType,
c.ComponentDescription,
c.QuanSharingConfiguration,
c.QuanConfigured,--
c.QuanActuations,
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.SensorConnection, '<<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 lkp302SensorType st ON c.cmSmallint04 =
st.pkSensorTypeId
left outer JOIN lkp103SensorConnection sc ON c.cmSmallint05 =
sc.pkSensorConnectionId
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.fkComponentType = 7 -- Motion Sensor
ORDER BY c.ComponentDescription

GO
//

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

and

\\
Protected Sub UpdateSource2(ByVal 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(dsDataChanges, tbl)

_dataSet1.Merge(dsDataChanges)
_dataSet1.AcceptChanges()

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

 

[Bart Mermuys]

Hi,

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_TypeConnVolt
(
@fkDevice char(36),
@cmVarchar06 varchar(50) --pkComponentId for Cylinder
)AS SET NOCOUNT ON;
SELECT
c.pkComponentId,
c.fkDevice,
c.fkComponentType,
c.ComponentDescription,
c.QuanSharingConfiguration,
c.QuanConfigured,--
c.QuanActuations,
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.SensorConnection, '<<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 lkp302SensorType st ON c.cmSmallint04 =
st.pkSensorTypeId
left outer JOIN lkp103SensorConnection sc ON c.cmSmallint05 =
sc.pkSensorConnectionId
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.fkComponentType = 7 -- Motion Sensor
ORDER BY c.ComponentDescription

GO
//

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

and

\\
Protected Sub UpdateSource2(ByVal 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(dsDataChanges, tbl)

_dataSet1.Merge(dsDataChanges)
_dataSet1.AcceptChanges()

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: lkp302SensorType (pkSensorTypeId, SensorType)
lk3: lkp103SensorConnection (pkSensorConnectionId, SensorConnection)

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();
tbl040CmptAdapter.Fill( ds, "tbl04Cmpt" ) ' no join query
kp104VoltageAdapter.Fill( ds, "kp104Voltage" )


Add relation between kp104Voltage(=parent) and tbl040Cmpt(=child):

ds.Relations.Add( "pk104Voltage_tbl04Cmpt", _
ds.Tables("kp104Voltage").Columns("pk104Voltage"), _
ds.Tables("tbl04Cmpt").Columns("cmSmallint01") )


Hide the foreign key column:
ds.Tables("tbl04Cmpt").Columns("cmSmallint01").ColumnMapping = _
MappingType.Hidden


Then add an expression column:

ds.Tables("tbl04Cmpt").Columns.Add( "Voltage", _
typeof(string), "Parent(pk104Voltage_tbl04Cmpt).Voltage" )


That's it, now you have a "Voltage" column (lookup,readonly) 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

 

[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