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.SqlSe rver
Imports System
Imports System.Data
Imports system.Data.Sql Client

Public Class Form1

Private Sub btnProcess_Clic k(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnProcess.Clic k

Dim connection As New
System.Data.Sql Client.SqlConne ction("Server=V ISTADAN;Databas e=BlueForceImpo rt;Trusted_Conn ection=True")
Dim connection2 As New
System.Data.Sql Client.SqlConne ction("Server=P ortal;Database= SLDemoApp60;Tru sted_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", "tblEmploye es")

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

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

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

' Fill the DataSet.
Dim NewEmployees As DataSet = New DataSet("Employ ees")
EmployeeAdapter .Fill(NewEmploy ees)


' Create a SqlDataAdapter for the old Employee table.
Dim OldEmployeeAdap ter As SqlDataAdapter = New SqlDataAdapter( )

' A table mapping names the DataTable
OldEmployeeAdap ter.TableMappin gs.Add("Table", "PJEMPLOY")

' Open the Connection
connection2.Ope n()
MsgBox("SQLConn ection2 is open.")

' Create a SQLCommand to retrieve Employee Data
Dim OldEmployeeComm and As SqlCommand = New SqlCommand("SEL ECT * FROM
PJEMPLOY;", connection2)
OldEmployeeComm and.CommandType = CommandType.Tex t

' Set the SqlDataAdapter' s SelectCommand.
OldEmployeeAdap ter.SelectComma nd = OldEmployeeComm and

' Fill the DataSet.
Dim OldEmployees As DataSet = New DataSet("OldEmp loyees")
OldEmployeeAdap ter.Fill(OldEmp loyees)

connection.Clos e()
MsgBox("SQLConn ection1 is closed.")

connection2.Clo se()
MsgBox("SQLConn ection2 is closed.")



End Sub


End Class

[Cor Ligthert[MVP]]

Re: Help with datasets

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" <DanShepherd@di scussions.micro soft.comschreef in bericht
news:4847702D-F969-4CA6-AEEC-5FE5143B251F@mi crosoft.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.SqlSe rver
Imports System
Imports System.Data
Imports system.Data.Sql Client
>
Public Class Form1
>
Private Sub btnProcess_Clic k(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnProcess.Clic k
>
Dim connection As New
System.Data.Sql Client.SqlConne ction("Server=V ISTADAN;Databas e=BlueForceImpo rt;Trusted_Conn ection=True")
Dim connection2 As New
System.Data.Sql Client.SqlConne ction("Server=P ortal;Database= SLDemoApp60;Tru sted_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", "tblEmploye es")
>
' Open the Connection
connection.Open ()
MsgBox("SQLConn ection1 is open.")
>
' Create a SQLCommand to retrieve Employee Data
Dim EmployeeCommand As SqlCommand = New SqlCommand("SEL ECT * FROM
tblEmployees;", connection)
EmployeeCommand .CommandType = CommandType.Tex t
>
' Set the SqlDataAdapter' s SelectCommand.
EmployeeAdapter .SelectCommand = EmployeeCommand
>
' Fill the DataSet.
Dim NewEmployees As DataSet = New DataSet("Employ ees")
EmployeeAdapter .Fill(NewEmploy ees)
>
>
' Create a SqlDataAdapter for the old Employee table.
Dim OldEmployeeAdap ter As SqlDataAdapter = New SqlDataAdapter( )
>
' A table mapping names the DataTable
OldEmployeeAdap ter.TableMappin gs.Add("Table", "PJEMPLOY")
>
' Open the Connection
connection2.Ope n()
MsgBox("SQLConn ection2 is open.")
>
' Create a SQLCommand to retrieve Employee Data
Dim OldEmployeeComm and As SqlCommand = New SqlCommand("SEL ECT *
FROM
PJEMPLOY;", connection2)
OldEmployeeComm and.CommandType = CommandType.Tex t
>
' Set the SqlDataAdapter' s SelectCommand.
OldEmployeeAdap ter.SelectComma nd = OldEmployeeComm and
>
' Fill the DataSet.
Dim OldEmployees As DataSet = New DataSet("OldEmp loyees")
OldEmployeeAdap ter.Fill(OldEmp loyees)
>
connection.Clos e()
MsgBox("SQLConn ection1 is closed.")
>
connection2.Clo se()
MsgBox("SQLConn ection2 is closed.")
>
>
>
End Sub
>
>
End Class
>