working with Access in VB.Net

28 Dec 2006 8:03 PM

Trevor

I have created a small DB in Access, I then created the datasource and
got the data through to the app in detail and it shows me 6 columns and
each of the 6 columns holds a number.
I then have a further 6 text boxes, I want to enter a number in each of
the textboxes and search against all of the entries that I have in the
DB and if there are 4 or more matches I need to to display that row.
The number in textbox1 can be any of the columns in the table

So far I have created a variable for each of the textboxes when I click
the search button so it will take the number in there and I can use it
to compare against the columns in the table but I am having no luck
because I do not know the syntax to get this done.

All I want to do is create a small database that hols 6 numbers per a
line and if I give the app any six numbers then I need it to search
through the DB and give me back all the strings that had 3 or more
matches.

Please, Please can someone help me.

28 Dec 2006 10:56 PM

rdrunner

Hello...

It sounds like you want to implement some "Lotto" logic. I think the layout
of the data you described is a bit tough to querry. Since you want to match
any of the 6 input values against every column and only return the result if
4 or more columns match. The only querry i am comming up with against your
structure would be kind of ugly... (Ok... VERY Ugly)
eg:
Select max(ID) ID, Field1,Field2,...,Field6 from (
Select * from table where Field1 in (1,2,3,4,5,6)
union
Select * from table where Field2 in (1,2,3,4,5,6)
union
Select * from table where Field3 in (1,2,3,4,5,6)
.....
) Group by Field1,Field2,...,Field6 having count>=4

Now I would think about 2 possible solutuions... One would involve
determining which rows to show in your .Net Code (But only since you said
"small DB"... How many records are we talking here?

How about splitting each "row" into N seperate rows which belong to one
"Set" of numbers

Basically

TableSet
---------
ID
Name

TableTip
ID
Set
Tip

and then do a query along the lines of

Select Set,count(*) from tableTip
where Tip in (1,2,3,4,5,6)
group by Set
having count(*) >= 4

Well

Hope that helps

Show quoteHide quote

"Trevor" <trevor.niem***@za.syspro.com> wrote in message
news:1167336207.304252.92830@h40g2000cwb.googlegroups.com...
>I have created a small DB in Access, I then created the datasource and
> got the data through to the app in detail and it shows me 6 columns and
> each of the 6 columns holds a number.
> I then have a further 6 text boxes, I want to enter a number in each of
> the textboxes and search against all of the entries that I have in the
> DB and if there are 4 or more matches I need to to display that row.
> The number in textbox1 can be any of the columns in the table
>
> So far I have created a variable for each of the textboxes when I click
> the search button so it will take the number in there and I can use it
> to compare against the columns in the table but I am having no luck
> because I do not know the syntax to get this done.
>
> All I want to do is create a small database that hols 6 numbers per a
> line and if I give the app any six numbers then I need it to search
> through the DB and give me back all the strings that had 3 or more
> matches.
>
> Please, Please can someone help me.
>

29 Dec 2006 4:22 AM

RobinS

I think the question, "What is the data?" needs to be asked here.

So, Trevor, what is the data? Maybe there's a different way to
structure it that will allow you to get the results you need.

Robin S.
------------------------------------------
Show quoteHide quote

"rdrunner" <N***@your.com> wrote in message
news:uN15EOtKHHA.1240@TK2MSFTNGP03.phx.gbl...
> Hello...
>
> It sounds like you want to implement some "Lotto" logic. I think the
> layout of the data you described is a bit tough to querry. Since you
> want to match any of the 6 input values against every column and only
> return the result if 4 or more columns match. The only querry i am
> comming up with against your structure would be kind of ugly... (Ok...
> VERY Ugly)
> eg:
> Select max(ID) ID, Field1,Field2,...,Field6 from (
> Select * from table where Field1 in (1,2,3,4,5,6)
> union
> Select * from table where Field2 in (1,2,3,4,5,6)
> union
> Select * from table where Field3 in (1,2,3,4,5,6)
> ....
> ) Group by Field1,Field2,...,Field6 having count>=4
>
> Now I would think about 2 possible solutuions... One would involve
> determining which rows to show in your .Net Code (But only since you
> said "small DB"... How many records are we talking here?
>
> How about splitting each "row" into N seperate rows which belong to
> one "Set" of numbers
>
> Basically
>
> TableSet
> ---------
> ID
> Name
>
> TableTip
> ID
> Set
> Tip
>
> and then do a query along the lines of
>
> Select Set,count(*) from tableTip
> where Tip in (1,2,3,4,5,6)
> group by Set
> having count(*) >= 4
>
> Well
>
> Hope that helps
>
> "Trevor" <trevor.niem***@za.syspro.com> wrote in message
> news:1167336207.304252.92830@h40g2000cwb.googlegroups.com...
>>I have created a small DB in Access, I then created the datasource and
>> got the data through to the app in detail and it shows me 6 columns
>> and
>> each of the 6 columns holds a number.
>> I then have a further 6 text boxes, I want to enter a number in each
>> of
>> the textboxes and search against all of the entries that I have in
>> the
>> DB and if there are 4 or more matches I need to to display that row.
>> The number in textbox1 can be any of the columns in the table
>>
>> So far I have created a variable for each of the textboxes when I
>> click
>> the search button so it will take the number in there and I can use
>> it
>> to compare against the columns in the table but I am having no luck
>> because I do not know the syntax to get this done.
>>
>> All I want to do is create a small database that hols 6 numbers per a
>> line and if I give the app any six numbers then I need it to search
>> through the DB and give me back all the strings that had 3 or more
>> matches.
>>
>> Please, Please can someone help me.
>>
>

29 Dec 2006 5:10 AM

Kerry Moorman

Trevor,

Six columns of numbers sounds suspiciously like a repeating group. Please
tell us that is not the case.

Kerry Moorman

Show quoteHide quote

"Trevor" wrote:

> I have created a small DB in Access, I then created the datasource and
> got the data through to the app in detail and it shows me 6 columns and
> each of the 6 columns holds a number.
> I then have a further 6 text boxes, I want to enter a number in each of
> the textboxes and search against all of the entries that I have in the
> DB and if there are 4 or more matches I need to to display that row.
> The number in textbox1 can be any of the columns in the table
>
> So far I have created a variable for each of the textboxes when I click
> the search button so it will take the number in there and I can use it
> to compare against the columns in the table but I am having no luck
> because I do not know the syntax to get this done.
>
> All I want to do is create a small database that hols 6 numbers per a
> line and if I give the app any six numbers then I need it to search
> through the DB and give me back all the strings that had 3 or more
> matches.
>
> Please, Please can someone help me.
>
>

29 Dec 2006 8:06 AM

Trevor

Yes it is something to do with lotto but I don't want to get random
numbers or anything.

I want to input a set of 6 numbers that I have selected then take that
set and hold it in a DB....I was also looking at maybe holding it in an
xml file if possible.

I then want to be able to say that any six number did come up and I
want to search my DB to see how many times I got more than 3 numbers.
It sounds simple but has proved to be a bit irritating.
I have a collection of about 20 000 sets of six number sets and that
should not be a big DB.

Thanks for the suggestions so far though.

29 Dec 2006 10:07 AM

Stephany Young

Given a table (main) with a unique column (id) and 6 value columns (val1
thru val6) where each column is Int32 nd each row represents a single set of
numbers, the following query will return those rows where the criteria of 4
or more from 6 is satisfied:

Dim _con As New OleDbConnection(connectionstring)

Dim _com As new OleDbCommand("select * from " _
    "main where id in (select id from (select id," _
    "count(*) from (select id,val1 as val from main " _
    "union all select id,val2 from main union all " _
    "select id,val3 from main union all select id," _
    "val4 from main union all select id,val5 from " _
    "main union all select id,val6 from main) a " _
    "where val=? or val=? or val=? or val=? or " _
    "val=? or val=? group by id having count(*)>3) b)", _con)

_com.Parameters.Add("p1", OleDbType.Integer).Value = TextBox1.Text
_com.Parameters.Add("p2", OleDbType.Integer).Value = TextBox2.Text
_com.Parameters.Add("p3", OleDbType.Integer).Value = TextBox3.Text
_com.Parameters.Add("p4", OleDbType.Integer).Value = TextBox4.Text
_com.Parameters.Add("p5", OleDbType.Integer).Value = TextBox5.Text
_com.Parameters.Add("p6", OleDbType.Integer).Value = TextBox6.Text

_con.Open()

Dim _drdr As OleDbDataReader = _com.ExecuteReader()

While _drdr.Read
    ' Do something with the row
End While

_drdr.Close()

_con.Close()

Show quoteHide quote

"Trevor" <trevor.niem***@za.syspro.com> wrote in message
news:1167379606.854371.84440@i12g2000cwa.googlegroups.com...
> Yes it is something to do with lotto but I don't want to get random
> numbers or anything.
>
> I want to input a set of 6 numbers that I have selected then take that
> set and hold it in a DB....I was also looking at maybe holding it in an
> xml file if possible.
>
> I then want to be able to say that any six number did come up and I
> want to search my DB to see how many times I got more than 3 numbers.
> It sounds simple but has proved to be a bit irritating.
> I have a collection of about 20 000 sets of six number sets and that
> should not be a big DB.
>
>
> Thanks for the suggestions so far though.
>

29 Dec 2006 6:27 PM

RobinS

Show quote Hide quote

"Trevor" <trevor.niem***@za.syspro.com> wrote in message
news:1167379606.854371.84440@i12g2000cwa.googlegroups.com...
> Yes it is something to do with lotto but I don't want to get random
> numbers or anything.
>
> I want to input a set of 6 numbers that I have selected then take that
> set and hold it in a DB....I was also looking at maybe holding it in
> an
> xml file if possible.
>
> I then want to be able to say that any six number did come up and I
> want to search my DB to see how many times I got more than 3 numbers.
> It sounds simple but has proved to be a bit irritating.
> I have a collection of about 20 000 sets of six number sets and that
> should not be a big DB.
>
>
> Thanks for the suggestions so far though.
>

I'm probably going to be lambasted for this, but here's another idea.
What if you stored the data like this:

ID, Value, Seq
1        10        1
1        11        2
1        24        3
1        46        4
1        32        5
1        44        6
2        9         1
2        11        2
2        12        3
2        13        4
2        55        5
2        43        6
(and so on)

So you have them put numbers into 6 textboxes, and you
want to know which ID#'s have more than 4 matches, right?

You could query it like this:

select ID, count(ID) from BigTable where value IN (textbox1.text,
textbox2.text, textbox3.text, textbox4.text, textbox5.text,
textbox6.text) GROUP BY ID WHERE count(ID) >= 4

This will give you a list of ID's with 4 or more matches.
YOu could use that list of ID's to query back and get the
data for display.

If it wasn't millions of records, I'd be tempted to keep the
data in both formats -- one for searching, and one for display.

Then you could do the second query like this:

select * from OriginalTable WHERE ID IN (1,2,3)
(where 1, 2, 3) are the id's returned from the first query
and display the rows easily that way.

If it was SQLServer, you could do this:
select * from OriginalTable WHERE ID IN (
select ID, count(ID) from BigTable where value IN (textbox1.text,
    textbox2.text, textbox3.text, textbox4.text, textbox5.text,
    textbox6.text) GROUP BY ID WHERE count(ID) >= 4)

You can try that with Access, but I think it converts it to
a join "underneath" rather than processing it as a subquery,
and won't let you do a join with a query containing aggregate
functions.

Just another idea.
Robin S.

29 Dec 2006 10:58 PM

Trevor

Thank you very much for all the suggestions I will now attempt to put
this together and see if I get it working.