Solved

Compare multiple values

Posted on 2006-11-21
12
163 Views
Last Modified: 2010-04-23
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
Comment
Question by:rogue_1
  • 5
  • 5
  • 2
12 Comments
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17992197
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
    ... add the rest ..
) as [mytable]

Cheers,
Hillwaaa
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17994602
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

by:rogue_1
ID: 18000825
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

by:Hillwaaa
Hillwaaa earned 100 total points
ID: 18000874
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

by:Sancler
ID: 18001812
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

by:rogue_1
ID: 18050864
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 34

Expert Comment

by:Sancler
ID: 18057292
>>
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

by:rogue_1
ID: 18108359
"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

by:Sancler
ID: 18110748
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

by:rogue_1
ID: 18112120
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

by:
Sancler earned 400 total points
ID: 18112244
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

by:rogue_1
ID: 18235435
Good to go, thanks for the help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now