Modifing my VB.NET Code to refine my searchs with check boxs

Question

I Need Help Modifing the code below, so that I can use check boxs to help refine the search, I am already able to refine the search using a textbox, this can be seen in the code shown below, but I want this text box to be used in conjunction with these check boxs show in the pic

This is what my program looks like and i would like to be able to use the shown text boxs to refine my search

    Dim conn As New OleDbConnection                 
    Dim cmd As New OleDbCommand                     
    Dim da As New OleDbDataAdapter
    Dim dt As New DataTable                        
    Dim sSQL As String = String.Empty

    Try
        conn = New OleDbConnection(Get_Constring)  
        conn.Open()                                
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text

        sSQL = "SELECT Books.[Book ID], Books.Title, Books.Author, Books.Category, Books.Location, Books.[Fiction/Non-Fiction], Books.Loaned FROM Books"
        sSQL = sSQL & " Where Books.Title like '%" & Me.search_txt.Text & "%' ORDER BY Books.Title"


        cmd.CommandText = sSQL
        da.SelectCommand = cmd
        da.Fill(dt)

        Me.search_datagrid.DataSource = dt              

        If dt.Rows.Count = 0 Then
            MsgBox("No record found!")                  
        End If

    Catch ex As Exception
        MsgBox(ErrorToString)                           '

    Finally
        conn.Close()                                    

    End Try
End Sub

Any help in this regard would be apperciated.

are your checkboxes grouped according to a field in your table? — Malcolm Salvador, Jul 24 '15 at 02:33
Yes, this can be seen here in my pic http://i.stack.imgur.com/Y1Xwe.png I am trying to get the Fiction/Non-fiction group and the category group to work. I really don't care as much about the types section — Matt07211, Jul 24 '15 at 02:36
Can you post what the value of your query string ends up being before it gets executed by the database? perhaps that will shed some light on the issue. Also, we really would need to know the structure of your table. Is `Books.Category` just a CHAR column? What about the column which indicates fiction/non-fiction? — RianBattle, Jul 24 '15 at 14:57
All columns in my access database are text except for the tickboxs in the loaned column — Matt07211, Jul 25 '15 at 03:38

tastydew · Answer 1 · 2015-07-24T03:08:49.540

Making checks to see if whether a child of a group has been checked to build a query would possibly be your best bet

    sSQL = sSQL & " Where Books.Title like '%" & Me.search_txt.Text "

    if cboFiction.Checked = True Then

          sSQL = sSQL & " AND Books.Fiction = True "

    End If

    If cboNonFiction.Checked = True Then

          sSQL = sSQL & " AND Books.NonFiction = True "

    End If

    // You would then repeat this for the categories
    // adding to the query as items are selected...


    cmd.CommandText = sSQL
    da.SelectCommand = cmd
    da.Fill(dt)

    Me.search_datagrid.DataSource = dt              

    If dt.Rows.Count = 0 Then
        MsgBox("No record found!")                  
    End If

Catch ex As Exception
    MsgBox(ErrorToString)                           '

Finally
    conn.Close()                                    

End Try
End Sub

Just make sure you add the ORDER BY Books.Title clause to your query at the end.

From both your code and the other persons code, it seems it only affects which order the books are shown, but doesn't actually remove from the results shown. How would I fix this. — Matt07211, Jul 24 '15 at 05:59

score 0 · Answer 2 · 2015-07-25T03:44:25.910

0

The Above suggestion will solve your problem, but i suggest to use StringBuilder instead for string to build your query.In this particular scenario stringBuilder shows some efficient performance. reference.

So your snippet will be like the following :

 Dim queryBuilder As new StringBuilder
 queryBuilder.Append("SELECT Books.[Book ID], Books.Title, Books.Author, Books.Category, Books.Location, Books.[Fiction/Non-Fiction], Books.Loaned FROM Books")
 queryBuilder.Append(" Where Books.Title like '%" & Me.search_txt.Text & "%'")

now you have to gothrough the checkboxes as follows:

if cboFiction.Checked = True Then
      queryBuilder.Append(" AND Books.Fiction = True ")
End If
If cboNonFiction.Checked = True Then
      queryBuilder.Append(" AND Books.NonFiction = True ")
End If

like wise you can build the query, then execute the query as follows:

 Try
    conn = New OleDbConnection(Get_Constring)  
    conn.Open()                                
    cmd.Connection = conn
    cmd.CommandType = CommandType.Text
    cmd.CommandText = queryBuilder.ToString & "ORDER BY Books.Title"
    da.SelectCommand = cmd
    da.Fill(dt)
    Me.search_datagrid.DataSource = dt        
    If dt.Rows.Count = 0 Then
        MsgBox("No record found!")                  
    End If
Catch ex As Exception
    MsgBox(ErrorToString)                          '
Finally
    conn.Close()                                    
End Try

edited Jul 25 '15 at 03:44

answered Jul 24 '15 at 04:41

I went to try your method first, I modified my code with this, but I am getting an error that Type 'StringBuilder' is not Defined Is there anything that i may be missing from my code? – Matt07211 Jul 24 '15 at 05:16
hope that you may miss the `new` keyword to make instance od StringBuilder Class. or may miss `Imports System.Text` – Jul 24 '15 at 05:21
I Put the `Imports System.Text` in the right place now i just need to test the code, but I still don't know what that bit of code is – Matt07211 Jul 24 '15 at 05:53
From both your code and the other persons code, it seems it only affects which order the books are shown, but doesn't actually remove from the results shown. How would I fix this. – Matt07211 Jul 24 '15 at 05:59
The code posted here should actually throw an error provided the check boxes are checked. You would be adding AND conditions after the ORDER BY clause which obviously causes a syntax error.queryBuilder.Append(" ORDER BY Books.Title") after all the `If` statements, and remove the ORDER BY clause in the initial declaration of `queryBuilder` – RianBattle Jul 24 '15 at 14:59

score 0 · Accepted Answer · edited May 23 '17 at 10:26

this is not an approach i like because is open to injection (here are many alternatives) but it's the easiest way to build such a query.

here is the solution i would apply making the assumption that the 'category' checkboxes are bound to the Category field:

Dim sb As New StringBuilder
sb.Append("SELECT Books.[Book ID], Books.Title, Books.Author, Books.Category, Books.Location, Books.[Fiction/Non-Fiction], Books.Loaned FROM Books")
sb.AppendFormat(" WHERE Books.Title like '%{0}%'", Me.search_txt.Text)

' now handle the checkboxes
Dim chksb As New StringBuilder
If (chkFiction.Checked) Then chksb.Append(",'Fiction'") 'mind the leading comma
If (chkRomance.Checked) Then chksb.Append(",'Romance'") 'mind the leading comma
If (chkClassical.Checked) Then chksb.Append(",'Classical'") 'mind the leading comma
If (chksb.ToString().Length > 0) Then
 sb.AppendFormat(" AND Books.Category IN ({0})", chksb.ToString().Substring(1))
End If
sb.Append(" ORDER BY Title;")

the result is a query like this one:

SELECT  Books.[Book ID],
        Books.Title,
        Books.Author,
        Books.Category,
        Books.Location,
        Books.[Fiction/Non-Fiction],
        Books.Loaned
FROM    Books
Where   Books.Title like '%filter from text box%'
        AND Books.Category IN ('Fiction','Classic')
ORDER BY Books.Title;

Thanks for your help, your could worked great for me, but for a moment I couldn't get the fiction and non-fiction to work but that was a stuff up on my end :), and once again thanks. — Matt07211, Jul 25 '15 at 03:57

Modifing my VB.NET Code to refine my searchs with check boxs

3 Answers3