Autocomplete jquery and SQL , ASP.NET

Question

I am trying to create an autocomplete textbox using jquery which will be bound to SQL database. I also want to place a dropdownlist on the page so based on initial selection autocomplete textbox will retrieve data from different tables. It's an ASP.NET 2.0 page and code behind is VB.NET. I have AJAX autocomplete working but it matches only first characters and its not as robust as jquery. Can anyone share a sample code that will retrieve search data from SQL and also give me directions on how to make the table name dynamic?

Any help is greatly appreciated

Mart

immutabl · Accepted Answer

You could write a handler (.ashx) to return the query results from the db for both your text search and table names. For table names have a look at INFORMATION_SCHEMA.TABLES IN SQL Server...

Have a look at this .NET AJAX Calls to ASMX or ASPX or ASHX? for a slightly more modern method using ScriptService - the basic idea is the same.

I'll post code tomorrow if required (its v.late),

Hth.

EDIT:

At its simplest (you'll appreciate the issues involved in doing something more involved as you go) lets imagine you have three tables: User, Department and Product.

Table: User
ID int
forename varchar(50)
surname varchar(50)
DateOfBirth datetime

Table: Department
Id int
Name varchar

Table: Product
Id int
Name varchar

And you need 3 Sprocs to select by part of the Name:

The form of the sproc for Products and Departments is the same:

CREATE PROCEDURE dbo.ProductSelect (dbo.DepartmentSelect)
    @qry VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
FROM dbo.Product  (dbo.Department)
WHERE [Name] LIKE '%' + @qry + '%'
END

And for Users slightly different:

CREATE PROCEDURE [dbo].[UserSelect]
    @qryTerm VARCHAR(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT U.Id, U.Forename + ' ' + U.Surname AS [Name], U.DateOfBirth
FROM 
    dbo.[User] U
WHERE
    (
        forename LIKE '%' + @qryTerm + '%'
        OR
        surname LIKE '%' + @qryTerm + '%'
    )
ORDER BY 
    surname, Forename
END

Basic page/control markup:


    Tables:
    




    

....

To bind the dropdownlist to a list of tables you need a sproc/query along the lines of:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' ORDER BY TABLE_NAME _{[Change the schema name to that of your schema]}

//Bind your dropdown to the list of tables
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["dbConnString"]))
    {
        using (SqlCommand cmnd = conn.CreateCommand())
        {
            cmnd.CommandType = CommandType.StoredProcedure;
            cmnd.CommandText = "dbo.TableSelect";

            conn.Open();

            System.Text.StringBuilder sb = new System.Text.StringBuilder();

            using (SqlDataReader rdr = cmnd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                string _fullName;
                while (rdr.Read())
                {
                    _fullName = string.Format("{0}.{1}",rdr["Table_Schema"].ToString(), rdr["Table_Name"].ToString());
                    ddlTables.Items.Add(new ListItem(_fullName,_fullName));
                }
                ddlTables.Items.Insert(0, new ListItem("Select Table ..."));
            }
        }
    }

Then you need to wire up your textbox to a JavaScript function that responds to the keyup event:

//Wire up the textbox
        txtUser.Attributes.Add("onkeyup", "doLookup();");

function doLookup(){

}

The modern way of doing this is by using JQuery's AJaX functionality. Basically you make a call to the URL of your handler which will return the query results which you then bind or output to a GUI element underneath your textbox so it looks kind of like a dropdownlist. User then clicks on one of the entries returned to copy it to the textbox.

Some handler code:

    public void ProcessRequest(HttpContext context)
    {
        HttpRequest req = context.Request;
        if (string.IsNullOrEmpty(req.QueryString["qry"]) || string.IsNullOrEmpty(req.QueryString["tableName"])){
            return;//  You could return something meaningful if no sql query is passed
        }


        String sqlstr = req.QueryString["qry"];
        String tableName = req.QueryString["tableName"];

        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["dbConnStr"])){
            using (SqlCommand cmnd = conn.CreateCommand())
            {
                cmnd.CommandText = tableName + "Select";
                cmnd.CommandType = CommandType.StoredProcedure;
                SqlParameter pram = new SqlParameter("@qry", sqlstr);
                pram.Direction = ParameterDirection.Input;
                cmnd.Parameters.Add(pram);

                pram = new SqlParameter("@tableName", tableName);
                pram.Direction = ParameterDirection.Input;
                cmnd.Parameters.Add(pram);

                conn.Open();

                using (SqlDataReader rdr = cmnd.ExecuteReader(CommandBehavior.CloseConnection)){
                    while (rdr.Read()){
                        sb.Append(rdr["Name"].ToString() + "
"); //Modern object-oriented thing to do is build up a JSON string and return that.
                    }
                }

            }
        }

        context.Response.ContentType = "text/plain";//or "text/JSON"
        context.Response.Write(sb.ToString());
    }

Good luck. Let me know if you need any more detail...

Autocomplete jquery and SQL , ASP.NET

Answers (2)

Related Questions