Member Avatar for Rachna0309

I have datagridview with 12 columns from which last 3 columns are comboboxcolumn and I have to get value of those 3 columns to update database.I have witten below code on cellvaluechanged event:

Dim rcount As Integer = dgvUserDetails.Rows.Count
        For Each row As DataGridViewRow In dgvUserDetails.Rows
            If Not row.IsNewRow Then
                VNO = Me.dgvUserDetails.Rows(e.RowIndex).Cells(1).Value
                sql = "Select ID from datafile where VNO = " & VNO & ""
                If rs.State = 1 Then rs.Close()
                rs.Open(sql, MainCon, 1, 3)
                If Not rs.EOF Then
                    dataid = rs.Fields(0).Value
                End If


                If e.ColumnIndex = 9 Then
                    If Me.dgvUserDetails.Rows(e.RowIndex).Cells(9).Value.ToString <> "" Then
                        caste = Me.dgvUserDetails.Rows(e.RowIndex).Cells(9).Value.ToString
                        sql = "Select ID from Category where Desc = '" & caste & "'"
                        If rs.State = 1 Then rs.Close()
                        rs.Open(sql, MainCon, 1, 3)
                        If Not rs.EOF Then
                            C_Id = rs.Fields(0).Value
                        End If
                        sql = "update datafile set caste = " & C_Id & " where ID = " & dataid & ""
                        MainCon.Execute(sql)
                    End If
                ElseIf e.ColumnIndex = 10 Then
                    If Me.dgvUserDetails.Rows(e.RowIndex).Cells(10).Value.ToString <> "" Then
                        prof = Me.dgvUserDetails.Rows(e.RowIndex).Cells(10).Value.ToString
                        sql = "Select ID from Category where Desc = '" & prof & "'"
                        If rs.State = 1 Then rs.Close()
                        rs.Open(sql, MainCon, 1, 3)
                        If Not rs.EOF Then
                            P_Id = rs.Fields(0).Value
                        End If
                        sql = "update datafile set prof = " & P_Id & " where ID = " & dataid & ""
                        MainCon.Execute(sql)
                    End If
                ElseIf e.ColumnIndex = 11 Then
                    If Me.dgvUserDetails.Rows(e.RowIndex).Cells(11).Value.ToString <> "" Then
                        degree = Me.dgvUserDetails.Rows(e.RowIndex).Cells(11).Value.ToString
                        sql = "Select ID from Category where Desc = '" & degree & "'"
                        If rs.State = 1 Then rs.Close()
                        rs.Open(sql, MainCon, 1, 3)
                        If Not rs.EOF Then
                            D_Id = rs.Fields(0).Value
                        End If
                        sql = "update datafile set degree = " & D_Id & " where ID = " & dataid & ""
                        MainCon.Execute(sql)
                    End If
                End If
            End If
        Next

But when this for loop starts,It only updates caste(i.e first column out of 3).It doesnt move to next column.Can any1 please help me.
Thanks in advance.

  • 2 Contributors
  • 3 Replies
  • 137 Views
  • 48 Minutes Discussion Span
  • Latest Post Latest Post by tinstaafl
Member Avatar for tinstaafl

If I understand your code right you need something like this:

                    Dim rcount As Integer = dgvUserDetails.Rows.Count
                    For Each row As DataGridViewRow In dgvUserDetails.Rows
                        If Not row.IsNewRow Then
                            VNO = Me.dgvUserDetails.Rows(e.RowIndex).Cells(1).Value
                            sql = "Select ID from datafile where VNO = " & VNO & ""
                            If rs.State = 1 Then rs.Close()
                            rs.Open(sql, MainCon, 1, 3)
                            If Not rs.EOF Then
                                dataid = rs.Fields(0).Value
                            End If

                            If e.ColumnIndex = 9 Then
                                For I = 9 to 11
                                    Select I
                                        Case 9                                      
                                            If Me.dgvUserDetails.Rows(e.RowIndex).Cells(9).Value.ToString <> "" Then
                                                caste = Me.dgvUserDetails.Rows(e.RowIndex).Cells(9).Value.ToString
                                                sql = "Select ID from Category where Desc = '" & caste & "'"
                                                If rs.State = 1 Then rs.Close()
                                                rs.Open(sql, MainCon, 1, 3)
                                                If Not rs.EOF Then
                                                    C_Id = rs.Fields(0).Value
                                                End If
                                                sql = "update datafile set caste = " & C_Id & " where ID = " & dataid & ""
                                                MainCon.Execute(sql)
                                            End If
                                        Case 10
                                            If Me.dgvUserDetails.Rows(e.RowIndex).Cells(10).Value.ToString <> "" Then
                                                prof = Me.dgvUserDetails.Rows(e.RowIndex).Cells(10).Value.ToString
                                                sql = "Select ID from Category where Desc = '" & prof & "'"
                                                If rs.State = 1 Then rs.Close()
                                                rs.Open(sql, MainCon, 1, 3)
                                                If Not rs.EOF Then
                                                    P_Id = rs.Fields(0).Value
                                                End If
                                                sql = "update datafile set prof = " & P_Id & " where ID = " & dataid & ""
                                                MainCon.Execute(sql)
                                            End If
                                        Case 11
                                            If Me.dgvUserDetails.Rows(e.RowIndex).Cells(11).Value.ToString <> "" Then
                                                degree = Me.dgvUserDetails.Rows(e.RowIndex).Cells(11).Value.ToString
                                                sql = "Select ID from Category where Desc = '" & degree & "'"
                                                If rs.State = 1 Then rs.Close()
                                                rs.Open(sql, MainCon, 1, 3)
                                                If Not rs.EOF Then
                                                    D_Id = rs.Fields(0).Value
                                                End If
                                                sql = "update datafile set degree = " & D_Id & " where ID = " & dataid & ""
                                                MainCon.Execute(sql)
                                            End If
                            End If
                        End If
                    Next
Member Avatar for tinstaafl

If that works please remember to mark this solved. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.