Filtering data in grid view.

[lkallas]

I have a database "holiday.mdb" and there is a table "Properties" that contains columns "County" , "Bedrooms".
The following code shows how to filter data by one criteria( by the county OR by the number of bedrooms). But how can I filter data so that it considers both (county and number of bedrooms)? For example: selected item is "Cardiff" and number of bedrooms is "4" . Now it should filter the data in the table so that it show only places in Cardiff with 4 bedrooms.

How should I edit SQLString?
Any good tutorials for SQL statements in vb?


Imports System.Data.OleDb
Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
OleDbDataAdapter1.Fill(DataSet11)

End Sub

Private Sub lstCounties_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCounties.SelectedIndexChanged
'Above line uses a new data object for every individual query
Dim County, SQLString As String
Dim dtProperties As New DataTable() 'Data table will fill the data grid
Dim dbDataAdapter As OleDbDataAdapter
Dim ConnectString As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = Holidays.mdb"
County = lstCounties.Text
SQLString = "SELECT * FROM Properties WHERE County = " & "'" & County & "'" & ""
'Above, imagine Cardiff has been selected, the SQL would search for it as:
' " SELECT * FROM Properties Where County = 'Cardiff' "
dbDataAdapter = New OleDbDataAdapter(SQLString, ConnectString)
dbDataAdapter.Fill(dtProperties)
grdProperties.DataSource = dtProperties
End Sub

Private Sub lstBedrooms_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstBedrooms.SelectedIndexChanged
'Above line uses a new data object for every individual query
Dim Bedrooms, SQLString As String
Dim dtProperties As New DataTable() 'Data table will fill the data grid
Dim dbDataAdapter As OleDbDataAdapter
Dim ConnectString As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = Holidays.mdb"
Bedrooms = lstBedrooms.Text
SQLString = "SELECT * FROM Properties WHERE Bedrooms = " & Bedrooms & ""
dbDataAdapter = New OleDbDataAdapter(SQLString, ConnectString)
dbDataAdapter.Fill(dtProperties)
grdProperties.DataSource = dtProperties

End Sub

End Class

[jmcilhinney]

You just answered your own question.

But how can I filter data so that it considers both (county and number of bedrooms)?

It's simple Boolean logic. You join Boolean multiple expressions with AND and OR operators to create more complex expressions.

You don't need a tutorial on SQL statements in VB because SQL statements have nothing to do with VB. SQL is SQL. You just need a SQL tutorial and I doubt that a you'd have trouble finding one with a web search.

Finally, are getting paid by the ampersand? What's the point of putting ampersands between two literal strings? All you do is make your code harder to read. Would you do this:

Dim s As String = "Hello" & " " & "World"

I certainly hope not. You'd do this:

Dim s As String = "Hello World"

In that case, why do this:

Dim ConnectString As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = Holidays.mdb"

when you can do this:

Dim ConnectString As String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = Holidays.mdb"

and why do this:

SQLString = "SELECT * FROM Properties WHERE County = " & "'" & County & "'" & ""

when you can do this:

SQLString = "SELECT * FROM Properties WHERE County = '" & County & "'"

That last bit is the silliest of all because concatenating with an empty string is like adding zero to a number, i.e. pointless.

[lkallas]

Thank you! Good advice!

How should I write the syntax when I have 3 criterias. So it would work if all 3 fields are filled and also when some of them are leaved blank.

County = lstCounties.Text
Bedrooms = lstCounties.Text
NonSmoking = txtboxSmoking.Text

Came up with this syntax:
SQLString = "SELECT * FROM Properties WHERE County = '" & County & "'" AND WHERE Bedrooms= '" & Bedrooms & "'" AND WHERE NonSmoking= '" & NonSmoking & "'"

[IanRyder]

Hi,

Take some time and have a read through the Basic concepts of SQL syntax from this tutorial:-

http://www.w3schools.com/sql/default.asp

Hope that helps.

Cheers,

Ian

[jmcilhinney]

Thank you! Good advice!

How should I write the syntax when I have 3 criterias. So it would work if all 3 fields are filled and also when some of them are leaved blank.

County = lstCounties.Text
Bedrooms = lstCounties.Text
NonSmoking = txtboxSmoking.Text

Came up with this syntax:
SQLString = "SELECT * FROM Properties WHERE County = '" & County & "'" AND WHERE Bedrooms= '" & Bedrooms & "'" AND WHERE NonSmoking= '" & NonSmoking & "'"

So you're saying that you want the parameters to be optional?

[lkallas]

Thank you Ian, helped me alot understanding SQL basics!

Yes, I meant optional parameters. So it will filter the data even if some of the so called search fields are left empty. And even then if all the fields are filled.

I guess it is quite difficult to achieve....

[jmcilhinney]

Thank you Ian, helped me alot understanding SQL basics!

Yes, I meant optional parameters. So it will filter the data even if some of the so called search fields are left empty. And even then if all the fields are filled.

I guess it is quite difficult to achieve....

No, not especially difficult. Firstly though, follow the Blog link in my signature and check out my post on Parameters In ADO.NET to learn why and how to use parameters in preference to string concatenation. As for how to implement optional parameters, you just need to provide two criteria per filter. Because Jet OLE DB doesn't properly support named parameters, you must use two separate parameters per filter, e.g.

myCommand.CommandText = "SELECT * FROM MyTable WHERE (@FirstColumn1 IS NULL OR FirstColumn = @FirstColumn2) AND (@SecondColumn1 IS NULL OR SecondColumn = @SecondColumn2)"
Dim firstColumnValue = If(firstColumn Is Nothing, CObj(DBNull.Value), firstColumn)
Dim secondColumnValue = If(secondColumn Is Nothing, CObj(DBNull.Value), secondColumn)


With myCommand.Parameters
    .Add("@FirstColumn1", OleDbType.VarChar, 50).Value = firstColumnValue
    .Add("@FirstColumn2", OleDbType.VarChar, 50).Value = firstColumnValue
    .Add("@SecondColumn1", OleDbType.VarChar, 50).Value = secondColumnValue
    .Add("@SecondColumn2", OleDbType.VarChar, 50).Value = secondColumnValue
End With

[lkallas]

Thanks!
Way too advanced for me to understand using parameters.


But what if I use SQL LIKE operator?

For example:

County = lstCounties.Text
Bedrooms = lstCounties.Text
NonSmoking = txtboxSmoking.Text'%tav%'

SQLString = "SELECT * FROM Properties WHERE County LIKE = '" % County % "'" AND WHERE Bedrooms LIKE = '" % Bedrooms % "'" AND WHERE NonSmoking LIKE= '" % NonSmoking % "'"



And how can I add code to this forum so it displays like in your comment?

[jmcilhinney]

You could do that and it would work for text fields, so that may be OK in your case, but what I've shown will work regardless of data type.

If you want to see the contents of my post then Quote it.

[lkallas]

Solved my previous question like so:

Dim County, SQLString As String
Dim Bedrooms As String
Dim NonSmoking as string
Dim dtProperties As New DataTable()
Dim dbDataAdapter As OleDbDataAdapter
Dim ConnectString As String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = Holidays.mdb"

County = lstCounties.Text
Bedrooms = lstCounties.Text
NonSmoking = txtboxSmoking.Text

SQLString = "SELECT * FROM Properties WHERE County Like '%" & County & "%' and Bedrooms LIKE '%" & Bedrooms & "%' and NonSmoking LIKE '%" & NonSmoking & "%'"

dbDataAdapter = New OleDbDataAdapter(SQLString, ConnectString)
dbDataAdapter.Fill(dtProperties)
grdProperties.DataSource = dtProperties




How can I use SQL Between command in vb? I try to make a query that can filter places that have for example 2-4 bedrooms.

Amount1 = txtboxBedroomamount1.Text()
Amount2 = txtboxBedroomamount2.Text()

SQLString = "SELECT * FROM Properties WHERE Bedrooms between = '" & Amount1 & "' AND '" & Amount2 & "'"


This query doesn't seem to work....

[dunfiddlin]

Er , no it wouldn't. How would you normally get a value between two limits?

..... WHERE Bedrooms >= 2 AND Bedrooms <= 4

BETWEEN acts on location, not on value.

[lkallas]

Thanks dunfiddlin!

It works now! But when I leave the fields blank and execute the code it will give me an error.
Also when I try to use numbers like 3,5 and 4.5 It gives me an error.....

What am I doing wrong?

[dunfiddlin]

Blank fields will give you an error because they have no value. You should ensure that zero values are 0 and not blank (null).

I'm not really sure what you mean in the second situation. What does 'use numbers like' entail?

[lkallas]

I mean that if I am using textboxes in my VB program (one is Amount1 and the other Amount2) and I am trying to make a query that will filter all numbers between Amount1 and Amount2.

WHERE Bedrooms >= 2 AND Bedrooms <= 4

This line did the trick! But what if I want to filter numbers that are with decimal places(example: 4,5). Then it gives an error.
When I execute the code with one or both textboxes left empty it also gives an error. Can't figure out why and what should I change...

[wiss.dev]

use . instead of , in the decimal number ( e.g. 4.5)

[jmcilhinney]

When I execute the code with one or both textboxes left empty it also gives an error. Can't figure out why and what should I change...

I already told you how to handle that back in post #7!