Updating SQL Table from View in DGV

[neodotnet3]

Hello,

I have a Form with a DGV, that has an SQL view. This view has pretty titles based on the table and also key tables with the descriptive values replacing the keys in the table, i.e.

TABLE
id
client_name
country_key

VIEW
id
[Client Name]
Country

where there is a country_key table with the descriptions.

The user may want to change either of these values, so I was thinking of maybe creating a dictionary, like so;

        DicTableKey.Add("Client Name", Tuple.Create("client", "ent.client"))
        DicTableKey.Add("Country", Tuple.Create("country_key", "ent.client"))

So when a user updates a cell, I can check the dictionary and update the table accordingly. However what do I do about the keys, as the descriptive value will be displayed and not the key value.

Is there a better approach? What is best practice?

Appreciate your help.

[vbdotnut]

What I might do is mess around with a DataGridViewComboBoxColumn

This is assuming youre employing a DataAdapter/DataTable as a datasource for the DGV.

Go back to your view and add the key column, which you can then hide in the DGV

Once you create the DataGridViewComboBoxColumn then go back to your DB and get distinct CtryID/Country from whatever table has them all. use that for a datasource for the DataGridViewComboBoxColumn, set the ValueMember to they key and the DisplayMember to the Country Text. Then create your own Update Command using parameters that satisfy the target table.

[wes4dbt]

I agree, if the DGV is bound to a DataTable (or bindingsource with a datatable datasource) then us a DataGridViewComboBox for the country_key column. Then there will be no need for the Dictionary to verify the entry because by default the DataGridViewComboBox drop down style is DropDownList. So the user will have to chose an item from the list.

Something like,

         ' Some other dgv  column
        Dim col1 As New DataGridViewTextBoxColumn
        col1.DataPropertyName = "Somefield"
        col1.HeaderText = "Column1"
        col1.Name = "Column1"
        Me.yourDataGridView.Columns.Add(col1)

        'You Combobox column
        Dim col2 As New DataGridViewComboBoxColumn
        col2.DataPropertyName = "Country_key"
        col2.HeaderText = "Country"
        col2.Name = "Country_key"
        

        col2.DisplayMember = "country"
        col2.ValueMember = "ID"
        col2.DataSource = countryDataTable

        Me.yourDataGridView.Columns.Add(col2)

[vbdotnut]

Well, the datagridview does not necessarily need to be bound, and in fact if the view's columns names differ from the table column names then you would loose the advantage anyway. It is only important that the DataGridViewComboBoxColumn is databound to a table that has the id/string key pair for the value/displaymember properties.