exception handler

I have .net application (VB.net).
 We  have several  text boxes for users to enter search key words, then click on search button, the datagrid will display the records which matches the key words.

But the problem is if the user mis-type some key words which does not existed in the database, then they will get the Server Error . bacially the link broken.
What I am looking for is, if the user mis-type any search key words, instead of seeing the server error, A message will pop  up say"NO result founded"

IHow can i write the exception handler to avoid the server error.

Thank you a lot.
Protected Sub ApplyFilter_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ApplyFilter.Click

        Dim filterExpression As String = String.Empty

        If (Me.RadComboBox_CategoryType.SelectedValue <> "") Then
            filterExpression = "([Category_Type] = '" + Me.RadComboBox_CategoryType.SelectedValue + "')"
        End If

        If (Me.Textbox_DBA.Text <> "") Then
            filterExpression = "([DBA] LIKE '%" + Me.Textbox_DBA.Text.Replace(",", "''") + "%')"
        End If

        'If (Me.RadComboBox_ContactType.SelectedValue <> "") Then
        ' filterExpression = "([Contact_Type] = '" + Me.RadComboBox_ContactType.SelectedValue + "')"
        ' End If


        If ((Me.Textbox_MinSqFt_Start.Text <> "") And (Me.Textbox_MinSqFt_End.Text <> "")) Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "(" + "([Format_Locations_Min_Sq_Feet] >= " + Me.Textbox_MinSqFt_Start.Text + ")" + " AND " + "([Format_Locations_Min_Sq_Feet] <= " + Me.Textbox_MinSqFt_End.Text + ")" + ")"
            Else
                filterExpression = "(" + "([Format_Locations_Min_Sq_Feet] >= " + Me.Textbox_MinSqFt_Start.Text + ")" + " AND " + "([Format_Locations_Min_Sq_Feet] <= " + Me.Textbox_MinSqFt_End.Text + ")" + ")"
            End If
        ElseIf ((Me.Textbox_MinSqFt_Start.Text <> "") And (Me.Textbox_MinSqFt_End.Text = "")) Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "([Format_Locations_Min_Sq_Feet] >= " + Me.Textbox_MinSqFt_Start.Text + ")"
            Else
                filterExpression = "([Format_Locations_Min_Sq_Feet] >= " + Me.Textbox_MinSqFt_Start.Text + ")"
            End If
        ElseIf ((Me.Textbox_MinSqFt_Start.Text = "") And (Me.Textbox_MinSqFt_End.Text <> "")) Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "([Format_Locations_Min_Sq_Feet] <= " + Me.Textbox_MinSqFt_End.Text + ")"
            Else
                filterExpression = "([Format_Locations_Min_Sq_Feet] <= " + Me.Textbox_MinSqFt_End.Text + ")"
            End If
        End If

        If ((Me.Textbox_MaxSqft_Start.Text <> "") And (Me.Textbox_MaxSqft_End.Text <> "")) Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "(" + "([Format_Locations_Max_Sq_Feet] >= " + Me.Textbox_MaxSqft_Start.Text + ")" + " AND " + "([Format_Locations_Max_Sq_Feet] <= " + Me.Textbox_MaxSqft_End.Text + ")" + ")"
            Else
                filterExpression = "(" + "([Format_Locations_Max_Sq_Feet] >= " + Me.Textbox_MaxSqft_Start.Text + ")" + " AND " + "([Format_Locations_Max_Sq_Feet] <= " + Me.Textbox_MaxSqft_End.Text + ")" + ")"
            End If
        ElseIf ((Me.Textbox_MaxSqft_Start.Text <> "") And (Me.Textbox_MaxSqft_End.Text = "")) Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "([Format_Locations_Max_Sq_Feet] >= " + Me.Textbox_MaxSqft_Start.Text + ")"
            Else
                filterExpression = "([Format_Locations_Max_Sq_Feet] >= " + Me.Textbox_MaxSqft_Start.Text + ")"
            End If
        ElseIf ((Me.Textbox_MaxSqft_Start.Text = "") And (Me.Textbox_MaxSqft_End.Text <> "")) Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "([Format_Locations_Max_Sq_Feet] <= " + Me.Textbox_MaxSqft_End.Text + ")"
            Else
                filterExpression = "([Format_Locations_Max_Sq_Feet] <= " + Me.Textbox_MaxSqft_End.Text + ")"
            End If
        End If

        If (Me.Textbox_Contact_FirstName.Text <> "") Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "([Contact_First_Name] LIKE '%" + Me.Textbox_Contact_FirstName.Text.Replace("'", "''") + "%')"
            Else
                filterExpression = "([Contact_First_Name] LIKE '%" + Me.Textbox_Contact_FirstName.Text.Replace("'", "''") + "%')"
            End If
        End If

        If (Me.Textbox_Contact_LastName.Text <> "") Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "([Contact_Last_Name] LIKE '%" + Me.Textbox_Contact_LastName.Text.Replace("'", "''") + "%')"
            Else
                filterExpression = "([Contact_Last_Name] LIKE '%" + Me.Textbox_Contact_LastName.Text.Replace("'", "''") + "%')"
            End If
        End If

        If (Me.Textbox_CompanyName.Text <> "") Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "([Company] LIKE '%" + Me.Textbox_CompanyName.Text.Replace("'", "''") + "%')"
            Else
                filterExpression = "([Company] LIKE '%" + Me.Textbox_CompanyName.Text.Replace("'", "''") + "%')"
            End If
        End If

        If (Me.Textbox_ContactComments.Text <> "") Then
            If (filterExpression <> "") Then
                filterExpression = filterExpression + " AND " + "([StateOfExpansion] LIKE '%" + Me.Textbox_ContactComments.Text.Replace("'", "''") + "%')"
            Else
                filterExpression = "([StateOfExpansion] LIKE '%" + Me.Textbox_ContactComments.Text.Replace("'", "''") + "%')"
            End If
        End If


        If ((filterExpression.Trim()).Length > 0) Then

            Dim conn As New SqlConnection("Data Source=**;Initial Catalog=RetailerContact;Persist Security Info=True;User ID=User;Password=***User")
            Dim sqlCmd As New SqlCommand()
            Dim parentTablePrimaryKeyList As Object = Nothing

            conn.Open()
            sqlCmd.Connection = conn
            sqlCmd.CommandText = "sp_GenerateUniqueRetailerList"
            sqlCmd.Parameters.AddWithValue("whereClause", filterExpression)
            sqlCmd.CommandType = CommandType.StoredProcedure
            parentTablePrimaryKeyList = sqlCmd.ExecuteScalar()

            
            If (parentTablePrimaryKeyList <> Nothing) Then
                Me.SqlDataSource_RETAILERCONTACTDB.FilterExpression = "_PK_RetailerContact IN (" + parentTablePrimaryKeyList.ToString() + ")"
                Me.RadGrid1_LIST_OF_RECORDS.Rebind()
                Me.RadGrid1_LIST_OF_RECORDS.Columns(0).Visible = False
                Session("FILTER") = parentTablePrimaryKeyList.ToString()
            End If

            conn.Close()

        End If

    End Sub

Open in new window

FinghtingMiaoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
You do not tell us what the error message is, so it is hard to pinpoint exactly what your problem is.

You do not tell on which line you get the error, so I suspect that it is ExecuteScalar, and the remaining of this discussion does the same.

Usually, you should not get an error from the server for such a condition. You should receive a Null value, and the error would be a .NET exception, not a server error.

Put an exception handler around the exception:
Try
     parentTablePrimaryKeyList = sqlCmd.ExecuteScalar()
Catch ex As SQLException
   Stop
Catch ex As Exception
   Stop
End Try

Open in new window

Reproduce the problem.

If the problem is indeed when calling ExecuteScalar, the application will end up in debugging mode on one of the 2 Stop. Use the debugger to get information about the exception. From that information, you might be able to pinpoint the problem and correct it at the source.

If not, replace the Stop that will be hit by your "NO result founded" message and delete the other Stop. Do not leave the Stop instructions in the final version of the application, because they become and error at the user level. They should be used only for debugging.
CodeCruiserCommented:
Try changing

If (parentTablePrimaryKeyList <> Nothing) Then

to

If (IsDBNull(parentTablePrimaryKeyList) = False AND IsNothing(parentTablePrimaryKeyList) = False ) Then
FinghtingMiaoAuthor Commented:
Yes, the error it is get from ExecuteScalar.
I just put "4343" as company Name (one of the filter), then get the error say"Invalid length parameter passed ot the substring function"

I used the exception handler, but still get the error.
I attahed the screen shot.

Thank you a lot!
Issue.bmp
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

CodeCruiserCommented:
The problem might be in your stored procedure. Are you doing any substring on this parameter?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jacques Bourgeois (James Burger)PresidentCommented:
It's normal that you get the error even with the error handler. The error handler does not prevent the error, it enables you to trap it, analyze it and react accordingly.

As CodeCruiser told you, the problem is in the Stored procedure. You are probably sending a string that is not of the appopriate length for the command in the procedure. Either correct the stored procedure, or check the length of the String returned by the user input and tell the user which length it should have.
FinghtingMiaoAuthor Commented:
Thank you guys! I will try to debug the sp.
thanks again!
FinghtingMiaoAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.