vb.net datagridview oledb oledbcommand executereader

how to use cellendedit in multi column datagridview with multi oledb command in vb.net

I'm trying to use cellendedit in multi column datagridview with multi oledb command in vb.net

I tried the code below it doesn't produce anything in datagridview and if I use only one datagridview column with one oledb command then it appears in datagridview.

I have the code below, but this is still wrong.

please guide me

Thanks

Public Class Form1
    Dim dr1 As OleDbDataReader
    Dim dr2 As OleDbDataReader
    Dim dr3 As OleDbDataReader
    Public Function GetConnectionString2() As String
        Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIAL.accdb;Persist Security Info=False;"
        Return strCon
    End Function
    Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        If DataGridView1.Columns(e.ColumnIndex) Is DataGridView1.Columns("CODEPRODUCT") AndAlso DataGridView1.Columns(e.ColumnIndex) Is DataGridView1.Columns("SIZEPRODUCT") Then
            Using _conn As New OleDbConnection(GetConnectionString2)
                Using cmd1 As New OleDbCommand("select * from [PRODUCT] where CODEPRODUCT='" & DataGridView1.Rows(e.RowIndex).Cells("CODEPRODUCT").Value.ToString() & "'", _conn)
                    Using cmd2 As New OleDbCommand("select * from [SIZEPRODUCT] where SIZEPRODUCT='" & DataGridView1.Rows(e.RowIndex).Cells("SIZEPRODUCT").Value.ToString() & "'", _conn)
                        Using cmd3 As New OleDbCommand("select * from [COLORCODE] where COLORCODE='" & DataGridView1.Rows(e.RowIndex).Cells("COLORCODE").Value.ToString() & "'", _conn)
                            Try
                                _conn.Open()
                                dr1 = cmd1.ExecuteReader
                                dr2 = cmd2.ExecuteReader
                                dr3 = cmd2.ExecuteReader
                                dr1.Read()
                                dr2.Read()
                                dr3.Read()
                                If dr1.HasRows AndAlso dr2.HasRows AndAlso dr3.HasRows Then
                                    DataGridView1.Rows(e.RowIndex).Cells("DESCRIPTION").Value = dr1.Item("DESCRIPTION")
                                    DataGridView1.Rows(e.RowIndex).Cells("CATEGORY").Value = dr2.Item("CATEGORY")
                                    DataGridView1.Rows(e.RowIndex).Cells("COLORNAME").Value = dr3.Item("COLORNAME")
                                Else
                                    MsgBox("Not found")
                                End If
                            Catch ex As Exception
                                MessageBox.Show(ex.Message)
                            End Try
                        End Using
                    End Using
                End Using
            End Using
        End If
    End Sub
End Class

Update code

Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        Using _conn As New OleDbConnection(GetConnectionString2)
If DataGridView1.Columns(e.ColumnIndex) Is DataGridView1.Columns("CODEPRODUCT") OrElse DataGridView1.Columns(e.ColumnIndex) Is DataGridView1.Columns("SIZEPRODUCT") OrElse DataGridView1.Columns(e.ColumnIndex) Is DataGridView1.Columns("COLORCODE") Then
                Using cmd1 As New OleDbCommand("select * from [PRODUCT] where CODEPRODUCT='" & DataGridView1.Rows(e.RowIndex).Cells("CODEPRODUCT").Value.ToString() & "'", _conn)
                    'Using cmd2 As New OleDbCommand("select * from [SIZEPRODUCT] where SIZEPRODUCT='" & DataGridView1.Rows(e.RowIndex).Cells("SIZEPRODUCT").Value.ToString() & "'", _conn)
                    'Using cmd3 As New OleDbCommand("select * from [COLORCODE] where COLORCODE='" & cstr(DataGridView1.Rows(e.RowIndex).Cells("COLORCODE").Value).ToString() & "'", _conn)
                    Try
                        _conn.Open()
                        dr1 = cmd1.ExecuteReader
                        'dr2 = cmd2.ExecuteReader
                        'dr3 = cmd3.ExecuteReader
                        If dr1.Read OrElse dr2.Read OrElse dr3.Read Then
                            DataGridView1.Rows(e.RowIndex).Cells("DESCRIPTION").Value = dr1.Item("DESCRIPTION")
                            ''DataGridView1.Rows(e.RowIndex).Cells("CATEGORY").Value = dr2.Item("CATEGORY")
                            'DataGridView1.Rows(e.RowIndex).Cells("COLORNAME").Value = dr3.Item("COLORNAME")
                        Else
                            MsgBox("Not found")
                        End If
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
                End Using
                'End Using
                ''End Using
            End If
        End Using
    End Sub

Solution

If the idea here is to run the three queries only when the user enters/selects all the required and correct identifiers of the three tables, then you can execute just one query instead of three to select the required fields from the three tables.

Assuming unbound grid, the fishy table and field names are correct, and parameters value of type String. You could write for example:

Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    Dim dgv = DirectCast(sender, DataGridView)
    Dim KeyCols = {"CodeProduct", "SizeProduct", "ColorCode"}
    Dim ValueCols = {"Description", "Category", "ColorName"}

    If e.RowIndex >= 0 AndAlso KeyCols.Any(Function(col) col = dgv.Columns(e.ColumnIndex).Name) Then
        Dim dict = dgv.Rows(e.RowIndex).Cells.OfType(Of DataGridViewTextBoxCell).
        Where(Function(cell) KeyCols.Any(Function(col) col = cell.OwningColumn.Name)).
        ToDictionary(Function(cell) cell.OwningColumn.Name, Function(cell) cell.Value)

        If dict.Values.Any(Function(v) v Is Nothing OrElse String.IsNullOrEmpty(v.ToString())) Then
            Return
        End If

        Dim sql =
        <sql>
            SELECT Product.Description, SizeProduct.Category, ColorCode.ColorName
            FROM Product, SizeProduct, ColorCode
            WHERE Product.CodeProduct = ?
            AND SizeProduct.SizeProduct = ?
            AND ColorCode.ColorCode = ?
        </sql>.Value

        Using con = New OleDbConnection("..."), cmd = New OleDbCommand(sql, con)
            cmd.Parameters.AddRange(
            dict.Select(Function(kvp) New OleDbParameter("?", OleDbType.VarWChar) With {
                .Value = kvp.Value
            }).ToArray())

            con.Open()

            Using rdr = cmd.ExecuteReader()
                If rdr.Read() Then
                    For Each col In ValueCols
                        dgv(col, e.RowIndex).Value = rdr(col)
                    Next
                Else
                    MessageBox.Show("one or more wrong identifiers.")
                End If
            End Using
        End Using
    End If
End Sub

Note here, this query will return a single record to read only if all the passed CodeProduct, SizeProduct, and ColorCode identifiers are valid. The alternative - as noted - is to run a separate query for each table when you get the related id/code changed in the grid.

Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    If e.RowIndex < 0 OrElse e.RowIndex < 0 Then Return
    Dim dgv = DirectCast(sender, DataGridView)
    Dim row = dgv.Rows(e.RowIndex)
    Dim arrQueries() = {
        New With {
            .KeyCell = row.Cells("CodeProduct"),
            .ValueCell = row.Cells("Description"),
            .Table = "Product"},
        New With {
            .KeyCell = row.Cells("SizeProduct"),
            .ValueCell = row.Cells("Category"),
            .Table = "SizeProduct"},
        New With {
            .KeyCell = row.Cells("ColorCode"),
            .ValueCell = row.Cells("ColorName"),
            .Table = "ColorCode"}
    }

    If Not arrQueries.Any(
        Function(q) q.KeyCell.OwningColumn Is dgv.Columns(e.ColumnIndex)) Then
        Return
    End If

    Using con = New OleDbConnection("..."), cmd = con.CreateCommand()
        con.Open()
        For Each q In arrQueries
            If q.KeyCell.Value IsNot Nothing AndAlso
                q.KeyCell.Value IsNot DBNull.Value AndAlso
                Not String.IsNullOrEmpty(q.KeyCell.Value.ToString()) Then
                cmd.CommandText = String.Format(
                    "SELECT {0} FROM {1} WHERE {2} = ?",
                    q.ValueCell.OwningColumn.DataPropertyName,
                    q.Table,
                    q.KeyCell.OwningColumn.DataPropertyName)
                cmd.Parameters.Clear()
                cmd.Parameters.Add("?", OleDbType.VarWChar).Value = q.KeyCell.Value
                q.ValueCell.Value = cmd.ExecuteScalar()
            Else
                q.ValueCell.Value = Nothing
            End If
        Next
    End Using
End Sub