Autocomplete jquery and SQL , ASP.NET

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 co开发者_Go百科de 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

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:

<div>
    Tables:
    <asp:DropDownList runat="server" ID="ddlTables" />

</div>

<div>
    <asp:TextBox runat="server" ID="txtUser" />
</div>
....

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() + "<br/>"); //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...

Try this guy, might help you: http://1code.codeplex.com/