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

x
?
Solved

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

Posted on 2011-10-04
11
Medium Priority
?
892 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:FinghtingMiao
  • 6
  • 3
  • 2
11 Comments
 
LVL 41

Expert Comment

by:graye
ID: 36912816
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
 

Author Comment

by:FinghtingMiao
ID: 36917719
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
 
LVL 41

Expert Comment

by:graye
ID: 36917944
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:FinghtingMiao
ID: 36918186
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 36919391
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
 
LVL 41

Expert Comment

by:graye
ID: 36919532
That's it exactly!
0
 

Author Comment

by:FinghtingMiao
ID: 36919783
You both are awesome!! That code above work perfectly. I learned! thank you guys so much!
0
 

Author Comment

by:FinghtingMiao
ID: 36919809
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36919796
Glad your problem is sorted :-)
0
 

Author Comment

by:FinghtingMiao
ID: 36919810
mis-type
0
 

Author Closing Comment

by:FinghtingMiao
ID: 36919817
thanks again!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question