[RESOLVED] [2005] How to programmatically assign a value in a datagridviewcomboboxcell

[eimroda]

I have an unbound DataGridView and a ComboBox cell in it. I got a problem when assigning a value to the combobox cell at runtime. below is my code:

    Private Sub GetRecords(ByVal strSQL As String)
        Try
            If IsConnected() Then
                Dim myCommand As SqlCommand = New SqlCommand(strSQL, Conn)
                Dim myDataReader As SqlDataReader = myCommand.ExecuteReader()

                Dim strID, strLName, strFName, strMI, strContactNo, strAddress, strBIR, strPHIC As String
                Dim intSpecialization As Integer
                Dim strRow As String()

                Grid.RowCount = IIf(Grid.AllowUserToAddRows, 1, 0)

                With myDataReader
                    Do While myDataReader.Read()
                        strID = IIf(.IsDBNull(0), 0, .GetInt32(0).ToString)
                        strLName = IIf(.IsDBNull(1), "", .GetString(1).Trim)
                        strFName = IIf(.IsDBNull(2), "", .GetString(2).Trim)
                        strMI = IIf(.IsDBNull(3), "", .GetString(3).Trim)
                        intSpecialization = IIf(.IsDBNull(4), 1, .GetInt32(4))
                        strContactNo = IIf(.IsDBNull(5), "", .GetString(5).Trim)
                        strAddress = IIf(.IsDBNull(6), "", .GetString(6).Trim)
                        strBIR = IIf(.IsDBNull(7), "", .GetString(7).Trim)
                        strPHIC = IIf(.IsDBNull(8), "", .GetString(8).Trim)

                        'error in this line:
                        'System.FormatException: DataGridViewComboBoxCell value is not valid
                        'the intSpecialization variable is the value for the combo box column
                        strRow = New String() {strID, strLName, strFName, strMI, intSpecialization, _
                            strContactNo, strAddress, strBIR, strPHIC}

                        Grid.Rows.Add(strRow)
                    Loop
                End With

                'close data reader
                If myDataReader IsNot Nothing Then myDataReader.Close()
                LastSqlUsed = strSQL
                GridChanged = False
                CloseConnection()
            End If
        Catch ex As Exception
            DisplayError(ex.Message)
        End Try
    End Sub

This is how I created the ComboBox column:

    Sub FillCombo()
        Dim myColumn As New DataGridViewComboBoxColumn

        If IsConnected() Then
            Dim sqlCommand As New SqlCommand("SELECT * FROM tblSpecializations ORDER BY Sp_Desc", Conn)
            Dim sqlAdapter As New SqlDataAdapter
            sqlAdapter.SelectCommand = sqlCommand
            Dim myTable As New DataTable

            sqlAdapter.Fill(myTable)

            With myColumn
                '.DataPropertyName = "colSpecialization"
                .Name = "colSpecialization"
                .HeaderText = "Specialization"
                .DropDownWidth = 160
                .Width = 200
                .MaxDropDownItems = 10
                '.FlatStyle = FlatStyle.Popup
                'set the data properties
                .DataSource = myTable
                .ValueMember = "Sp_Control"
                .DisplayMember = "Sp_Desc"
            End With
            Grid.Columns.Remove("colSpecialization")
            Grid.Columns.Insert(4, myColumn)
        End If

    End Sub

[jmcilhinney]

That's all very nice code but how about you explain what the problem is so we have an idea what we're looking for. Would you go to the doctor and say "I've got a problem"? Would you take your car to a mechanic and say "I've got a problem"? I would hope that in both cases you'd try to provide as much information about the problem as possible so they knew what they were looking for. This situation is exactly the same.

[eimroda]

oh sorry if I did not make myself clearly. anyways, this is embedded in my first code:

                       'error in this line:
                        'System.FormatException: DataGridViewComboBoxCell value is not valid
                        'the intSpecialization variable is the value for the combo box column
                        strRow = New String() {strID, strLName, strFName, strMI, intSpecialization, _
                            strContactNo, strAddress, strBIR, strPHIC}

i got everything ok, saving, deleting, updating etc. but when i pull records form the database and assign them to the DGV, i am getting that error.

my combobox column should handle the Specialization column which is an Integer type. The variable intSpecialization holds the value and I am getting the error "System.FormatException: DataGridViewComboBoxCell value is not valid" when it executes the line

                        strRow = New String() {strID, strLName, strFName, strMI, intSpecialization, _
                            strContactNo, strAddress, strBIR, strPHIC}

                        Grid.Rows.Add(strRow)

intSpecialization has a valid value of integer when i debug it...
(Please tell me if it is not still clear.. my poor English... )

[jmcilhinney]

I have a question. You say that your grid is unbound yet you say that it is populated by data from a database. Why would you not choose to bind the data to the grid in that case? Not using data-binding is simply making a relatively easy process more difficult and more error-prone.

[eimroda]

as they say, old habit never dies...

i have newly shifted to .net from vb6 and I am still comfortable with the "unbound" thingy even though reading from this forum, data-bound controls are OK with .Net...

but as of this project, I am using "still" the unbound procedure... the Bound things maybe considered later ( I still dont know how to do it)

the problem so far i encountered is this one, assigning value to a combobox column...

[jmcilhinney]

The Value in each cell corresponds to the SelectedValue of the ComboBox control hosted in that cell. You have bound a DataTable to your combo box cell (why is it OK to bind to a column yet it's not OK to bind to the grid?) so each cell Value must correspond to a value in the Sp_Control column of your tblSpecializations table. If they don't then you'll get that error.

I suggest that you stop what you're doing and create a test project to familiarise yourself with binding data to a grid. It will take less time to do that than to continue as you're going. Otherwise you're going to spend time fixing issues that simply wouldn't exist if you bound your data. It takes one or two lines of code to bind data to a grid. There's no manual transfer of data either way so everything becomes significantly easier.

I would also suggest getting rid of all that code to create the grid column. Unless there's a specific reason you can't create column(s) at design time you are again simply wasting time and code and potentially introducing issues.

[eimroda]

The Value in each cell corresponds to the SelectedValue of the ComboBox control hosted in that cell. You have bound a DataTable to your combo box cell (why is it OK to bind to a column yet it's not OK to bind to the grid?) so each cell Value must correspond to a value in the Sp_Control column of your tblSpecializations table. If they don't then you'll get that error.

i tried and got a value of 3 as value of intSpecialization variable, which is also present in my tblSpecializations table but i still got the error...

I suggest that you stop what you're doing and create a test project to familiarise yourself with binding data to a grid. It will take less time to do that than to continue as you're going. Otherwise you're going to spend time fixing issues that simply wouldn't exist if you bound your data. It takes one or two lines of code to bind data to a grid. There's no manual transfer of data either way so everything becomes significantly easier.

Im trying to get some tutorials now.. tnx!

but if anybody can point me to right direction of what i am doing now, please do...

[jmcilhinney]

Having had another look I'm guessing that this is the problem:

              strRow = New String() {strID, strLName, strFName, strMI, intSpecialization, _
                            strContactNo, strAddress, strBIR, strPHIC}

You're converting everything to strings and and assigning them to cells. That's all well and good for the values that are already strings but the fifth column is expecting an Integer. You're converting your Integer to a string and assigning that and it doesn't match any of the values from the columns ValueMember. You should be creating an Object array, not a String array. That way each value will be assigned as is rather than converted to a string. Do not EVER convert anything that is not a string to a string unless you specifically need a string, which in almost all cases means when you want to display it. You are NOT displaying that value. You're assigning it to a grid cell. The grid cell stores the value itself and the IT looks after converting it to a string for display purposes.

[eimroda]

thank you very much for your time! i'm going back now to my workplace and try this and update you if it works

(my workplace has no internet connection and i'm about 3 km from there right now )

[jmcilhinney]

The fact that that code was not flagged as an issue indicates that you have Option Strict turned Off. I suggest that you turn it On immediately for the current project and for all future projects. It will indicate any code that employs late-binding or implicit conversion. Fixing those errors by employing explicit casts and conversions instead will at least make your code run faster, and it may even pick up issues like this that don't actually show themselves until run time. If you'd been told by the IDE that you were converting an Integer to a String implicitly this may have alerted you to the issue yourself.

[eimroda]

jmcilhinney! you're the man! it should be Object and not String as you said.. it works perfectly now.. tnx man!