AutoComplete Extender using ASP.NET and Excel spreadsheet

Question

Hi,

I have a textbox on the default. aspx page bound to a Ajax Autocomplete extender.

When the user types, I want the popup to display 'USA Counties' that match the first 2 or 3 letters.

The source information could be in a Excel spreadsheet with 2 colums, Id, USACountyName

I can use a OleDb connection in the default.cs code behind page load.... but not sure how to PULL all this together.

Any working examples be useful.

thank you

Answer 1

To implement an AutoComplete Extender in ASP.NET that pulls data from an Excel spreadsheet, you can follow these steps:

1. Set Up the Excel Connection: Use an OleDb connection to read data from the Excel file. Ensure your Excel file is accessible and structured with two columns: Id and USACountyName.
2. Create a Web Method: In your code-behind (default.cs), create a web method that will query the Excel data based on the user's input. This method should accept parameters for the prefix text and return matching county names.
3. Configure the AutoComplete Extender: In your ASPX page, set up the AutoComplete Extender to call the web method you created. Specify the ServiceMethod and ServicePath properties in the extender.

Here’s a basic example to illustrate the steps:

Code Behind (default.cs)

using System;
using System.Data;
using System.Data.OleDb;
using System.Web.Services;

public partial class _Default : System.Web.UI.Page
{
    [WebMethod]
    public static string[] GetUSA Counties(string prefixText, int count)
    {
        // Define the path to your Excel file
        string excelFilePath = "path_to_your_excel_file.xlsx";
        string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";

        using (OleDbConnection conn = new OleDbConnection(connString))
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT USACountyName FROM [Sheet1$] WHERE USACountyName LIKE ?", conn);
            cmd.Parameters.AddWithValue("@prefixText", prefixText + "%");

            OleDbDataReader reader = cmd.ExecuteReader();
            List<string> counties = new List<string>();

            while (reader.Read())
            {
                counties.Add(reader[0].ToString());
            }
            return counties.ToArray();
        }
    }
}

ASPX Page

<asp:TextBox ID="txtCounty" runat="server"></asp:TextBox>
<ajaxToolkit:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
    TargetControlID="txtCounty"
    ServiceMethod="GetUSA Counties"
    MinimumPrefixLength="2"
    CompletionInterval="1000"
    EnableCaching="true"
    CompletionSetCount="10" />

Explanation:

• The GetUSA Counties method queries the Excel file for county names that start with the user’s input (prefixText).
• The AutoComplete Extender is configured to call this method when the user types in the textbox, displaying suggestions that match the input.

Make sure to handle any exceptions and validate inputs as necessary. This example provides a basic structure to get you started with integrating an AutoComplete Extender with data from an Excel spreadsheet.

---

References:

• Understanding ASP.NET AJAX Web Services