Set value to DataGridViewComboBoxColumn

Hi

I need to set the value on  a DataGridViewComboBoxColumn.

I populate the combobox by:

 Public Sub InsertItemsintocmbTransSplit(ByVal cmb As DataGridViewComboBoxColumn, ByVal SQLDatabase As String)

        Dim objConn As System.Data.SqlClient.SqlConnection
        Dim OBJSQL As New System.Data.SqlClient.SqlCommand
        Dim objDR As System.Data.SqlClient.SqlDataReader


        'If Not Linq_Datatable Is Nothing Then
        '    Linq_Datatable = Nothing

        'End If

        'Linq_Datatable = New DataTable


        Try

            Dim strConnectionString As String = "Data Source=" + SQLServerName + ";Initial Catalog=" + SQLDatabase + ";User Id=" + SQLUser + ";Password=" + SQLPassword + ";"

            objConn = New System.Data.SqlClient.SqlConnection(strConnectionString)


            Dim Sql As String


            Sql = "Select TransSplitNo,TransactionSplit" _
                   & " from dbo.TRC_MaintainTransactionSplitFields" _
                   & " order by TransactionSplit DESC "


            Dim da As New SqlDataAdapter(Sql, _
                                objConn)
            Dim dt As New DataTable

            da.Fill(dt)


            objConn.Open()


            OBJSQL = New System.Data.SqlClient.SqlCommand(Sql, objConn)
            ' OBJSQL2 = New System.Data.SqlClient.SqlCommand(Sql2, objConn)

            objDR = OBJSQL.ExecuteReader
            'Dim dt As New DataTable

            cmb.DisplayMember = "TransactionSplit"
            cmb.ValueMember = "TransSplitNo"
            cmb.DataSource = dt

            'If objDR.HasRows Then
            '    While objDR.Read()
            '        cmb.Items.Add(objDR.Item("TransactionSplit").ToString)



            '    End While
            'End If

            '   cmb.Items.Add("None")


        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error-InsertItemsintocmbTransSplit()", MessageBoxButtons.OK, MessageBoxIcon.Error)


        Finally

            OBJSQL.Dispose()
            objDR.Close()
            objConn.Close()




        End Try



    End Sub

Open in new window


I query the SQL database to get the selected value by:

 Public Function GetTranssplitvalue(ByVal NomAccountID As String, ByVal CompanyID As String, ByVal URN As String, ByVal SQLDatabase As String) As String

        Dim objConn As System.Data.SqlClient.SqlConnection
        Dim OBJSQL As New System.Data.SqlClient.SqlCommand
        Dim Linq_Datatable As DataTable
        Dim objDR As System.Data.SqlClient.SqlDataReader




        Linq_Datatable = New DataTable


        Try

            Dim strConnectionString As String = "Data Source=" + SQLServerName + ";Initial Catalog=" + SQLDatabase + ";User Id=" + SQLUser + ";Password=" + SQLPassword + ";"

            objConn = New System.Data.SqlClient.SqlConnection(strConnectionString)


            Dim Sql As String


            Sql = "Select TransSplitID from dbo.TRC_TransactionSplitFieldsLines " _
          & " where NLNominalAccountID = " & "'" & NomAccountID & "'" _
          & " and CompanyID = " & "'" & CompanyID & "'" _
          & " and URN = " & "'" & URN & "'"






            objConn.Open()


            OBJSQL = New System.Data.SqlClient.SqlCommand(Sql, objConn)
            ' OBJSQL2 = New System.Data.SqlClient.SqlCommand(Sql2, objConn)

            objDR = OBJSQL.ExecuteReader

            'Load Linq_Datatable
            Linq_Datatable.Load(objDR)


            For Each row As DataRow In Linq_Datatable.Rows

                Return row("TransSplitID").ToString


            Next

            Return String.Empty

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error-GetTranssplitvalue()", MessageBoxButtons.OK, MessageBoxIcon.Error)


        Finally
            If Not Linq_Datatable Is Nothing Then
                Linq_Datatable = Nothing

            End If
            OBJSQL.Dispose()
            objDR.Close()
            objConn.Close()




        End Try



    End Function

Open in new window


Then set the value by:

   Dim NLNominalAccountID As String = String.Empty
        Dim URN As String = String.Empty
        NLNominalAccountID = NominalLookup1.NominalCode.PrimaryKey.Value
        Dim rowcount As String = dtViewMaintainTransSplit.RowCount


        If rowcount > 0 Then

            Dim dgv As DataGridView = dtViewMaintainTransSplit
            Dim cellValue As String
            Dim TransSplitID As String

            For r As Integer = 0 To dgv.RowCount - 1
                Dim rw As DataGridViewRow = dgv.Rows(r)
                For c As Integer = 0 To dgv.ColumnCount - 1

                    If c = 4 Then
                        cellValue = dgv.Rows(r).Cells(c).Value
                        URN = dgv.Rows(r).Cells(c).Value
                        TransSplitID = GetTranssplitvalue(NLNominalAccountID, CompanyID, URN, "CPS_ReportConsole")
                        TransSplitID = RTrim(TransSplitID)

                        If TransSplitID <> String.Empty Then
                            DirectCast(dtViewMaintainTransSplit(5, r), DataGridViewComboBoxCell).Value = TransSplitID

                        End If
                  



                    End If
                Next


            Next


        End If

Open in new window


But I get an error on Datagridview.

Please help.
Shezad AhmedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Shezad AhmedAuthor Commented:
never mind got it to work.

I had to set the value as integer not string.

  If TransSplitID <> String.Empty Then
                            Dim NumTransspiltID As Integer = TransSplitID
                            DirectCast(dtViewMaintainTransSplit(5, r), DataGridViewComboBoxCell).Value = NumTransspiltID

                        End If
0

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
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.