[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • Last Modified:

Updating Multiple Tables?

I am populating a DataGrid in C# from two tables.  Table1 has several columns, one of which is a TypeID which contains a numeric ID.  When I'm populating my grid I'm taking this ID, and doing an inner join to get the actual type string.  Eg ID 1 = Bank, ID 2 = Shop, etc.

However I'm rather stuck on what to do for an update, if the Type is changed in my grid (by selecting the string from a drop-down list) how can I update Table1 with the correct ID value?  I figure I could have several UPDATE and SELECT queries, but I'm hoping there is a more efficient way.  Something like:

UPDATE Table1 SET TypeID = Table2.ID WHERE Table2.Description = 'WhateverUserSelects'
0
angus_young_acdc
Asked:
angus_young_acdc
  • 5
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: the dropdown should have 2 values per item: the VALUE (matching the ID to be stored), and the TEXT (which is the one to be displayed)
hence, for the update, you use the selecteditem's VALUE property, not the TEXT property... which solves the problem automatically.

anyhow, in terms of sql:


UPDATE Table1 
  SET TypeID = (select Table2.ID from table2 WHERE Table2.Description = 'WhateverUserSelects' )
where key = <key of the value to update>

Open in new window

0
 
angus_young_acdcAuthor Commented:
Hi angellll that's a very good idea!  Never thought of that, how can I assign the correct ID Value to the combo box for each text entry?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please show how you fill the combo box already?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
angus_young_acdcAuthor Commented:
Certainly the code is in the snippet below
            SqlCommand command = new SqlCommand();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.AppSettings["SQLConnectionString"];
            conn.Open();
            command.CommandText = _procedure; // SELECT * FROM
            command.CommandType = CommandType.Text;
            command.Connection = conn;
            SqlDataReader reader = default(SqlDataReader);
            reader = command.ExecuteReader();
            int index = 0;
            while (reader.Read())
            {
                cboStatusTypes.Items.Add(reader["type"]);
            }
            reader.Close();
            conn.Close();

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to change:
cboStatusTypes.Items.Add(reader["type"]);
into:
cboStatusTypes.Items.Add(reader["type"], reader["id"]);
0
 
angus_young_acdcAuthor Commented:
Hi angellll I have tried that but it gives an overload error:

Error      5      No overload for method 'Add' takes '2' arguments
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about
cboStatusTypes.Items.Add( new ListItem(reader["type"], reader["id"]));

Open in new window

0
 
angus_young_acdcAuthor Commented:
Is ListItem a web control?  Suppose it may have helped to say that this is a windows app lol.  Anyway, I have created a custom class called ListItem, but although I can get the selected Item I can't get a value for SelectedValue
0
 
angus_young_acdcAuthor Commented:
I couldn't get this working, so instead decided to create a List<string> and add the values to that, then use the combobox selectedindex to get the specific index in the list.  Not pretty, but it works.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now