i have 2 tables with same fields except that table2 has 1 additional field..but otherwise table1 and table2 have same fields and have different number of records in each and each row in each table could be the same or completely different..

i am trying to add the distinct values of one field in table1 and compare it with the values of the same field in table2 which exists in the datagridview. I am making a mistake in the cmd..i am not sure where..can u pls help me with this ?

Public Class Form2

    Private Function GetMFGValues() As List(Of String)

        Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Final Database.mdb")


        Dim lst As New List(Of String)

        Dim values As New DataTable

        Dim cmd As New OleDbCommand

        cmd = New OleDbCommand("SELECT distinct MFG from table1", con)

            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

            Dim ds As DataSet = New DataSet()

            da.Fill(ds, "table2")

            DataGridView1.DataSource = ds.Tables("table2").DefaultView

        For Each r As DataRow In values.Rows




        Return lst

    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim ValidList As List(Of String) = GetMFGValues()

        If ValidList.Count > 0 Then

            For Each r As DataGridViewRow In DataGridView1.Rows

                If r.IsNewRow = False AndAlso Not TypeOf (r.Cells("MFG").Value) Is DBNull Then

                    If ValidList.Contains(r.Cells("MFG").Value.ToString) Then

                        r.Cells("MFG").Style.BackColor = Color.Green

                    End If

                End If



            MessageBox.Show("There are no values to highlight")

        End If

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


    End Sub

End Class

It seems to me you are clearing your list before returnging it from the function. Is that what you want to do?

Actually, the more I look at your code, the more confused I get. You have a select statement from Table1, but then you put it into a dataset with a table name of Table2. Then you put the values into a generic list, but then clear the list before returning it???

asnider..u r right.. lst.clear had to be under a catch exception.. i am modifying my code now.. i am gonna post it again..sorry abt that..

So did that solve the problem?

ya snider got it.. :) i shall post the soln as soon as i get a chance..