asked on

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'

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

angus_young_acdc

ASKER

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?

Guy Hengel [angelIII / a3]

can you please show how you fill the combo box already?

angus_young_acdc

ASKER

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

Guy Hengel [angelIII / a3]

you need to change:
cboStatusTypes.Items.Add(reader["type"]);
into:
cboStatusTypes.Items.Add(reader["type"], reader["id"]);

angus_young_acdc

ASKER

Hi angellll I have tried that but it gives an overload error:

Error 5 No overload for method 'Add' takes '2' arguments

Guy Hengel [angelIII / a3]

what about

cboStatusTypes.Items.Add( new ListItem(reader["type"], reader["id"]));

Open in new window

angus_young_acdc

ASKER

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

angus_young_acdc

ASKER

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.