Binding a Combo with Fixed Values - It can't be that complex surely!!

I have a combo which I need to populate with fixed values (e.g. OK,Yes,No etc..) and bind to a column in a table, which just happens to be displayed in a datagrid on the same form. Because the values are fixed, I figured it should be the same as binding a textbox object, it's just that the user doesn't have to type in the values, they can select them from the list (or so I thought). I added a textbox and bound that and it works fine. I added the combo, populated it but it doesn't change the values. It will show changes if I enter them via the text box.

For example:

1) Go to grid row 1, Data column (one I need to change) is '*' by default. Change this with combo to 'Yes', move off row 1 nothing changes.
2) Go back to grid row 1, change data column using textbox to 'Yes', cell in grid changes to 'Yes', combo changes to 'Yes', move off row and return and both textbox and combo show 'Yes'.

So the combo will show the data correctly, but cannot change the data.

I had posted this problem before but had to move onto another project before a solution could be found.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VFPSQLDeveloperAuthor Commented:
Sorry, meant to show the code used to do this:

        ' Join Valve Parameters & Group Tables and Populate Grid
        Dim cn As New SqlCeConnection
        cn.ConnectionString = _
               "Data Source=\My Documents\PmsCE.sdf;" + _
              "SSCE:Database Password=Pipelines;"

        Dim cmd As String
        Dim dr As SqlCeDataReader
        Dim count As Int16


            ' Check Location Exists for Pipelines
            Dim cm As New SqlCeCommand("SELECT Id FROM ValveLocation " & _
                "WHERE Pipeline_Id = " & Me.ComboBox1.SelectedValue & _
                " AND StrKey = '" & CStr(Me.TextBox1.Text) & "'", cn)

            ' Make Sure we have a matching Location (There must be a better way!)
            count = 0
            dr = cm.ExecuteReader()
            While dr.Read()
                count += 1
            End While

            ' Tell User if Duff Location
            If count = 0 Then
                MessageBox.Show("Location Not Valid for this Pipeline!")
            End If

            ' Fill Dataset with Parameters for Location
            cmd = "SELECT ValveGroup.Description, ValveParameters.Description, " & _
                "Prompt_Type " & _
                "FROM ValveParameters " & _
                "INNER JOIN ValveGroup ON Group_Id = ValveGroup.Id " & _
                "WHERE Pipeline_Id = " & Me.ComboBox1.SelectedValue & _
                " ORDER BY ValveGroup.Description, ValveParameters.Description"
            Dim da As New SqlCeDataAdapter(cmd, cn)
            Dim ds As New DataSet
            da.Fill(ds, "Parameters")

            ' Load Location Selection Form    
            Dim dlgLocation_Selection As New Location_Selection

            ' Populate the Grid
            Dim c As New DataColumn("Data", GetType(System.String))
            c.DefaultValue = "*"

            dlgLocation_Selection.DataGrid1.DataSource = ds.Tables(0)

            Dim dt As DataTable
            dt = ds.Tables("Parameters")

            ' Use Table Styles to Size Columns, Name Headers & and Make Id Columns Hidden
            Dim grdts As New DataGridTableStyle
            grdts.MappingName = dt.TableName
            dlgLocation_Selection.DataGrid1.TableStyles(0).GridColumnStyles(0).Width = 70
            dlgLocation_Selection.DataGrid1.TableStyles(0).GridColumnStyles(0).HeaderText = "Group"
            dlgLocation_Selection.DataGrid1.TableStyles(0).GridColumnStyles(1).Width = 75
            dlgLocation_Selection.DataGrid1.TableStyles(0).GridColumnStyles(1).HeaderText = "Description"
            dlgLocation_Selection.DataGrid1.TableStyles(0).GridColumnStyles(2).Width = 25
            dlgLocation_Selection.DataGrid1.TableStyles(0).GridColumnStyles(2).HeaderText = "Type"

            'Populate Combo

            ' Bind the textBox for Testing only
            dlgLocation_Selection.TextBox1.DataBindings.Add("Text", dt, "Data")

            ' Bind the Combo
            dlgLocation_Selection.ComboBox1.DataBindings.Add("Text", dt, "Data")

            ' Now Show the Dialog
            Me.Visible = False


            Me.Visible = True

            ' Release Data Adapter & Connection

        Catch sqlex As SqlCeException


        Catch ex As Exception
        End Try
Not read the full question yet, but noticed the comment about getting a count of the records to check whether there is one or not.  Best way to do this is like below.

        oCMD = cn.CreateCommand
        oCMD.CommandText = "SELECT Count(Id) FROM ValveLocation " & _
                "WHERE Pipeline_Id = " & Me.ComboBox1.SelectedValue & _
                " AND StrKey = '" & CStr(Me.TextBox1.Text) & "'"

        count = CType(oCMD.ExecuteScalar(), Integer)

       That will give you the count without having to use a datareader, problem with the way your doing it above is that while you do a dr.Read() it keeps a connection to the database, and when you call it again, i.e. loop through you are going back and forth between the database everytime.

The count code above will goto the database, count how many records match the criteria and return you the count, it is much more efficient than the way you have above.

Hope this helps.

I will read you post properly now and see if I can help you out with the main question

Just tried to do a quick test of what you are doing.

I think i have it how you want now,

Instead of binding to the Text value in the combobox, bind it to the SelectedItem value.

dlgLocation_Selection.ComboBox1.DataBindings.Add("SelectedItem", dt, "Data")

Let me know if this works



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VFPSQLDeveloperAuthor Commented:
Fixed! Many thanks Craig I'm embarassed to say how long I wasted on that!! Also thanks for the advice on obtaining the count.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.