I need to compare the values of two datasets and retrieve the differences
into a third dataset.

I need to retrieve Employees names and numbers from Excel sheet into ds1
and retrieve Employees names and numbers from SQL Employees Table into ds2.
I did this part successfully.

Now i want to compare the values of both datasets so I can figure out new
added employees in the Excel sheet and add them into ds3. How can I do that?

I know i need to loop through one and look for each record in the other but
I can't get the idea ... can anyone give me a sample of how to do this?

Ali,

You can use two kind of procedures.

The classic loop and access than both tables sequential.

Or just use the dataview.find to get the number in a for each loop from the
searching table in the table to search (what I prefer). If you don't find it
than you have to add the row of the searcher in the extra table.

http://msdn.microsoft.com/library/de...sfindtopic.asp

I hope this helps,

Cor

Dear Cor,
Thanx for your quick response.

I just need to know if all the values in ds1 ( retrieved from Excel Sheet)
exist in ds2 ( retrieved from SQL Employees table). if not, return the
non-existing employees and add them into ds3 and view it in datagridview.

your example doesn't look work for me.. If so, can u give me an example of
how to use it???

Thanx
"Cor Ligthert [MVP]" wrote:

> Ali,
>
> You can use two kind of procedures.
>
> The classic loop and access than both tables sequential.
>
> Or just use the dataview.find to get the number in a for each loop from the
> searching table in the table to search (what I prefer). If you don't find it
> than you have to add the row of the searcher in the extra table.
>
> http://msdn.microsoft.com/library/de...sfindtopic.asp
>
> I hope this helps,
>
> Cor
>
>
>

Ali,

> your example doesn't look work for me.. If so, can u give me an example of
> how to use it???

Can you show what does not work?

Cor

Ali,

Do something like this --

ds2.AcceptChanges
ds2.Merge(ds1,true)
ds2.GetChanges

You will however have to specify schemas on all these datasets, and if you
are workign with multiple tables - you will need to get rid of relatioships
or GetChanges may include unchanged rows as well (to maintain relational
sanctity)

HTH


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
----------------------------------------------------------------------------


"Ali" <(E-Mail Removed)> wrote in message
news:E57C4418-2B13-491F-A1DB-(E-Mail Removed)...
> Dear Cor,
> Thanx for your quick response.
>
> I just need to know if all the values in ds1 ( retrieved from Excel Sheet)
> exist in ds2 ( retrieved from SQL Employees table). if not, return the
> non-existing employees and add them into ds3 and view it in datagridview.
>
> your example doesn't look work for me.. If so, can u give me an example of
> how to use it???
>
> Thanx
> "Cor Ligthert [MVP]" wrote:
>
>> Ali,
>>
>> You can use two kind of procedures.
>>
>> The classic loop and access than both tables sequential.
>>
>> Or just use the dataview.find to get the number in a for each loop from
>> the
>> searching table in the table to search (what I prefer). If you don't find
>> it
>> than you have to add the row of the searcher in the extra table.
>>
>> http://msdn.microsoft.com/library/de...sfindtopic.asp
>>
>> I hope this helps,
>>
>> Cor
>>
>>
>>

I have done this so far. the problem is that I don't know how to loop
properly. I just need to ckeck if all the values in ds( retrieved from Excel
sheet) exist in ds1 ( retrieved from SQL Table). if not, retrun non-
existing employees in ds3.

All the values of ds have to be in ds1 ... but oppoiste might not be true. (
means ds1 always greater than ds ).



Dim sConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data
source=c:\PayrollJVOct2005.xls;" & _
"Extended properties=""Excel 8.0;
HDR=Yes;"""


Dim jConString As String = "Data Source=JDE;Initial
Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
Dim jCon As New System.Data.SqlClient.SqlConnection(jConString)
Dim Jda As New System.Data.SqlClient.SqlDataAdapter
Dim Con As New System.Data.OleDb.OleDbConnection(sConString)
Con.Open()


Dim cmdSelect As New System.Data.OleDb.OleDbCommand("select * from
[sheet1$]", Con)
Dim da As New System.Data.OleDb.OleDbDataAdapter
da.SelectCommand = cmdSelect
Dim ds As New System.Data.DataSet
da.Fill(ds)
Con.Close()
Con.Dispose()
DR = ds.Tables(0).Rows(0)


Dim jConString2 As String = "Data Source=EN;Initial
Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
Dim jCon2 As New System.Data.SqlClient.SqlConnection(jConString2)
Dim Jda2 As New System.Data.SqlClient.SqlDataAdapter("Select aban8
from HAIFDTA.[F0101] where ABAT1='E'", jCon2)
jCon2.Open()
Jda2.Fill(ds1)
jCon2.Close()
jCon2.Dispose()

DrEmployee = ds1.Tables(0).Rows(0)

Dim m As Integer
For i = 1 To ds.Tables(0).Rows.Count

-- check code here <=== here where I am stuck
Next




"Cor Ligthert [MVP]" wrote:

> Ali,
>
> > your example doesn't look work for me.. If so, can u give me an example of
> > how to use it???
>
> Can you show what does not work?
>
> Cor
>
>
>

Ali,

I made a sample for you (I use datatables, with a dataset is the datatable
just ds.tables(0)

By the way, you need only a close or a dispose. I use only the close.

\\\
'Creating a testset
Dim dt1 As New DataTable
Dim dt2 As New DataTable
dt1.Columns.Add("Customers")
dt2.Columns.Add("Customers")
For i As Integer = 0 To 20
dt1.LoadDataRow(New Object() {i.ToString}, True)
dt2.LoadDataRow(New Object() {i.ToString}, True)
Next
dt2.Rows(4).Delete()
dt2.Rows(9).Delete()

'Start of sample
Dim dt3 As DataTable = dt1.Clone
Dim dv As DataView(dt2)
dv.Sort = "Customers"
For Each dr As DataRow In dt1.Rows
If dv.Find(dr("Customers")) = -1 Then
dt3.ImportRow(dr)
End If
Next
///

I hope this helps,

Cor

"Ali" <(E-Mail Removed)> schreef in bericht
news:5D0E2E98-9706-4D8A-9E82-(E-Mail Removed)...
>I have done this so far. the problem is that I don't know how to loop
> properly. I just need to ckeck if all the values in ds( retrieved from
> Excel
> sheet) exist in ds1 ( retrieved from SQL Table). if not, retrun non-
> existing employees in ds3.
>
> All the values of ds have to be in ds1 ... but oppoiste might not be true.
> (
> means ds1 always greater than ds ).
>
>
>
> Dim sConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "data
> source=c:\PayrollJVOct2005.xls;" & _
> "Extended properties=""Excel 8.0;
> HDR=Yes;"""
>
>
> Dim jConString As String = "Data Source=JDE;Initial
> Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> Dim jCon As New System.Data.SqlClient.SqlConnection(jConString)
> Dim Jda As New System.Data.SqlClient.SqlDataAdapter
> Dim Con As New System.Data.OleDb.OleDbConnection(sConString)
> Con.Open()
>
>
> Dim cmdSelect As New System.Data.OleDb.OleDbCommand("select * from
> [sheet1$]", Con)
> Dim da As New System.Data.OleDb.OleDbDataAdapter
> da.SelectCommand = cmdSelect
> Dim ds As New System.Data.DataSet
> da.Fill(ds)
> Con.Close()
> Con.Dispose()
> DR = ds.Tables(0).Rows(0)
>
>
> Dim jConString2 As String = "Data Source=EN;Initial
> Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> Dim jCon2 As New System.Data.SqlClient.SqlConnection(jConString2)
> Dim Jda2 As New System.Data.SqlClient.SqlDataAdapter("Select aban8
> from HAIFDTA.[F0101] where ABAT1='E'", jCon2)
> jCon2.Open()
> Jda2.Fill(ds1)
> jCon2.Close()
> jCon2.Dispose()
>
> DrEmployee = ds1.Tables(0).Rows(0)
>
> Dim m As Integer
> For i = 1 To ds.Tables(0).Rows.Count
>
> -- check code here <=== here where I am stuck
> Next
>
>
>
>
> "Cor Ligthert [MVP]" wrote:
>
>> Ali,
>>
>> > your example doesn't look work for me.. If so, can u give me an example
>> > of
>> > how to use it???
>>
>> Can you show what does not work?
>>
>> Cor
>>
>>
>>

The sample returns the difference between the two .. in my case I have in
dt1 some values which I don't want to include in my comparison. Because for
me , dt2 is the payroll for all employees , and dt1 is for all employees even
those who left the company, inactive.

in my case:

all values of dt2 ( Employees Numbers) should be part of dt1 but the
opposite is not true. so the difference between both tables won't work. your
suggestion is highly appreciated. Thanx


"Cor Ligthert [MVP]" wrote:

> Ali,
>
> I made a sample for you (I use datatables, with a dataset is the datatable
> just ds.tables(0)
>
> By the way, you need only a close or a dispose. I use only the close.
>
> \\\
> 'Creating a testset
> Dim dt1 As New DataTable
> Dim dt2 As New DataTable
> dt1.Columns.Add("Customers")
> dt2.Columns.Add("Customers")
> For i As Integer = 0 To 20
> dt1.LoadDataRow(New Object() {i.ToString}, True)
> dt2.LoadDataRow(New Object() {i.ToString}, True)
> Next
> dt2.Rows(4).Delete()
> dt2.Rows(9).Delete()
>
> 'Start of sample
> Dim dt3 As DataTable = dt1.Clone
> Dim dv As DataView(dt2)
> dv.Sort = "Customers"
> For Each dr As DataRow In dt1.Rows
> If dv.Find(dr("Customers")) = -1 Then
> dt3.ImportRow(dr)
> End If
> Next
> ///
>
> I hope this helps,
>
> Cor
>
> "Ali" <(E-Mail Removed)> schreef in bericht
> news:5D0E2E98-9706-4D8A-9E82-(E-Mail Removed)...
> >I have done this so far. the problem is that I don't know how to loop
> > properly. I just need to ckeck if all the values in ds( retrieved from
> > Excel
> > sheet) exist in ds1 ( retrieved from SQL Table). if not, retrun non-
> > existing employees in ds3.
> >
> > All the values of ds have to be in ds1 ... but oppoiste might not be true.
> > (
> > means ds1 always greater than ds ).
> >
> >
> >
> > Dim sConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "data
> > source=c:\PayrollJVOct2005.xls;" & _
> > "Extended properties=""Excel 8.0;
> > HDR=Yes;"""
> >
> >
> > Dim jConString As String = "Data Source=JDE;Initial
> > Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> > Dim jCon As New System.Data.SqlClient.SqlConnection(jConString)
> > Dim Jda As New System.Data.SqlClient.SqlDataAdapter
> > Dim Con As New System.Data.OleDb.OleDbConnection(sConString)
> > Con.Open()
> >
> >
> > Dim cmdSelect As New System.Data.OleDb.OleDbCommand("select * from
> > [sheet1$]", Con)
> > Dim da As New System.Data.OleDb.OleDbDataAdapter
> > da.SelectCommand = cmdSelect
> > Dim ds As New System.Data.DataSet
> > da.Fill(ds)
> > Con.Close()
> > Con.Dispose()
> > DR = ds.Tables(0).Rows(0)
> >
> >
> > Dim jConString2 As String = "Data Source=EN;Initial
> > Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> > Dim jCon2 As New System.Data.SqlClient.SqlConnection(jConString2)
> > Dim Jda2 As New System.Data.SqlClient.SqlDataAdapter("Select aban8
> > from HAIFDTA.[F0101] where ABAT1='E'", jCon2)
> > jCon2.Open()
> > Jda2.Fill(ds1)
> > jCon2.Close()
> > jCon2.Dispose()
> >
> > DrEmployee = ds1.Tables(0).Rows(0)
> >
> > Dim m As Integer
> > For i = 1 To ds.Tables(0).Rows.Count
> >
> > -- check code here <=== here where I am stuck
> > Next
> >
> >
> >
> >
> > "Cor Ligthert [MVP]" wrote:
> >
> >> Ali,
> >>
> >> > your example doesn't look work for me.. If so, can u give me an example
> >> > of
> >> > how to use it???
> >>
> >> Can you show what does not work?
> >>
> >> Cor
> >>
> >>
> >>
>
>
>

Hi Cor Ligthert,

I really need someone help and ur the only one who responded to my request.
I really appreciate this.


The sample which u post returns the difference between the two datasets ..
in my case I have in
dt1 some values which I don't want to include in my comparison. Because for
me , dt2 is the payroll for all employees , and dt1 is for all employees even
those who left the company, inactive employees. so they will not be
included in my comparison.



in my case:

all values of dt2 ( Employees Numbers) should be part of dt1 but the
opposite is not true. so the difference between both tables won't work. your
suggestion is highly appreciated. Thanx


I write something like this but it did not work:
dr= ds.tables(0).rows
dr1= ds1.tables(0).rows
For i = 1 to ds.tables(0).rows.count
For j = 1 tp ds1.tables(0).rows.count
if dr(6) <> dr1(0)
j = j + 1
else
i = i + 1
Next
Next


"Cor Ligthert [MVP]" wrote:

> Ali,
>
> I made a sample for you (I use datatables, with a dataset is the datatable
> just ds.tables(0)
>
> By the way, you need only a close or a dispose. I use only the close.
>
> \\\
> 'Creating a testset
> Dim dt1 As New DataTable
> Dim dt2 As New DataTable
> dt1.Columns.Add("Customers")
> dt2.Columns.Add("Customers")
> For i As Integer = 0 To 20
> dt1.LoadDataRow(New Object() {i.ToString}, True)
> dt2.LoadDataRow(New Object() {i.ToString}, True)
> Next
> dt2.Rows(4).Delete()
> dt2.Rows(9).Delete()
>
> 'Start of sample
> Dim dt3 As DataTable = dt1.Clone
> Dim dv As DataView(dt2)
> dv.Sort = "Customers"
> For Each dr As DataRow In dt1.Rows
> If dv.Find(dr("Customers")) = -1 Then
> dt3.ImportRow(dr)
> End If
> Next
> ///
>
> I hope this helps,
>
> Cor
>
> "Ali" <(E-Mail Removed)> schreef in bericht
> news:5D0E2E98-9706-4D8A-9E82-(E-Mail Removed)...
> >I have done this so far. the problem is that I don't know how to loop
> > properly. I just need to ckeck if all the values in ds( retrieved from
> > Excel
> > sheet) exist in ds1 ( retrieved from SQL Table). if not, retrun non-
> > existing employees in ds3.
> >
> > All the values of ds have to be in ds1 ... but oppoiste might not be true.
> > (
> > means ds1 always greater than ds ).
> >
> >
> >
> > Dim sConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "data
> > source=c:\PayrollJVOct2005.xls;" & _
> > "Extended properties=""Excel 8.0;
> > HDR=Yes;"""
> >
> >
> > Dim jConString As String = "Data Source=JDE;Initial
> > Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> > Dim jCon As New System.Data.SqlClient.SqlConnection(jConString)
> > Dim Jda As New System.Data.SqlClient.SqlDataAdapter
> > Dim Con As New System.Data.OleDb.OleDbConnection(sConString)
> > Con.Open()
> >
> >
> > Dim cmdSelect As New System.Data.OleDb.OleDbCommand("select * from
> > [sheet1$]", Con)
> > Dim da As New System.Data.OleDb.OleDbDataAdapter
> > da.SelectCommand = cmdSelect
> > Dim ds As New System.Data.DataSet
> > da.Fill(ds)
> > Con.Close()
> > Con.Dispose()
> > DR = ds.Tables(0).Rows(0)
> >
> >
> > Dim jConString2 As String = "Data Source=EN;Initial
> > Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> > Dim jCon2 As New System.Data.SqlClient.SqlConnection(jConString2)
> > Dim Jda2 As New System.Data.SqlClient.SqlDataAdapter("Select aban8
> > from HAIFDTA.[F0101] where ABAT1='E'", jCon2)
> > jCon2.Open()
> > Jda2.Fill(ds1)
> > jCon2.Close()
> > jCon2.Dispose()
> >
> > DrEmployee = ds1.Tables(0).Rows(0)
> >
> > Dim m As Integer
> > For i = 1 To ds.Tables(0).Rows.Count
> >
> > -- check code here <=== here where I am stuck
> > Next
> >
> >
> >
> >
> > "Cor Ligthert [MVP]" wrote:
> >
> >> Ali,
> >>
> >> > your example doesn't look work for me.. If so, can u give me an example
> >> > of
> >> > how to use it???
> >>
> >> Can you show what does not work?
> >>
> >> Cor
> >>
> >>
> >>
>
>
>

Ali,

I am not sure if I understand you well. However if I do than it would be.

'Start of sample
Dim dt3 As DataTable = dt1.Clone
Dim dv As DataView(dt2)
dv.Sort = "Customers"
For Each dr As DataRow In dt1.Rows
if dr("StillInCompany) = true then
If dv.Find(dr("Customers")) = -1 Then
dt3.ImportRow(dr)
End If
end if
Next
///

However that is in my opinion something you should have to be able to do
yourself if you are making a program.

I hope this helps,

Cor