• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

RowFilter for DataTable Based on Complex SQL Query

I have included an image of the Search Filter page that I am working on-
I am debugging the sql string in the middle of my form- (called SQLStr)
but I am confused about building the statement..
One of the problems is I have to give the User the Option of a text field AND a repeater to filter the Company Name field;  these basically serve the same purpose but I dont know how to code it in my sql.
The user is to be given the ability to search by first letter of Company Name AND by typing a search string into the company name text field.
Can anyone help.
After feeding this string to my datatable, I show the gridview at the bottom
attached is the main BindData() code which checks all of my Search fields.
txtsearch2 is the companyname field and txtsearch is the contact last name field.
im also trying to show or hide the matching columns (agency code, company name) fields in the gridview below based on what is chosen.
Thanks
John
Sub BindData(ByVal GetFresh As Boolean, Optional ByVal TextSearch As Boolean = False, Optional ByVal Pageindexchange As Boolean = False)
            Try
                If GetFresh = True And TextSearch = False Then
                    strFilter = "All"
                Else
                    strFilter = Session("vsFilter")
                End If
                'setup connection for stored procedures
                Dim myConn2 As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("connectionString").ToString())
                Dim myComm2 As SqlCommand = New SqlCommand()
                myComm2.Connection = myConn2
 
                strId = Me.ddlLocation.SelectedItem.Value
                If strId = "0" Or strId = "All" Then
                    myComm2.CommandText = "SelectClients"
                Else
                    myComm2.CommandText = "SelectClientsByLocation"
                End If
                myComm2.CommandType = CommandType.StoredProcedure
                myConn2.Open()
                rdr2 = myComm2.ExecuteReader()
                'change datasource from datareader to datatable
                Dim DT As New DataTable()
                'Load Data from Data Reader to DataTable object
                DT.Load(rdr2)
 
                Dim dv As DataView
                dv = SortDataTable(DT, Pageindexchange)
 
                If strId = CStr(0) Or strId = "All" Then  ' 0 for all locations
                    If strFilter = "All" Then
                        strCondition = String.Empty
                    Else
                        strCondition = "CompanyName LIKE '" & Trim(strFilter) & "%' " + " AND "
                        'blank out companyname search field
                        'use repeater letter
                        txtSearch.Text = ""
                    End If
                Else
                    If strFilter = "All" Then
                        strCondition = "LocationID=" & Trim(strId) + " AND "
                    Else
                        strCondition = "CompanyName LIKE '" & Trim(strFilter) & "%'  and LocationID=" & Trim(strId) + " AND "
                        'blank out companyname search field
                        'use repeater letter
                        txtSearch.Text = ""
                    End If
                End If
 
                'test search textfield and concatenate to query string
                'with values of company name OR contactlastname
                txtSearch.Text = txtSearch.Text.Replace("'", "''")
                If txtSearch.Text <> "" Then
                    strExpression += "CompanyName LIKE '" & Trim(txtSearch.Text) & "%' "
                    strCondition += "CompanyName LIKE '" & Trim(txtSearch.Text) & "%' " + " AND "
                Else
                    strCondition += String.Empty
                    strExpression += String.Empty
                End If
 
                'test search2 textfield and concatenate to query string
                'with values of contactlast name 
                txtSearch2.Text = txtSearch2.Text.Replace("'", "''")
                If strFilter = "All" Then
                    'check if repeater is selected
               
                    If txtSearch2.Text <> "" Then
                        strExpression += "ContactLastName LIKE '%" & Trim(txtSearch2.Text) & "%' "
                        strCondition += "ContactLastName LIKE '%" & Trim(txtSearch2.Text) & "%' " + " AND "
                    Else
                        strCondition += String.Empty
                        strExpression += String.Empty
                    End If
                End If
 
               'test status drop-down
                If ddlStatus.SelectedValue = "A" Then
                    strCondition += "Status= 'A' "
                    strExpression += " Status = Active"
                Else
                    strCondition += "Status= 'I' "
                    strExpression += " Status = InActive "
                End If
 
                If txtSearch.Text <> "" Then
                    ddlAgency.SelectedValue = ""
 
                    gvAddress.Columns(4).Visible = True
                    gvAddress.Columns(5).Visible = False
 
                ElseIf ddlAgency.SelectedValue <> "" Then
 
                    gvAddress.Columns(4).Visible = False
                    gvAddress.Columns(5).Visible = True
 
                ElseIf (ddlAgency.SelectedValue = "" And txtSearch.Text = "") Then
 
                    gvAddress.Columns(4).Visible = False
                    gvAddress.Columns(5).Visible = False
 
                ElseIf (ddlAgency.SelectedValue = "" And txtSearch.Text = "" And txtSearch2.Text <> "") Then
 
                    gvAddress.Columns(4).Visible = True
                    gvAddress.Columns(5).Visible = False
 
                End If
 
 
               'case 1: Agency selected 
                If ddlAgency.SelectedValue <> "" Then
                    strCondition += " AND osccode= '" & (ddlAgency.SelectedValue) & "'"
                    strExpression += " AND AgencyCode = " & (ddlAgency.SelectedValue)
                End If
 
                'include true condition at end of sql string
                strCondition += " AND 1=1 "
 
                dv.RowFilter = strCondition
                ' dv.Sort = sortExpression
                'add ORDER BY clause 
                lblCon.Text = "SQLStr: " & strCondition
                lblExp.Text = sortExpression
                gvAddress.DataSource = dv
                Dim intRecordCount As Integer = dv.Count
                lblCount.Text = "Contacts: " & dv.Count
 
                If Session("selValue") Is Nothing Then _
                    gvAddress.Visible = False
                    lblCount.Visible = False
                Else
                    gvAddress.Visible = True
                    lblCount.Visible = True
                    gvAddress.DataBind()
                End If
                'close objects
                rdr2.Close()
                myConn2.Close()
 
            Catch exA As Exception
            End Try
            BuildAlphaPager()
        End Sub

Open in new window

final-gui.gif
0
jtrapat1
Asked:
jtrapat1
  • 4
  • 4
1 Solution
 
Bob LearnedCommented:
I can't see in that description what the resulting RowFilter expression is, and so I can't totally understand your question.
0
 
jtrapat1Author Commented:
TheLearnedOne-
Thanks for the response-
I have re-thought my whole design and I am not at all happy with the current form-

I have more important problems involving the data being Selected on this form-
If I posted my Business Rules on which three fields are acceptable -
could you recommend an alternative gui design?

I admit that this form is very complicated to design so it must be confusing to the end user-
Basically, I need an easy-to-read Search Form, easily Searchable, and easy to Select a ClientID from-
Something that will allow the end user to quickly drill down to find the Client.

I need a little help on combining the values of the drop downs and text fields to string together a valid sql statement.

I had some requests to keep the Repeater but I also had a request to add a wildcard searchable textfield for CompanyName.
So, I need at least five fields:  location (drop down), status (drop down), agency (drop down),  companyname (repeater - since most of the search results will be companies), and contactlastname (wildcard searchable textfield).

Could I possibly build One textfield to search on two separate sql fields?
Or, would this turn into a nasty sql statement?
I dont mind coding an AND OR clause but I just want to account for every situation.

Anyway, here are the three business rules, if you can help-

--Client  Name  will be two fields - first name and last name.   Both of these fields will be required whenever the client is only way to identify the customer. Services generally uses only a client first name and last name while Events will  also have a company name or agency name/code.  
 
--Agency  - this field will allow you to select an agency from the agency list using the agency name and 5 digit code.   If an agency is selected in this field you will not be able to enter information in the "company" field, but you will be able to enter information in the client name fields.  
 
--Company   - this is a text field and is intended to identify a company name.    If anything is entered in this field you will not be able to choose an agency, but you will be able to enter information in the client name fields.
 
If all three of the above fields are left blank, a new client record will not be saved and you will receive an error message.



Thanks
John
0
 
Bob LearnedCommented:
John,

Can you attach a .png screen shot of the rendered page, so that I could better understand your current situation?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jtrapat1Author Commented:
TLO-
Thanks
Attached is a .png file of the rendered grid-
I thought maybe making a combination of contactlastname OR companyname as a wildcard search field, that this would make the search easier;
But I dont know how easy the sql statement would be-
Thanks in Advance.
John
final.PNG
0
 
Bob LearnedCommented:
I have some comments/suggestions:

1) With all the foreign last names in our company, I prefer to search on either last name or first name.

2) What we do here is to define a search by either first name or last name, but not both.

3) I am not a big fan of mixed fonts.  I prefer to use Verdana or Tahoma with all my pages.

4) Is the SQLStr at the top of the only a development debug display?

5) How did you want to use a DropDownList?
0
 
jtrapat1Author Commented:
TLO-

Yes, my debug string in the middle of the screen is simply for testing-

I HAVE to search on companyname since most of the users clients will be companies...

By dropdownlist, I am using it now to search ahead and only bring back the agencies available for MY clients, not a global list of all the agencies.

John
0
 
Bob LearnedCommented:
John,

Those are still pretty vague answers to the questions.  I don't understand your requirement enough to help you, and we could go at this, but I would have to admit that I would quickly grow tired of that approach.  

I am not always the best at reading the words, and getting the picture.  I had you attach a picture, and it still didn't help.  So, now I would like to find a description that helps bring all this together.
0
 
jtrapat1Author Commented:
TheLearnedOne:
Regarding this topic-
I just had a question about your "mixed fonts" comment.
Can you expand on this and explain where you specify the fonts used by the user's browser?

Thanks
John
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now