Fill up cells from values in table based on the value of another cell

[jared.geli]

Hi guys I have this problem where a certain cell will select a value from a table based on another cell (like an id or something). First I select some values to be put in Datagrid (a typical OleDBCommand) then add some more columns using Datagridview.Columns.Add. Here's the scenario I selected invoice_no, customer_no, customer_name, invoice_amnt from om_list table, after the values I selected shows in the Datagrid I'm going to add another column manually using datagridview.columns.add and named it date_deliver. Now what I want is to select date_deliver from cancel_discount table where invoice_no based on the invoice_no in the datagrid that I selected earlier from order table.

So far here's my work so far:

cmd = New OleDbCommand("Select invoice_no, internal_id, customer_id, account_name, seller_name, terms, date_invoice, segment From om_list", con)

        con.Open()
        adpt = New OleDbDataAdapter(cmd)
        'Here one CommandBuilder object is required.
        'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
        dtset = New DataSet()
        adpt.Fill(dtset)

        con.Close()

        DataGridView1.DataSource = dtset.Tables(0)

        Dim c8 As New DataGridViewTextBoxColumn
        c8.Name = "date_deliver"
        c8.ValueType = GetType(Date)
        DataGridView1.Columns.Add(c8)
        DataGridView1.Columns(8).DefaultCellStyle.Format = "MM/dd/yyyy"

        Dim cmd2 = New OleDbCommand("", con)

        Dim c9 As New DataGridViewTextBoxColumn
        c9.Name = "invoice_amnt"
        c9.ValueType = GetType(Decimal)
        DataGridView1.Columns.Add(c9)
        DataGridView1.Columns(9).DefaultCellStyle.Format = "c"

        Dim c10 As New DataGridViewTextBoxColumn
        c10.Name = "rgpi_deduction"
        c10.ValueType = GetType(Decimal)
        DataGridView1.Columns.Add(c10)
        DataGridView1.Columns(10).DefaultCellStyle.Format = "c"

        Dim c11 As New DataGridViewTextBoxColumn
        c11.Name = "mda_deduction"
        c11.ValueType = GetType(Decimal)
        DataGridView1.Columns.Add(c11)
        DataGridView1.Columns(11).DefaultCellStyle.Format = "c"

        Dim c12 As New DataGridViewTextBoxColumn
        c12.Name = "payment_applied"
        c12.ValueType = GetType(Decimal)
        DataGridView1.Columns.Add(c12)
        DataGridView1.Columns(12).DefaultCellStyle.Format = "c"

        Dim c13 As New DataGridViewTextBoxColumn
        c13.Name = "date_collected"
        c13.ValueType = GetType(Date)
        DataGridView1.Columns.Add(c13)
        DataGridView1.Columns(13).DefaultCellStyle.Format = "MM/dd/yyyy"

        For Each rw As DataGridViewRow In DataGridView1.Rows
            For i As Integer = 8 To rw.Cells.Count - 1
                If rw.Cells(i).ColumnIndex = 8 Then

                    Dim cmd As New OleDbCommand("Select date_deliver From cancel_discount Where invoice_no = '" & rw.Cells(0).Value & "'", con)
                    con.Open()
                    Dim sdr As OleDbDataReader = cmd.ExecuteReader()
                    If sdr.Read = True Then

                        rw.Cells("date_deliver").Value = sdr("date_deliver")

                    End If
                    con.Close()
                End If
            Next
        Next

The "date_deliver" value doesn't show on the supposed cell. The code is on form load event

[AleMonteiro]

Did you debug it? Is the line rw.Cells("date_deliver").Value = sdr("date_deliver") being executed?

Beside that, why are you making an loop for until 8 and only executing something when it's 8? It doesn't make a lot of sense to me.

I'd remove all that and increment your initial select to be like this:

SELECT 
    l.invoice_no, l.internal_id, l.customer_id, l.account_name, l.seller_name, l.terms, l.date_invoice, l.segment
    , IsNull(c.date_deliver, '') AS date_deliver
FROM
    om_list AS L
        LEFT JOIN cancel_discount AS C
            ON ( L.invoice_no = C.invoice_no )

[jared.geli]

Found the problem it doesn't have any value to based on. I should put the loop code on a DataGridView Event but I don't know what event should I use. About the 8 sorry I forgot to change it back it's supposed to be 0. Thanks your sql works but my original plan is to serach for 5 tables and I tried joining them all but didn't turn out quite well so my last resort was to load first the table then add columns then select the values based on the invoice_no in DataGridView.

[jared.geli]

Problem solved all I need to do was nest the Left Join loop. I just put the Left Joins in parantheses:

Select O.invoice_no as invoice_no, O.internal_id as internal_id, 

O.customer_id as customer_id, O.account_name as account_name, 

O.seller_name as seller_name, O.terms as terms, O.date_invoice as date_invoice, 

O.segment as segment, SUM(C.invoice_amnt) as invoice_amnt, 

Last(C.date_deliver) as date_deliver, SUM(P.payment_applied) as payment_applied, 

Last(P.date_collected) as date_collected, SUM(R.amount) as rgpi_deduction, 

SUM(M.amount) as mda_deduction From ((((om_list as O) 

Left Join cancel_discount as C on O.invoice_no = C.invoice_no) 

Left Join payment_applied as P on O.invoice_no = P.invoice_no) 

Left Join app_deduc_rgpi as R on O.invoice_no = R.invoice_no) 

Left Join app_deduc_mda as M on O.invoice_no = M.invoice_no 

Group By O.invoice_no, O.internal_id, O.customer_id, O.account_name, 

O.seller_name, O.terms, O.date_invoice, O.segment

Unlike SQL in MS Access I need to do this or else I will get a "Missing Operator" error

Source: http://nm1m.blogspot.com/2007/10/multiple-left-joins-in-ms-access.html