asked on

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

MageDribble

change the datagrid code

dgFirm.DataSource = objDataSet
dgFirm.DataBind()

to this:

dgFirm.SetDataBinding(objDataSet, "Table")

amruthanet

ASKER

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.

MageDribble

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.

ASKER CERTIFIED SOLUTION

razorback041

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amruthanet

ASKER

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