Help with datasets

[=?Utf-8?B?RGFuIFNoZXBoZXJk?=]

I am playing around trying to learn how to manipulate data in VB 2005. I
have code set to open up two db connections and pull in select data. Now
what I need to do is loop through table 1, field 1 and compare it to table 2,
field 2 and do some logical processing if the value exists.. I think I need
to use a While / for loop but not sure where to add it in. Basically I want
to look at table 1, field 1 compare it to table 2, field 2 and , if it does
not exist, write it to a file. If it does exist then continue on to the next
record.

Here is my code so far:
Imports Microsoft.SqlServer
Imports System
Imports System.Data
Imports system.Data.SqlClient

Public Class Form1

Private Sub btnProcess_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnProcess.Click

Dim connection As New
System.Data.SqlClient.SqlConnection("Server=VISTAD AN;Database=BlueForceImport;Trusted_Connection=Tru e")
Dim connection2 As New
System.Data.SqlClient.SqlConnection("Server=Portal ;Database=SLDemoApp60;Trusted_Connection=True")

' Create a SqlDataAdapter for the Employee table.
Dim EmployeeAdapter As SqlDataAdapter = New SqlDataAdapter()

' A table mapping names the DataTable
EmployeeAdapter.TableMappings.Add("Table", "tblEmployees")

' Open the Connection
connection.Open()
MsgBox("SQLConnection1 is open.")

' Create a SQLCommand to retrieve Employee Data
Dim EmployeeCommand As SqlCommand = New SqlCommand("SELECT * FROM
tblEmployees;", connection)
EmployeeCommand.CommandType = CommandType.Text

' Set the SqlDataAdapter's SelectCommand.
EmployeeAdapter.SelectCommand = EmployeeCommand

' Fill the DataSet.
Dim NewEmployees As DataSet = New DataSet("Employees")
EmployeeAdapter.Fill(NewEmployees)
' Create a SqlDataAdapter for the old Employee table.
Dim OldEmployeeAdapter As SqlDataAdapter = New SqlDataAdapter()

' A table mapping names the DataTable
OldEmployeeAdapter.TableMappings.Add("Table", "PJEMPLOY")

' Open the Connection
connection2.Open()
MsgBox("SQLConnection2 is open.")

' Create a SQLCommand to retrieve Employee Data
Dim OldEmployeeCommand As SqlCommand = New SqlCommand("SELECT * FROM
PJEMPLOY;", connection2)
OldEmployeeCommand.CommandType = CommandType.Text

' Set the SqlDataAdapter's SelectCommand.
OldEmployeeAdapter.SelectCommand = OldEmployeeCommand

' Fill the DataSet.
Dim OldEmployees As DataSet = New DataSet("OldEmployees")
OldEmployeeAdapter.Fill(OldEmployees)

connection.Close()
MsgBox("SQLConnection1 is closed.")

connection2.Close()
MsgBox("SQLConnection2 is closed.")

End Sub
End Class

[Cor Ligthert[MVP]]

Dan,

For this kind of compares the do while is not the best option anymore in VB.

The For and ForEach is so strong that it does everything by instance,

\\
ForEach dr1 as DataRow in Table1.Rows
ForEach dr2 as DataRow in Table2.Rows
'Do what you want to do between dr2 and dr1
Next
Next
///

Don't be afraid that this is slow, you cannot get it faster, behind the
scene it will forever something like this.

Cor

"Dan Shepherd" <Da*********@discussions.microsoft.comschreef in bericht
news:48**********************************@microsof t.com...

>I am playing around trying to learn how to manipulate data in VB 2005. I
have code set to open up two db connections and pull in select data. Now
what I need to do is loop through table 1, field 1 and compare it to table
2,
field 2 and do some logical processing if the value exists.. I think I
need
to use a While / for loop but not sure where to add it in. Basically I
want
to look at table 1, field 1 compare it to table 2, field 2 and , if it
does
not exist, write it to a file. If it does exist then continue on to the
next
record.

Here is my code so far:
Imports Microsoft.SqlServer
Imports System
Imports System.Data
Imports system.Data.SqlClient

Public Class Form1

Private Sub btnProcess_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnProcess.Click

Dim connection As New
System.Data.SqlClient.SqlConnection("Server=VISTAD AN;Database=BlueForceImport;Trusted_Connection=Tru e")
Dim connection2 As New
System.Data.SqlClient.SqlConnection("Server=Portal ;Database=SLDemoApp60;Trusted_Connection=True")

' Create a SqlDataAdapter for the Employee table.
Dim EmployeeAdapter As SqlDataAdapter = New SqlDataAdapter()

' A table mapping names the DataTable
EmployeeAdapter.TableMappings.Add("Table", "tblEmployees")

' Open the Connection
connection.Open()
MsgBox("SQLConnection1 is open.")

' Create a SQLCommand to retrieve Employee Data
Dim EmployeeCommand As SqlCommand = New SqlCommand("SELECT * FROM
tblEmployees;", connection)
EmployeeCommand.CommandType = CommandType.Text

' Set the SqlDataAdapter's SelectCommand.
EmployeeAdapter.SelectCommand = EmployeeCommand

' Fill the DataSet.
Dim NewEmployees As DataSet = New DataSet("Employees")
EmployeeAdapter.Fill(NewEmployees)
' Create a SqlDataAdapter for the old Employee table.
Dim OldEmployeeAdapter As SqlDataAdapter = New SqlDataAdapter()

' A table mapping names the DataTable
OldEmployeeAdapter.TableMappings.Add("Table", "PJEMPLOY")

' Open the Connection
connection2.Open()
MsgBox("SQLConnection2 is open.")

' Create a SQLCommand to retrieve Employee Data
Dim OldEmployeeCommand As SqlCommand = New SqlCommand("SELECT *
FROM
PJEMPLOY;", connection2)
OldEmployeeCommand.CommandType = CommandType.Text

' Set the SqlDataAdapter's SelectCommand.
OldEmployeeAdapter.SelectCommand = OldEmployeeCommand

' Fill the DataSet.
Dim OldEmployees As DataSet = New DataSet("OldEmployees")
OldEmployeeAdapter.Fill(OldEmployees)

connection.Close()
MsgBox("SQLConnection1 is closed.")

connection2.Close()
MsgBox("SQLConnection2 is closed.")

End Sub
End Class