rogue_1
asked on
Compare multiple values
I have two datatables:
tbl_PlayersNumbers (multiple records)
Columns - UniqueKey(PK)(Autonumber),
tbl_WinningNumbers (one record)
Columns - WinningDrawing(PK)(date/ti
Next I need to compare Players1 to Winning1, Players1 to Winning 2, etc This covers all possiblities of Players and Winning. Each time that a combination matches, I need to add 1 to a varible called "Matched". Then loop thru each record in tbl_PlayersNumbers.
I think I can do this with If/Then statements but there has to be a better way. Thanks for any help.
tbl_PlayersNumbers (multiple records)
Columns - UniqueKey(PK)(Autonumber),
tbl_WinningNumbers (one record)
Columns - WinningDrawing(PK)(date/ti
Next I need to compare Players1 to Winning1, Players1 to Winning 2, etc This covers all possiblities of Players and Winning. Each time that a combination matches, I need to add 1 to a varible called "Matched". Then loop thru each record in tbl_PlayersNumbers.
I think I can do this with If/Then statements but there has to be a better way. Thanks for any help.
How about something like this? Not tested, by the way.
Dim Matched As Integer = 0
For i As Integer = 1 To 3
Dim thisWinner As Integer = tbl_WinningNumbers.Rows(0)
For j As Integer = 1 To 3
Dim thisColumn As String = tbl_PlayersNumbers.Columns
Dim dr() As DataRow = tbl_PlayersNumbers.Select(
Matched += dr.Length
Next j
Next i
MsgBox("Matched = " & Matched.ToString)
Note that it's specific to the example you gave - i.e. the 1 To 3 in the loops matches the column numbers in your tables as your example has set them out. You might also find it faster to use a filtered dataview rather than .Select on the table itself. But my code is intended to illustrate the basic idea: of letting inbuilt methods doing the matching and counting. It's a similar approach to Hillwaaa's in that respect, but done within the application rather than going back to the database.
Roger
Dim Matched As Integer = 0
For i As Integer = 1 To 3
Dim thisWinner As Integer = tbl_WinningNumbers.Rows(0)
For j As Integer = 1 To 3
Dim thisColumn As String = tbl_PlayersNumbers.Columns
Dim dr() As DataRow = tbl_PlayersNumbers.Select(
Matched += dr.Length
Next j
Next i
MsgBox("Matched = " & Matched.ToString)
Note that it's specific to the example you gave - i.e. the 1 To 3 in the loops matches the column numbers in your tables as your example has set them out. You might also find it faster to use a filtered dataview rather than .Select on the table itself. But my code is intended to illustrate the basic idea: of letting inbuilt methods doing the matching and counting. It's a similar approach to Hillwaaa's in that respect, but done within the application rather than going back to the database.
Roger
ASKER
Thanks for the responses.
Sancler,
I actually use a select statement first which works fine. So I am working with just the rows that I need. So I have tried your method and ran into a problem.
Here is the code:
Dim Matched As Integer = 0
For i As Integer = 1 To 3
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
For j As Integer = 1 To 3
Dim thisColumn As String = DsLotteryMod1.tbl_PlayersN
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
Next j
Next i
MsgBox("Matched = " & Matched.ToString)
I get this error:
Cannot perform '=' operation on System.String and System.Int32
on this line:
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Now I think I need to convert one of the values to match the other. However when I try it, it causes other errors. Thanks
Sancler,
I actually use a select statement first which works fine. So I am working with just the rows that I need. So I have tried your method and ran into a problem.
Here is the code:
Dim Matched As Integer = 0
For i As Integer = 1 To 3
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
For j As Integer = 1 To 3
Dim thisColumn As String = DsLotteryMod1.tbl_PlayersN
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
Next j
Next i
MsgBox("Matched = " & Matched.ToString)
I get this error:
Cannot perform '=' operation on System.String and System.Int32
on this line:
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Now I think I need to convert one of the values to match the other. However when I try it, it causes other errors. Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, my fault. I overlooked that. But I did say "Not tested, by the way" ;-)
Hillwaaa's first suggestion should work. Or
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Roger
Hillwaaa's first suggestion should work. Or
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Roger
ASKER
After quite a few days I have made some progress. Here is the code now:
Dim Matched As Integer = 0
For i As Integer = 0 To 4
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
For j As Integer = 2 To 6
Dim thisColumn As String = DsLotteryMod1.tbl_PlayersN
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
Next j
Next i
MsgBox("Matched = " & Matched.ToString)
Couple of things that I do not understand. This line of code always looks at the first record:
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
But I need it to look at the last record that was just added. I should have been a little clearer originally.
When it does the matching, it matches all the numbers for all the records at once. i would like to do some other things after it matches one record from tbl_Players. Such as, If Matched >= 1 then add a record to another table, etc. Would I need to make a change to this line of code:
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
Thanks for all the help. This has really been a huge help to a begginer.
Dim Matched As Integer = 0
For i As Integer = 0 To 4
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
For j As Integer = 2 To 6
Dim thisColumn As String = DsLotteryMod1.tbl_PlayersN
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
Next j
Next i
MsgBox("Matched = " & Matched.ToString)
Couple of things that I do not understand. This line of code always looks at the first record:
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
But I need it to look at the last record that was just added. I should have been a little clearer originally.
When it does the matching, it matches all the numbers for all the records at once. i would like to do some other things after it matches one record from tbl_Players. Such as, If Matched >= 1 then add a record to another table, etc. Would I need to make a change to this line of code:
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
Thanks for all the help. This has really been a huge help to a begginer.
>>
But I need it to look at the last record that was just added
<<
Just change the 0 in this line
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
to the index of the row most recently added. You will find that by
Dim lastRow As Integer = DsLotteryMod1.tbl_WinningN
A new row is always added to the end of a datatable, but as the index is zero based, its index will be one less than the count.
>>
i would like to do some other things after it matches one record from tbl_Players.
<<
What you want is
If dr.Length >= 1 Then
'code to do what you want
End If
Put it immediately before (or instead of)
Matched += dr.Length
The specific match that you are dealing with will be indicated by the values of i and j
Roger
But I need it to look at the last record that was just added
<<
Just change the 0 in this line
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
to the index of the row most recently added. You will find that by
Dim lastRow As Integer = DsLotteryMod1.tbl_WinningN
A new row is always added to the end of a datatable, but as the index is zero based, its index will be one less than the count.
>>
i would like to do some other things after it matches one record from tbl_Players.
<<
What you want is
If dr.Length >= 1 Then
'code to do what you want
End If
Put it immediately before (or instead of)
Matched += dr.Length
The specific match that you are dealing with will be indicated by the values of i and j
Roger
ASKER
"Dim lastRow As Integer = DsLotteryMod1.tbl_WinningN
Having some problems with the second part. This select brings in more then 1 record.
For i As Integer = 0 To 4
Dim lastRow As Integer = DsLotteryMod1.tbl_WinningN
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
For j As Integer = 2 To 6
Dim thisColumn As String = DsLotteryMod1.tbl_PlayersN
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
Next j
Next i
When the match takes place, it does not match by datarow but by column. So I can't figure out how to isolate 1 record at a time. The if dr.Length statement needs to be for each record, not the array of the records. After that I am finally set. Thanks for all the help.
Having some problems with the second part. This select brings in more then 1 record.
For i As Integer = 0 To 4
Dim lastRow As Integer = DsLotteryMod1.tbl_WinningN
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningN
For j As Integer = 2 To 6
Dim thisColumn As String = DsLotteryMod1.tbl_PlayersN
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
Next j
Next i
When the match takes place, it does not match by datarow but by column. So I can't figure out how to isolate 1 record at a time. The if dr.Length statement needs to be for each record, not the array of the records. After that I am finally set. Thanks for all the help.
I'm not sure I quite follow. The dr.Length statement cannot "be for each record". .Length is a property of an ARRAY (in this case of records or, more strictly, datarows). A single record (or datarow) cannot have a .Length.
If what you mean is that you want to do something with each datarow (if there are any) in the dr() array then you could use something on these lines
For i As Integer = 0 to dr.Length -1
'do something with dr(i)
Next
In that code, if there weren't any datarows in the dr() array, nothing would happen but, if there were one or more datatorws in it, dr(i) would represent just one of those on each pass through the loop. You could substitute that code for
Matched += dr.Length
or put it just before or just after it, depnding on whether you also want to display the total number of matches.
Roger
If what you mean is that you want to do something with each datarow (if there are any) in the dr() array then you could use something on these lines
For i As Integer = 0 to dr.Length -1
'do something with dr(i)
Next
In that code, if there weren't any datarows in the dr() array, nothing would happen but, if there were one or more datatorws in it, dr(i) would represent just one of those on each pass through the loop. You could substitute that code for
Matched += dr.Length
or put it just before or just after it, depnding on whether you also want to display the total number of matches.
Roger
ASKER
Lets say I start out with 6 records in tbl_PlayersNumbers when the match begins.
I was shooting for this.
Check record 1.
Check record 2.
Check record 3.
etc.....
After each record is complete, I would look at the variable Matched and decide if i wanted
to do something with that record.
So if Matched is >= 1 for record 1, then do something. The problem I think I am having is:
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
The first line creates an array that matches all the numbers, the second line adds up the matches. So I
am not sure how to apply this code:
For i As Integer = 0 to dr.Length -1
'do something with dr(i)
Next
"The dr.Length statement cannot "be for each record". .Length is a property of an ARRAY (in this case of records or, more strictly, datarows). A single record (or datarow) cannot have a .Length."
This makes sense, but how would I be able to identify the match per record(datarow) while using dr.Length. Sorry for the not being more clear.
I was shooting for this.
Check record 1.
Check record 2.
Check record 3.
etc.....
After each record is complete, I would look at the variable Matched and decide if i wanted
to do something with that record.
So if Matched is >= 1 for record 1, then do something. The problem I think I am having is:
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersN
Matched += dr.Length
The first line creates an array that matches all the numbers, the second line adds up the matches. So I
am not sure how to apply this code:
For i As Integer = 0 to dr.Length -1
'do something with dr(i)
Next
"The dr.Length statement cannot "be for each record". .Length is a property of an ARRAY (in this case of records or, more strictly, datarows). A single record (or datarow) cannot have a .Length."
This makes sense, but how would I be able to identify the match per record(datarow) while using dr.Length. Sorry for the not being more clear.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good to go, thanks for the help
try:
select wn.WinningDrawing, sum(mysum) from tbl_WinningNumbers wn, (
select count(*) as mysum from tbl_WinningNumbers w1, tbl_PlayersNumbers where w1.Winning1 = Players1 and w1.WinningDrawing = wn.WinningDrawing
union all
select count(*) as mysum from tbl_WinningNumbers w2, tbl_PlayersNumbers where w2.Winning2 = Players1 and w2.WinningDrawing = wn.WinningDrawing
union all
... add the rest ..
) as [mytable]
Cheers,
Hillwaaa