I want to try and store distinct values of table1 in an array and compare that with values in table2 in each field. But I am not sure where I need to pass the sql statement..can u please take a look at it ? for that matter..i will just have one extra field in table2 which has the user's name..that's it..rest of the fields are the same in table 1 and 2. when i hit the click button i keep getting the msg "no values to highlight.." :(
Imports System.Data.OleDb
Imports System.Windows.forms
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")
con.Open()
Dim lst As New List(Of String)
Dim values As New DataTable
Try
Dim cmd As New OleDbCommand
cmd = New OleDbCommand("SELECT * MFG FROM Table2 WHERE MFG IN (SELECT distinct MFG FROM Table1)", con)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Try
Dim ds As DataSet = New DataSet()
da.Fill(ds, "table2")
DataGridView1.DataSource = ds.Tables("table2").DefaultView
Finally
con.Close()
cmd = Nothing
da.Dispose()
con.Dispose()
End Try
con.Close()
cmd = Nothing
con.Dispose()
For Each r As DataRow In values.Rows
lst.Add(r("MFG").ToString)
Next
Catch ex As Exception
lst.Clear()
End Try
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.ToUpper) Then
r.Cells("MFG").Style.BackColor = Color.Green
End If
End If
Next
Else
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
End Sub
End Class