Is there a max limit a autocomplete extender can have?

Question

I have an an autocomplete extender on my page that searches a database based on last name. For some reason when searching "Smith" which should return 1564 results nothing is being returned? When I search other names it works fine "Jones" etc. I am thinking "Smith" has the most rows and there is a limit or something? Any ideas?

 <asp:TextBox ID="DoctorNameTextBox" runat="server" Height="24px" Width="739px" 
            Font-Size="Small"></asp:TextBox>
        <asp:AutoCompleteExtender ID="AutoCompleteExtender" runat="server" 
    DelimiterCharacters="" Enabled="True" ServicePath="AutoComplete.asmx" 
            ServiceMethod="GetCompletionList" TargetControlID="DoctorNameTextBox" 
    MinimumPrefixLength="2" UseContextKey="true" ContextKey="StateDropDown"
            CompletionListElementID="autocompleteDropDownPanel" 
            onclientitemselected="getSelected" CompletionSetCount="20" 
            ShowOnlyCurrentWordInCompletionListItem="True">

Public Function GetCompletionList(prefixText As String, count As Integer, ByVal contextKey As String) As String()
    Try
        Dim Con As SqlConnection
        Dim cmd As SqlCommand
        Con = New SqlConnection
        Dim test As String
        test = contextKey
        Con.ConnectionString = ""
        Con.Open()

        cmd = New SqlCommand
        cmd.Connection = Con
        cmd.CommandText = "SELECT NPI, [Entity Type Code], [Provider Last Name (Legal Name)], [Provider First Name],[Provider First Line Business Mailing Address], [Provider Business Mailing Address City Name], [Provider Business Mailing Address State Name], [Provider Business Mailing Address Postal Code] FROM NPIData WHERE   ([Provider Business Mailing Address State Name] = @State) AND ([Provider Last Name (Legal Name)] LIKE N'%' + @Provider + N'%') ORDER BY [Provider First Name]"
        cmd.Parameters.AddWithValue("@Provider", prefixText)
        cmd.Parameters.AddWithValue("@State", contextKey)
        Dim customers As List(Of String) = New List(Of String)
        Dim reader As SqlDataReader = cmd.ExecuteReader()


        While reader.Read
            customers.Add(reader("Provider Last Name (Legal Name)").ToString + ", " + reader("Provider First Name").ToString + "   " + reader("Provider First Line Business Mailing Address").ToString + "  " + reader("Provider Business Mailing Address City Name").ToString + ", " + reader("Provider Business Mailing Address State Name").ToString + "  " + reader("Provider Business Mailing Address Postal Code").ToString + "  " + reader("NPI").ToString)

        End While


        Con.Close()

        Return customers.ToArray
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

End Function

Answer 1

When the list returned by the Web Service exceeds the max jsonSerialization limit of 102400 (string length), the AutoCompleteExtender fails silently. A way to "fix" this (although is not practical) is to increase the value in the web.config as so:

Make sure you have this section in your sectionGroup section. If you don't, add it.

<sectionGroup name="system.web.extensions" type="System.Web.Extensions">
    <sectionGroup name="scripting" type="System.Web.Extensions">
         <sectionGroup name="webServices" type="System.Web.Extensions">
                  <section name="jsonSerialization" type="System.Web.Extensions"/>
          </sectionGroup>
    </sectionGroup>
</sectionGroup> 

And then set the value:

<configuration> 
   <system.web.extensions>
       <scripting>
           <webServices>
               <jsonSerialization maxJsonLength="50000000"/>
           </webServices>
       </scripting>
   </system.web.extensions>
</configuration> 

See this other answer.