[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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

0
FinghtingMiao
Asked:
FinghtingMiao
  • 3
  • 2
  • 2
3 Solutions
 
Jacques Bourgeois (James Burger)Commented:
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.
0
 
CodeCruiserCommented:
Try changing

If (parentTablePrimaryKeyList <> Nothing) Then

to

If (IsDBNull(parentTablePrimaryKeyList) = False AND IsNothing(parentTablePrimaryKeyList) = False ) Then
0
 
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
CodeCruiserCommented:
The problem might be in your stored procedure. Are you doing any substring on this parameter?
0
 
Jacques Bourgeois (James Burger)Commented:
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.
0
 
FinghtingMiaoAuthor Commented:
Thank you guys! I will try to debug the sp.
thanks again!
0
 
FinghtingMiaoAuthor Commented:
Thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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