473,706 Members | 5,428 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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

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

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

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
2582
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 datagrid. Currently I use a join between the main table and the lookup table to get the Product description. Is it possible to use a dropdownlist in the datagrid to display and select (in Edit mode) the lookup values? Thank you.
4
2146
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 SQL database "pubs". I've followed the instructions in the comments and also changed everything pertaining to SQL over to OLEDB. (i.e. Changed SqlDbType. to OleDbType.) I also changed the datafield names and variable names accordingly. The page...
3
5437
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 the search criteria: The class lib returns two fields "custid" and "custname" - I need to obtain the custid to go to the next web page. =======
0
1261
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 data table. When I make selections in bound combo boxes on the form and save the changes the datagrid which is populated by the following stored procedure does not reflect the changes for the referenced lookup tables until I close and reload...
0
1193
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 to convert between ID and Text. Again I've got most of this working but I'm stuck on one point, which probably does not involve the dropdown. How can I display the Lookup name in the column when I'm not editing? It always displays the key ID...
1
4020
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 DoCmd.RunSQL command in code and my question is how do I write a sql statement in code that updates the fldTeacher1 field with a new value? The main table is tblStudent and the lookup table is tblTeacher. The lookup field in tblStudent is fldTeacher1 and...
5
2596
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
2452
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 works best) to display a dropdown menu of fields populated from table tblInvoiceData. This control also includes a textbox which the user can input a value. These two columns are side by side and not in a vertical layout. The user then clicks on...
3
1563
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 column which duly fire the relevent events, I have handled the edit and canel events OK, but Im having problem with the row_updated event. I thought I would be able to get the new values e.newvalues.item(Columnindex)
0
8779
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8695
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9282
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9154
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8987
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7900
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4445
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4708
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2089
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.