Search code examples
vb.netdatagridviewoledboledbcommandexecutereader

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

screenshot DATAGRIDVIEW


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