VB Database basic questions Options

[zan777]

I have very limited programming knowledge and have tried to start a very basic project but have quickly become stuck. The concept of what I am trying to create is a database with a series of columns where the user selects the criteria and it will narrow down the search to the point were the user can choose from one of the selections.

In my example to understand how to achieve what i want i have done the following:

Create a database with a table inside
the table has the columns.. team name | red | blue |green| yellow | black
team name is set as varchar(50) with the rest being bits
data that would go in would be "roosters" with red + blue checked or eels (blue + yellow check) (for those who follow football).

Next I used the add query in the table adapter and made 2 queries "where (blue = 1) and were (red = 1)
this works to select all the records that have a red box checked or a blue box check but it won't work for red and blue. Ideally I would like to have checkboxes for each colour so the user can select the combination of colours it returns matches for. Also I am unsure how to "reset" the queries so that all the records will be displayed again.

If anyone would be kind enough to look at my example project you can grab it here:

https://www.dropbox.com/s/e2eup0r4m19v5pb/W...pplication1.zip

I know this seems really basic, but hopefully someone can help

thanks

[kikz]

CODE

WHERE blue=1 OR red=1

will select all teams that have either red or blue or both selected.

CODE

WHERE blue = 1 AND RED = 1

will select only teams with both selected.


You haven't actually asked a question, so I'm not sure how to reply ( i know this because a ctrl+f with "?" turned up nothing :p)

Frankly, the interface you've got is going to make building the query difficult. You're using checkboxes so you've got either true or false, but how do you indicate you don't want to search for a particular colour with either true or false.

eg. Lets say you've got five checkboxes ( which you do ). You could use those to build up a query like this.

CODE

Dim query = "SELECT * FROM Table1 WHERE " & _
"BLUE = " & IIF(BlueCheckBox.Checked,"1","0") & "," & _
"Red = " & IIF(RedCheckBox.Checked,"1","0") & "," & _
"Green = " & IIF(GreenCheckBox.Checked,"1","0") & "," & _
"Yellow = " & IIF(YellowCheckBox.Checked,"1","0") & "," & _
"Black = " & IIF(BlackCheckBox.Checked,"1","0") & "," & _

That would nearly build you up the required SQL query. You'd then pass that into the CommandText of your select command.


PS grab some linq to sql at least.

This post has been edited by kikz: Jul 24 2012, 08:12 PM

[Periander]

Sports calendar?

[zan777]

CODE

WHERE blue=1 OR red=1

will select all teams that have either red or blue or both selected.

CODE

WHERE blue = 1 AND RED = 1

will select only teams with both selected.


You haven't actually asked a question, so I'm not sure how to reply ( i know this because a ctrl+f with "?" turned up nothing :p)

Frankly, the interface you've got is going to make building the query difficult. You're using checkboxes so you've got either true or false, but how do you indicate you don't want to search for a particular colour with either true or false.

eg. Lets say you've got five checkboxes ( which you do ). You could use those to build up a query like this.

CODE

Dim query = "SELECT * FROM Table1 WHERE " & _
"BLUE = " & IIF(BlueCheckBox.Checked,"1","0") & "," & _
"Red = " & IIF(RedCheckBox.Checked,"1","0") & "," & _
"Green = " & IIF(GreenCheckBox.Checked,"1","0") & "," & _
"Yellow = " & IIF(YellowCheckBox.Checked,"1","0") & "," & _
"Black = " & IIF(BlackCheckBox.Checked,"1","0") & "," & _

That would nearly build you up the required SQL query. You'd then pass that into the CommandText of your select command.


PS grab some linq to sql at least.

Thanks or your reply! yes I can kind of see now how i didn't specifically ask a question. My implied questions were in my goals here:

Ideally I would like to have checkboxes for each colour so the user can select the combination of colours it returns matches for. Also I am unsure how to "reset" the queries so that all the records will be displayed again.

Anyway I think I have found a way to solve my problem but I have a a bit of a roadblock you or someone else may be able to help with. I have another "learning project" with a table that has 4 colums "name", "c1","c2"c3", name as string, c1,c2,c3 as bit.

I have used the following line of code to filter c2 column based on it being selected or not:

CODE

Tbl1DataGridView.DataSource = Me.DbaseDataSet.tbl1.Select("C2=1")

The problem is i get a DBnull error for column C3 which i am presuming is because C3 is unchecked and has a 0 value? Is there anyway I can fix this as I am pretty certain I would be able to solve the whole program on the basis of fixing this issue.

Thanks

This post has been edited by zan777: Jul 25 2012, 01:14 PM