Solved

Compare multiple values

Posted on 2006-11-21
162 Views
I have two datatables:
tbl_PlayersNumbers (multiple records)
Columns - UniqueKey(PK)(Autonumber), Players1(int), Players2(int), Players3(int)

tbl_WinningNumbers (one record)
Columns - WinningDrawing(PK)(date/time), Winning1(int), Winning2(int), Winning3(int)

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.

0
Question by:rogue_1
• 5
• 5
• 2

LVL 16

Expert Comment

Hi rogue_1,

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
) as [mytable]

Cheers,
Hillwaaa
0

LVL 34

Expert Comment

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).Item(i)
For j As Integer = 1 To 3
Dim thisColumn As String = tbl_PlayersNumbers.Columns(j).ColumnName
Dim dr() As DataRow = tbl_PlayersNumbers.Select(thisColumn & " = " & thisWinner)
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
0

Author Comment

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_WinningNumbers.Rows(0).Item(i)
For j As Integer = 1 To 3
Dim thisColumn As String = DsLotteryMod1.tbl_PlayersNumbers.Columns(j).ColumnName
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersNumbers.Select(thisColumn & " = " & thisWinner)
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_PlayersNumbers.Select(thisColumn & " = " & thisWinner)

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
0

LVL 16

Assisted Solution

Hillwaaa earned 100 total points
rogue_1

I may be way off track, but can you try:

Dim dr() As DataRow = tbl_PlayersNumbers.Select(thisColumn & " = " & CStr(thisWinner))

and if that doesn't work:

Dim dr() As DataRow = tbl_PlayersNumbers.Select(thisColumn & " = '" & thisWinner & "'")

0

LVL 34

Expert Comment

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_PlayersNumbers.Select(thisColumn & " = " & thisWinner.ToString)

Roger
0

Author Comment

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_WinningNumbers.Rows(0).Item(i)
For j As Integer = 2 To 6
Dim thisColumn As String = DsLotteryMod1.tbl_PlayersNumbers.Columns(j).ColumnName
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersNumbers.Select(thisColumn & " = '" & thisWinner & "'")
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_WinningNumbers.Rows(0).Item(i)

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_PlayersNumbers.Select(thisColumn & " = '" & thisWinner & "'")
Matched += dr.Length

Thanks for all the help.  This has really been a huge help to a begginer.
0

LVL 34

Expert Comment

>>
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_WinningNumbers.Rows(0).Item(i)

to the index of the row most recently added.  You will find that by

Dim lastRow As Integer = DsLotteryMod1.tbl_WinningNumbers.Rows.Count - 1

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
0

Author Comment

"Dim lastRow As Integer = DsLotteryMod1.tbl_WinningNumbers.Rows.Count - 1"  Worked like a champ

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_WinningNumbers.Rows.Count - 1
Dim thisWinner As Integer = DsLotteryMod1.tbl_WinningNumbers.Rows(lastRow).Item(i)
For j As Integer = 2 To 6
Dim thisColumn As String = DsLotteryMod1.tbl_PlayersNumbers.Columns(j).ColumnName
Dim dr() As DataRow = DsLotteryMod1.tbl_PlayersNumbers.Select(thisColumn & " = '" & thisWinner & "'")
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.
0

LVL 34

Expert Comment

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
0

Author Comment

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_PlayersNumbers.Select(thisColumn & " = '" & thisWinner & "'")
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.
0

LVL 34

Accepted Solution

Sancler earned 400 total points
If we go back to your first post you said

>>
Each time that a combination matches, I need to add 1 to a varible called "Matched".
<<

I do not know if this could happen in practice but in theory if, for any one the Winning Number values, there are two combinations that match in the same column in the Players table then that is two matches to be added to the variable called "Matched".  So, my original code - rather than cycling individually through every row for every column in the Players table - simply said "How many rows have a match for this column"?, and added the count of that number of rows to the "Matched" variable.

You then added the requirement that you wanted to do something so far as the individual records were concerned.  Luckily, my original code allowed that to happen.  Although originally it did nothing more than count the number of rows returned by

Dim dr() As DataRow = tbl_PlayersNumbers.Select(thisColumn & " = " & thisWinner)

all the rows containing the necessary match were there in the array dr().  As I said when you introduced that requirement "The specific match that you are dealing with will be indicated by the values of i and j".  Translating that into the form of the code line just quoted what it means is that each of the datarows in dr() at any one time is a row in which the one Player that is represented by thisColumn has a match with the value that is represented by thisWinner.  It is a distinct Player and a distinct winning value.  It may be that there could never be more than one match with a distinct Player and a distinct Winning value: I don't know that.  But if there can be, and you need to know that there are, more than one such matches, each of the multiple rows is there for you to deal with if you need to.  That's what you would use

For i As Integer = 0 to dr.Length -1
'do something with dr(i)
Next

for.

What more do you need to know?

Roger
0

Author Comment

Good to go, thanks for the help
0

Featured Post

XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!