ASP.NET AutoCompleteExtender 模糊查詢下拉式選單

查詢鼎新會計科目為例

.ASPX

        <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods = "true" />

        <asp:TextBox ID="txtItem" runat="server"></asp:TextBox>
        <asp:AutoCompleteExtender
             runat="server"
             ID="AutoCompleteExtender1"
             TargetControlID="txtItem"
             ServiceMethod="SearchItem"
             MinimumPrefixLength="1"
             CompletionInterval="100"
             EnableCaching="false"
             CompletionSetCount="10"
             FirstRowSelected = "false" >
        </asp:AutoCompleteExtender>

.ASPX.VB

Imports System.Data.SqlClient

    <System.Web.Script.Services.ScriptMethod(), System.Web.Services.WebMethod()> _
    Public Shared Function SearchItem(ByVal prefixText As String, ByVal count As Integer) As List(Of String)


        Dim cn As New SqlConnection
        cn.ConnectionString = ConfigurationManager.ConnectionStrings("myConn").ConnectionString
        cn.Open()

        Dim cmd As New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = "select distinct top (@MyCount) TH004, TH008 from COPTH where TH004 LIKE @SearchText + '%'"
        cmd.Parameters.AddWithValue("@MyCount", count)
        cmd.Parameters.AddWithValue("@SearchText", prefixText)

        Dim customers As List(Of String) = New List(Of String)
        Dim sdr As SqlDataReader
        sdr = cmd.ExecuteReader()

        While sdr.Read
            customers.Add(sdr("TH004").ToString & vbNewLine & " " & sdr("TH008").ToString)
        End While

        cn.Close()
        Return customers


    End Function

web.config
  <connectionStrings>
    <add name="myConn" connectionString="Data Source=.\SQLExpress;Initial Catalog=myDB;Persist Security Info=True;User ID=myID;Password=myPWD"/>
  </connectionStrings>