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?

[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.

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.
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.
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/
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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 ?
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()

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
grayeCommented:
That's it exactly!
FinghtingMiaoAuthor Commented:
You both are awesome!! That code above work perfectly. I learned! thank you guys so much!
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 !
CodeCruiserCommented:
Glad your problem is sorted :-)
FinghtingMiaoAuthor Commented:
mis-type
FinghtingMiaoAuthor Commented:
thanks again!
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.