[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

Search form - Not getting the expected results - wrong query?- Please help

 I have two forms:  
One as Searchform which does the searching and the Searchresultspage to display the results.

I have given the code behind to do the searching.  But something is wrong I am not getting any results displayed.  No error.  Please check the query and let me know where I am doing wrong.  I am not getting the expected results.  Always empty datagrid.  Please help

I appreciate the help.

Search form:

 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
    End Sub

    Private Sub btnsearchfirms_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearchfirms.Click
        Session("firm") = txtFirm.Text
        Session("FRN") = txtFRN.Text
        Session("City") = txtCity.Text
        Session("Disc") = Discipline.Checked
        Server.Transfer("FirmSearchResults.aspx")
    End Sub
End Class

FirmSearchResults page:

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim objConnection As New SqlClient.SqlConnection
        Dim objCommand As New SqlClient.SqlCommand
        Dim objAdapter As New SqlClient.SqlDataAdapter
        Dim objDataSet As New DataSet
        Dim strFRN As String
        Dim strcity As String
        Dim strFirm As String
        Dim strDis As Boolean
        Dim strSQLQuery As String
        Dim fields

        fields = "firm,residentmanager,City,state"

        ' Get Search
        If Not Page.IsPostBack Then
            strFirm = Session("Firm")
            strDis = Session("Disc")
            strFRN = Session("FRN")
            strcity = Session("city")

            Try
                objConnection = New SqlClient.SqlConnection("Data Source=DAISQL-DEV1;" _
                  & "Initial Catalog=KSBOA_DATA;User Id=guest000;Password=guest000;")
                strSQLQuery = "SELECT " & fields & " FROM tblboadata where "

                         If strFirm <> "null" And strFRN <> "null" And strcity <> "null" And strDis = "null" Then
                    strSQLQuery = strSQLQuery & " firm like '" & strFirm & "%" & "' and permitnum = '" & strFRN & "' and city = '" & strcity & "' order by Firm asc"
                ElseIf strFirm = "null" And strFRN <> "null" And strcity <> "null" And strDis = "null" Then
                    strSQLQuery = strSQLQuery & " permitnum like '" & strFRN & "%" & "' and city = '" & strcity & "%" & "' order by firm asc"
                ElseIf strFirm <> "null" And strFRN = "null" And strcity <> "null" And strDis = "null" Then
                    strSQLQuery = strSQLQuery & " permitnum like '" & strFRN & "%" & "' and city = '" & strcity & "%" & "' order by firm asc"
                ElseIf strFirm = "null" And strFRN = "null" And strcity <> "null" And strDis = "null" Then
                    strSQLQuery = strSQLQuery & " city like '" & strcity & "%" & "' order by firm asc"
                ElseIf strFirm <> "null" And strFRN <> "null" And strcity = "null" And strDis = "null" Then
                    strSQLQuery = strSQLQuery & " firm like '" & strFirm & "%" & "' and permitnum like '" & strFRN & "%" & "' order by firm asc"
                ElseIf strFirm <> "null" And strFRN = "null" And strcity = "null" And strDis = "null" Then
                    strSQLQuery = strSQLQuery & " firm like '" & strFirm & "%" & "' order by firm asc"
                ElseIf strFirm = "null" And strFRN <> "null" And strcity = "null" And strDis = "null" Then
                    strSQLQuery = strSQLQuery & " permitnum like '" & strFRN & "%" & "' order by firm asc"
                ElseIf strFirm = "null" And strFRN = "null" And strcity = "null" And strDis <> "null" Then
                    strSQLQuery = strSQLQuery & " permitnum like '" & strFRN & "%" & "' order by firm asc"
                Else
                    strSQLQuery = strSQLQuery & " firm = null and FRN = null and city = null And strDis = null"
                End If

                objCommand = New SqlClient.SqlCommand(strSQLQuery, objConnection)

                ' Get a DataSet to bind the DataGrid to
                objAdapter = New SqlClient.SqlDataAdapter(objCommand)
                objDataSet = New DataSet
                objAdapter.Fill(objDataSet)

                Session("ActiveDataTable") = objDataSet
                ' DataBind DG to DS
                dgFirm.DataSource = objDataSet
                dgFirm.DataBind()
            Catch ex As Exception

            Finally
                If objConnection.State <> ConnectionState.Closed Then
                    objConnection.Close()
                End If
            End Try
        End If

    End Sub
0
amruthanet
Asked:
amruthanet
  • 2
  • 2
1 Solution
 
MageDribbleCommented:
change the datagrid code                

dgFirm.DataSource = objDataSet
dgFirm.DataBind()

to this:

dgFirm.SetDataBinding(objDataSet, "Table")
0
 
amruthanetAuthor Commented:
When I try to use this I am getting an error that

dgFirm.SetDataBinding(objDataSet, "Table")
is not a member of System.Web.UI.webcontrol.datagrid

I think it is something wrong in my query.  I don't know.  Please help.
0
 
MageDribbleCommented:
nope my datagrid code is for window forms not web controls - so it may be my code :)

revert back to your code then put a message box after your query and print your actual query to the screen and see if it looks right.  you may need to change your WHERE clause on the query.
0
 
razorback041Commented:
in your query...try

strFirm <> string.empty or strFirm is DBNull.value  instead of strFirm = "null" unless you are sure the value of the string is "null"

so your first if statement:

If  not strFirm is dbnull.value And strFRN is dbnull.value And strcity is dbnull.value Then
      if strDis is dbnull.value then
            strSQLQuery = strSQLQuery & " firm like '" & strFirm & "%" & "' and permitnum = '" & strFRN & "' and city = '" & strcity & "' order by Firm asc"
      end if
elseif

also in your queries..you should use "is null" instead of "= null"

strSQLQuery = strSQLQuery & " firm is null and FRN is null and city is null And strDis is null"

try that and see if you get anything.

also, step through the code, and see exactly what query is being passed in, copy it and try it in ent manager to see if you actually get a return, or if the query dies...this will help to see what we should focus on next.


 
0
 
amruthanetAuthor Commented:
Thanks.  Sorry I was out of town for couple of days.

I have put the sql query in my lableinfo and this is what I got.

It looks like it is working wherever I have the city fields.

But when I try the firm, it works for the below two names, but not for the others

Abanathy, Carla J., CPA
Acord, Cox & Company

But it is not bring the results for

Internal Revenue Service
Petroleum, Inc.

SELECT firm,residentmanager,City,state FROM tblboadata where firm like 'Internal Revenue Service%' order by firm asc

I am taking these values directly from my SQL table, so I know for sure these values exists.

For the FRN:  

It is not bringing the results at all.
SELECT firm,residentmanager,City,state FROM tblboadata where permitnum = '1261' order by firm asc

For Discipline- This is a check box- How should I handle this one?  I have declared this as a boolean.

SELECT firm,residentmanager,City,state FROM tblboadata where discipline = 'YES' order by firm asc

Pleas help.

This is my changed code:

 If Not Page.IsPostBack Then
            strFirm = Session("Firm")
            strDis = Session("Disc")
            strFRN = Session("FRN")
            strcity = Session("city")

            Try
                objConnection = New SqlClient.SqlConnection("Data Source=DAISQL-DEV1;" _
                  & "Initial Catalog=KSBOA_DATA;User Id=guest000;Password=guest000;")
                Dim strsqlquery As String = "SELECT " & fields & " FROM tblboadata where "

                   If strFirm <> String.Empty And strFRN = String.Empty And strcity = String.Empty And strDis = False Then
                    strsqlquery = strsqlquery & " firm like '" & strFirm & "%" & "' order by firm asc"
                ElseIf strFirm = String.Empty And strFRN <> String.Empty And strcity = String.Empty And strDis = False Then
                    strsqlquery = strsqlquery & " permitnum = '" & strFRN & "' order by firm asc"
                ElseIf strFirm = String.Empty And strFRN = String.Empty And strcity <> String.Empty And strDis = False Then
                    strsqlquery = strsqlquery & " city like '" & strcity & "%" & "' order by firm asc"
                ElseIf strFirm = String.Empty And strFRN = String.Empty And strcity = String.Empty And strDis <> False Then
                    strsqlquery = strsqlquery & " discipline = '" & "YES" & "' order by firm asc"
                ElseIf strFirm <> String.Empty And strFRN <> String.Empty And strcity = String.Empty And strDis = False then
                    strsqlquery = strsqlquery & " firm = '" & strFirm & "' and permitnum = '" & strFRN & "' order by firm asc"
          Else
                    strsqlquery = strsqlquery & " strfirm = string.empty and strFRN = string.empty and strcity = string.empty And strDis = false"
                End If
                Labelinfo.Text = strsqlquery


0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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