?
Solved

Adding a new filter to existing filters DataGridView

Posted on 2012-08-13
15
Medium Priority
?
996 Views
Last Modified: 2012-08-16
Hi Experts

Hope all is well...

OK I have to add quite a few Of these filters to this DataGridView, so hopefully once I know how to do this one I should be able to finish up this specific form in my app.

I have added a Check List Box to the form "ClbShaftSelect, below is the existing code thanks to you guys. How do I get this to produce data from date1 too date2 from shaft?

Private Function FilterSQL(ByVal connString As String, _
                               ByVal tableName As String, _
                               ByVal fieldName As String, _
                               ByVal dateFrom As Date, _
                               ByVal dateTo As Date) As DataTable
        '<Validate dates here>
        Dim dt As New DataTable
        Dim strSQL As String = String.Format("SELECT * FROM {0} WHERE {1} BETWEEN @DateFrom AND @DateTo", tableName, fieldName)
        Using conn As New SqlClient.SqlConnection(connString)
            conn.Open()
            Using da As New SqlClient.SqlDataAdapter(strSQL, conn)
                da.SelectCommand.Parameters.AddWithValue("@DateFrom", dateFrom)
                da.SelectCommand.Parameters.AddWithValue("@DateTo", dateTo)
                da.Fill(dt)
            End Using
            conn.Close()
        End Using
        Return dt
    End Function

Open in new window


Private Sub BtnView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnView.Click
 DataGridViewPerformance.DataSource = FilterSQL("Data Source=196.220.43.247,1444;Initial Catalog=psql;User ID=MyName;Password=Mypass;", _
                    "Performance", "Date", DtpStart.Value.Date, DtpEnd.Value.Date.AddHours(24).AddMinutes(-1))
End Sub

Open in new window


Pref with the option of selecting more than one.
0
Comment
Question by:GrahamSA
  • 8
  • 7
15 Comments
 
LVL 28

Expert Comment

by:Ark
ID: 38290965
Can you elaborate your task? You mean you need filter by multiple date columns within 2 dates selected by DateTimePickers?
        Dim strDateFilter = "BETWEEN @DateFrom AND @DateTo"
        Dim strFilter As String = "SELECT * FROM YourTableName"
        Dim lstFilters As New List(Of String)
        For Each s As String In ClbShaftSelect.CheckedItems
            lstFilters.Add(String.Format("({0} {1})", s, strDateFilter))
        Next
        If lstFilters.Count > 0 Then
            strFilter += " WHERE " & String.Join(" AND ", lstFilters.ToArray)
        End If

Open in new window

0
 

Author Comment

by:GrahamSA
ID: 38291055
Hi Ark

The date pickers are working perfectly, so as it stands now I can filter my grid view between the 2 dates. now I want to add a new filter to produce results according to the dates and the "shaft" using the check list box  ( ClbShaftSelect) .

I need to know how this will effect the existing code
0
 
LVL 28

Expert Comment

by:Ark
ID: 38291095
If you want add filtering to existing data (already filtered by date), you can filter entire DataGridView:
CType(DataGridView1.DataSource, DataTable).DefaultView.RowFilter = "UserName LIKE 'A%'"
- where Username is a field in DataTable
To remove addidtional filtering:
CType(DataGridView1.DataSource, DataTable).DefaultView.RowFilter = String.Empty
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:GrahamSA
ID: 38291261
Thanks

I will be using that a little later on.
For now I need to add more filters to that data before it gets to the gridview
0
 
LVL 28

Expert Comment

by:Ark
ID: 38291279
You want extend existing filter?
'Initial filter
Dim strSQL As String = String.Format("SELECT * FROM {0} WHERE {1} BETWEEN @DateFrom AND @DateTo", tableName, fieldName)
'Assuming your checklistbox contains strings like:
'Category=3
'Price>500
'Name Like 'A%'
Dim lstFilters As New List(Of String)
For Each s As String In ClbShaftSelect.CheckedItems
     lstFilters.Add(String.Format("({0})", s))
Next
If lstFilters.Count > 0 Then
    strSQL += " AND " & String.Join(" AND ", lstFilters.ToArray)
End If 

Open in new window

0
 

Author Comment

by:GrahamSA
ID: 38292828
Yes extend the existing filter,
on this event BtnView_Click
0
 
LVL 28

Expert Comment

by:Ark
ID: 38294173
strSQL (select string with filter(s)) should looks like:
strSQL="SELECT * FROM <TableName> WHERE (<Condition1>) AND (<Condition2) AND (<Condition3>) ... AND (<ConditionN>)" '(You can add ORDER BY and/or other SQL clauses as well)
<TableName> is your table name
<ConditionXXX> are SQL expressions. You can use either plain expressions or include variables. Expressions samples:
"[Sales]=5","Price>1000" ' Sales and Price are a numeric fields in your table. Brackets are optional in this case, but if you have field name which conflicts with system names (like Settings, Procedures etc) they are required.
"[Name]='john'", "EMail LIKE '%gmail.com'" - Name is a string (varchar/nvarchar/text etc) field. Strings (john,%gmail.com) must be enclosed in single quotes. String comparison is case Insensitive (JoHn=john)
"[DateField]>#2012-01-31#","[DateField]>'2012-01-31'" - Date values enclosed in #. You can compare date field with a string, but in this case date field will be CAST by SQL server to string first, according to server locale setttings. So it's better to use variables for dates:
"[DateField]>@SomeDate","[DateField] BETWEEN @DateFrom AND @DateTo"
If you'r using variables, you must define them as parameters before SQL call:

da.SelectCommand.Parameters.AddWithValue("@SomeDate", someDateValue)
 
da.SelectCommand.Parameters.AddWithValue("@DateFrom", dateFrom)
da.SelectCommand.Parameters.AddWithValue("@DateTo", dateTo)
0
 

Author Comment

by:GrahamSA
ID: 38296694
Ok how do I declare ClbShaftSelect and the column shafts?
0
 
LVL 28

Expert Comment

by:Ark
ID: 38298918
Can you please send unfiltered datagridview example and colums / criteria you want add to date filtering?
0
 

Author Comment

by:GrahamSA
ID: 38299333
Gee Sometimes I feel like I am getting nowhere, Thanks for putting up with this.

how do I send a datagrigview?
0
 
LVL 28

Expert Comment

by:Ark
ID: 38299604
Just send a screenshot with few rows filled with unfiltered (or filtered by dates only) data with columns names and describe which columns you want filter more
0
 

Author Comment

by:GrahamSA
ID: 38299667
Cool, here you go!

GridView with controls
The Check list box is called ClbShaftSelect and needs to accept more than one selection, Shaft Column

The Combo box is called CbxTeamLeader and will be an extra filter on the Team Leader but only when needed.

I have a lot of there to do so I really need to get these two right so I can teach myself how to use this for the rest of the forms.

There will be one more question regarding the combo once I understand this
0
 
LVL 28

Accepted Solution

by:
Ark earned 2000 total points
ID: 38299737
FilterSQL function body:
'Initial filter
Dim strSQL As String = "SELECT * FROM YorTableName WHERE [Date] BETWEEN @DateFrom AND @DateTo"
Dim lstFilters As New List(Of String)
For Each s As String In ClbShaftSelect.CheckedItems
     lstFilters.Add(String.Format("(Shaft='{0}')", s))
Next
If lstFilters.Count > 0 Then
    strSQL += " AND (" & String.Join(" OR ", lstFilters.ToArray) & ")"
End If  
If  CbxTeamLeader.SelectedIndex>0 Then 'or
'If  CbxTeamLeader.Text<>"" Then
    strSQL += " AND (TeamLeader='" & CbxTeamLeader.Text & "')"
End If

       Using conn As New SqlClient.SqlConnection(connString)
            conn.Open()
            Using da As New SqlClient.SqlDataAdapter(strSQL, conn)
                da.SelectCommand.Parameters.AddWithValue("@DateFrom", dateFrom)
                da.SelectCommand.Parameters.AddWithValue("@DateTo", dateTo)
                da.Fill(dt)
            End Using
            conn.Close()
        End Using

Return dt

Open in new window

0
 

Author Closing Comment

by:GrahamSA
ID: 38299791
Friend, Thank you so much!
This is a perfect solution to the CheckListBox

Opening a new Question to wrap up the combo now
0
 
LVL 28

Expert Comment

by:Ark
ID: 38299878
Thanks for points
Glad I could help
I've already added ComboBox support:
'=============
'Check if smth selected in combo box
'Assuming first entry (SelectedIndex=0) is empty string or
'smth like "Select Team Leader"
If  CbxTeamLeader.SelectedIndex>0 Then 'Smth else selected
'or check combo text directly
'If  CbxTeamLeader.Text<>"" Then ' when first entry is emty
'If  CbxTeamLeader.Text<>"Select Team Leader" Then ' when first entry is "Select Team Leader"
    strSQL += " AND (TeamLeader='" & CbxTeamLeader.Text & "')"
End If
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

809 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