473,289 Members | 1,829 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

How to update datagrid when it displays lookup table values

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

Nov 21 '05 #1
3 2252
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*********@hotmail.com> schreef in bericht
news:11**********************@g47g2000cwa.googlegr oups.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_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

Nov 21 '05 #2
Hi,

"dbuchanan" <db*********@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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_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").Col umnMapping = _
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
Thank you,
dbuchanan

Nov 21 '05 #3
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

Nov 21 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Vik | last post by:
How can I display the lookup values in a datagrid? E.g., the datagrid displays a table that contains a ProductID field. I want to display a Product description from a Product table in that...
4
by: Jonathan Upright | last post by:
Greetings to anyone who can help: I'm using WebMatrix to make ASP.NET pages, and I chose the "Editable DataGrid" at the project selector screen. As you may know, it defaults to the Microsoft...
3
by: Fred Nelson | last post by:
Hi: I have a VB.NET web app and in one section I'm creating a datagrid that lists several id's and names - I would like to be able to send the user to another web page if only one record matches...
0
by: dbuchanan | last post by:
Hello, For my datagrid I added a datagrid table style to include columns from my lookup tables. These display the values in the lookup tables rather than just the integer key value stored in the...
0
by: JohnSouth | last post by:
Hi I need a dropdown list column in a datagrid control, and I've got the basics working where the column contents is text. However the data table stores a foreign key to a lookup table so I need...
1
by: sierra467 | last post by:
I realize that lookup fields in a table should not be used but that is the way this particular creator has done. Could someone help me by answering my question. I am trying to run a...
5
by: Stephen Plotnick | last post by:
I'm very new to VB.NET 2003 Here is what I have accomplished: MainSelectForm - Selects an item In a public class I pass a DataViewRow to ItemInformation1 Form ItemInformation2 Form
13
by: shookim | last post by:
I don't care how one suggests I do it, but I've been searching for days on how to implement this concept. I'm trying to use some kind of grid control (doesn't have to be a grid control, whatever...
3
by: Just Me | last post by:
Im struggling a bit with the update function. I have bound a table with lets say 5 records ( 4 columns ) to the gridview , this displays jsut fine. I have added edit/cancel/update buttons in a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.