System.Data.SqlClient.SqlException: The identifier that starts with '..... ' is too long.Maximum length is 128.

Work on VB.Net
On the main.aspx page, we have filter text box on the top of the page and have the datagrid on the bottom of the page.
I have couple filters and they working fine with the datagrid below if I click on filter button, excpet the filter: (@Min sq Start  to  @Min sq End).  (@Max sq Start  to @Max sq End).
The problem is: If I use either of the set above as filter, it work fine. but if I use both of the then together (1999 to 2000), (3000-5000) then we will get the following error:
The identifier that starts with '(([Format_Locations_Min_Sq_Feet] >= 1999) AND ([Format_Locations_Min_Sq_Feet] <= 2000)) AND (([Format_Locations_Max_Sq_Feet] >= ' is too long. Maximum length is 128.

What is the problem? I attached related code to it.

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 " + "([Contact_Comments] LIKE '%" + Me.Textbox_ContactComments.Text.Replace("'", "''") + "%')"
            Else
                filterExpression = "([Contact_Comments] LIKE '%" + Me.Textbox_ContactComments.Text.Replace("'", "''") + "%')"
            End If
        End If


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

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

            conn.Open()
            sqlCmd.Connection = conn
            sqlCmd.CommandText = "exec sp_GenerateUniqueRetailerList " + Chr(34) + filterExpression + Chr(34)
            sqlCmd.CommandType = CommandType.Text
            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?
 
CodeCruiserCommented:
Do you mean that instead of

sqlCmd.CommandText = "exec sp_GenerateUniqueRetailerList " + Chr(34) + filterExpression + Chr(34)
            sqlCmd.CommandType = CommandType.Text
            parentTablePrimaryKeyList = sqlCmd.ExecuteScalar()

You tried

sqlCmd.CommandText = "sp_GenerateUniqueRetailerList"
sqlCmd.Parameters.AddWithValue("your param name", filterExpression)
sqlCmd.CommandType = CommandType.StoredProcedure
parentTablePrimaryKeyList = sqlCmd.ExecuteScalar()
0
 
grayeCommented:
I suspect the problem is in the stored procedure (called sp_GenerateUniqueRetailerList), rather than in your code.

I'd recommend that you look at the stored procedure and adjust the size of the input variable to something larger than 128.
0
 
FinghtingMiaoAuthor Commented:
in the store procedure, i saw the input variable @where is nvarchar(Max). And I can execute the sp with any length of the input without error.
Just when comes to the application, then tell me the identifier is too long and max is 128. i dont know how this come from?

Thanks.
0
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.

 
grayeCommented:
OK, then I'd suggest that you run the stored procedure while passing the filter string as a parameter (rather than on the command line with exec)

http://idealprogrammer.com/net-languages/code-samples/vbnet-sql-stored-procedure-source-code/
0
 
FinghtingMiaoAuthor Commented:
Graye:

I did, I just pass the filter string to @ where variable and execute the sp directly , which runs fine.
I googled on line about the the identifiler *** is too long and the max size is 128. Most ppl talk about single quote or double quote.
http://www.sqlservercentral.com/Forums/Topic208737-8-1.aspx.

Is that my case ?
0
 
grayeCommented:
That's it exactly!
0
 
FinghtingMiaoAuthor Commented:
You both are awesome!! That code above work perfectly. I learned! thank you guys so much!
0
 
FinghtingMiaoAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 450 points for CodeCruiser's comment http:/Q_27379900.html#36919391
Assisted answer: 50 points for graye's comment http:/Q_27379900.html#36917944
Assisted answer: 0 points for FinghtingMiao's comment http:/Q_27379900.html#36919783

for the following reason:

Thank you !
0
 
CodeCruiserCommented:
Glad your problem is sorted :-)
0
 
FinghtingMiaoAuthor Commented:
mis-type
0
 
FinghtingMiaoAuthor Commented:
thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.