Solved

jQuery AutoComplete from Database

Posted on 2013-01-22
32
507 Views
Last Modified: 2013-01-29
Hello Experts,

I'm using ASP.NET 4, C# and SQL. I need to retrieve two values from my database. The first value needs to be retrieved using jQuery AutoComplete from my database (this part works, see code below). The Second value needs to be returned to an ASP.NET Label Control that is related to the value selected from the jQuery AutoComplete TextBox (This part I need help with). I created a LEFT INNER JOIN using SQL which works fine in SQL but the code that I have for the jQuery AutoComplete below will not allow me to add the value from my database to a Label Control.

Code for jQuery AutoComplete from Database:
    [WebMethod]
    public static List<string> GetAutoCompleteData(string username)
    {
        List<string> result = new List<string>();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT pn.product_name, mn.model_name FROM ProductNumbers AS pn LEFT JOIN ModelNumbers AS mn ON pn.product_id = mn.product_id WHERE product_name LIKE '%'+@SearchText+'%'", conn))
            {
                conn.Open();
                cmd.Parameters.AddWithValue("@SearchText", username);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    result.Add(dr["product_name"].ToString());
                }

                return result;
            }
        }
    }

Open in new window


Please help!!!

Thanks in advance!!!
0
Comment
Question by:asp_net2
  • 15
  • 14
  • 2
32 Comments
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
check reply and code of mark3asp : http://forums.asp.net/t/1672421.aspx/1
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Hi leakim971,

Not sure how that link is related to my question. I looked at it and it does not seem to explain how to retrieve second value based on selected value from AutoComplete textbox. Do you know how to do this?
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
the answer of mark3asp show you :
- how to send a custom object back to the browser
- how to use this object and populate a second field (hidden or not, label, ...)

if you want to simplify, assuming model_name is the database field you want to see in the label :
result.Add(dr["product_name"].ToString() + ":" + dr["model_name"].ToString());

and split this in the parse function

arr = data[ i ].split(":")
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Hi leakim971,

Ok, i'm a little confused now. I found the following below by doing a search on google. The following below is what I'm currently using to retrieve data for one field in my DB using the jquery autocomplete by retrieving value from my DB.

Current CodeBehind:
    [WebMethod]
    public static List<string> GetAutoCompleteData(string username)
    {
        List<string> result = new List<string>();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT pn.product_name, mn.model_name FROM ProductNumbers AS pn LEFT JOIN ModelNumbers AS mn ON pn.product_id = mn.product_id WHERE product_name LIKE '%'+@SearchText+'%'", conn))
            {
                conn.Open();
                cmd.Parameters.AddWithValue("@SearchText", username);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    result.Add(dr["product_name"].ToString());
                }

                return result;
            }
        }
    }

Open in new window


Current Markup:
    <script type="text/javascript">
        $(document).ready(function () {
            SearchText();
        });

        function SearchText() {
            $(".autosuggest").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        url: "create.aspx/GetAutoCompleteData",
                        data: "{'username':'" + document.getElementById('txtProductNumber').value + "'}",
                        dataType: "json",
                        success: function (data) {
                            response(data.d);
                        },

                        error: function (result) {
                            alert("Error");
                        }
                    });
                }
            });
        }
    </script>

Open in new window


I'm not sure how to implement what you are meaning above into my current CodeBehind/Markup. I'm not very knowledable with jQuery yet.

I'm also not sure how you can assign "model_name" to a label control. It does not look like you assigned it above.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
OK, I was thinking you're using the deprecated jquery autocomplete plugin bassistance
look for AutoComplete: Source Custom Data Format here :
http://www.codeproject.com/Articles/78704/3-Different-Approaches-for-Implementing-the-JQuery
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
That did not help/or explain what I need. Please see in the steps below as to what I'm looking help for.

Step 1: User types in a couple letters into TextBox 1. The jQuery AutoComplete will retrieve a list of values from a table in my Database based on what is typed in. (This part works fine). The values are stroed in a table callled "ProductNumbers".

Step 2: I need to retrieve a related value stored in another Table called "ModelNumber" that has a relation to "ProductNumbers". So if I select a value from Step 1 I need to display it's related value to a "Label" control.

Your first solution did work in post "38806252", but it put the related value into the same TextBox value as the AutoComplete TextBox. I need that second related value to go to a Label Control. That is what I need help with. Is there a way to assign that second value to a Label Control from the code below that you assisted with? You will see in the code below that I have modified and added the following line below. The "model_name" value just needs to be added to a Label control and not the same TextBox control that is used for AutoComplete.

result.Add(dr["product_name"].ToString() + ":" + dr["model_name"].ToString());

Updated Code:
    [WebMethod]
    public static List<string> GetAutoCompleteData(string username)
    {
        List<string> result = new List<string>();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT pn.product_name, mn.model_name FROM ProductNumbers AS pn LEFT JOIN ModelNumbers AS mn ON pn.product_id = mn.product_id WHERE product_name LIKE '%'+@SearchText+'%'", conn))
            {
                conn.Open();
                cmd.Parameters.AddWithValue("@SearchText", username);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    //result.Add(dr["product_name"].ToString());
                    result.Add(dr["product_name"].ToString() + ":" + dr["model_name"].ToString());
                }

                return result;
            }
        }
    }

Open in new window

0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
not sure what you say it did not help...

the webmethod return a Name and the Abbreviation and on the client side put the value in projetc and selectedValue fields :

$('#project').val(ui.item.Name);
$('#selectedValue').text("Selected value:" + ui.item.Abbreviation);

the same way you want to put the fields inside ".autosuggest" and its label :

$(".autosuggest").val(ui.item.Name);
$('#label_ID').text("Selected value:" + ui.item.Abbreviation);
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Maybe, I'm just confused then. Not sure how all this ties together nor do I know what to modify. I guess I was looking for something like Label1.Text = dr["model_name"].ToString());

Could you tell me what I need to add to my current jQuery function or WebMethod?
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
Label1.Text = dr["model_name"].ToString());

yes but with an Ajax call (made by the autocomplete plugin
Once you return the label on the server side (I think you know how to do that with c# or just check the link, you need to use class)

you just need, assuming Label1 is the ID of your label :

$(".autosuggest").val(ui.item.Name);
$('#<%= Label1.ClientID %>').text(ui.item.model_name);
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
I'm very sorry leakim971, I'm still not understanding were to put what you meantioned above at?

Below is my current jQuery for autocomplete:
    <script type="text/javascript">
        $(document).ready(function () {
            SearchText();
        });

        function SearchText() {
            $(".autosuggest").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        url: "create.aspx/GetAutoCompleteData",
                        data: "{'username':'" + document.getElementById('txtProductNumber').value + "'}",
                        dataType: "json",
                        success: function (data) {
                            response(data.d);
                        },

                        error: function (result) {
                            alert("Error");
                        }
                    });
                }
            });
        }
    </script>

Open in new window


Do I need to change the CodeBehind below?
    [WebMethod]
    public static List<string> GetAutoCompleteData(string username)
    {
        List<string> result = new List<string>();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT pn.product_name, mn.model_name FROM ProductNumbers AS pn LEFT JOIN ModelNumbers AS mn ON pn.product_id = mn.product_id WHERE product_name LIKE '%'+@SearchText+'%'", conn))
            {
                conn.Open();
                cmd.Parameters.AddWithValue("@SearchText", username);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    //result.Add(dr["product_name"].ToString());
                    result.Add(dr["product_name"].ToString() + ":" + dr["model_name"].ToString());
                }

                return result;
            }
        }
    }

Open in new window

0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
You need to use the code in the article and not stay on your code
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
But they are not pulling data from DB. It appears they are getting there data from an .xml file. I still don't understand why that matters. I was able to get the value that I needed before, but it put the second value I needed on the autocomplete textbox when I need it to go on a Label Control.

I guess I will see if anyone else knows of another way using the code that I have now.

Thanks for your help leakim971. So close :(
0
 
LVL 10

Expert Comment

by:Monica P
Comment Utility
hi

Based on selected value in Autocomplete Textbox .. u want the related Items of "SelectedItem" to be displayed in the Label control

Like Selecting "Apple" in autocomplete option should show like " Red in colour" ,"Grow more in Kashmir" in label..

do u mean so?
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
@AkilaPalanimuthu,

Hi, thank you for helping with this post.

>> Based on selected value in Autocomplete Textbox .. u want the related Items of "SelectedItem" to be displayed in the Label control

That is correct. I have an AutoComplete TextBox that retrieves a value from my database (this part works). Now I need the related item of "SelectedItem" to be displayed in the Label Control.
0
 
LVL 10

Expert Comment

by:Monica P
Comment Utility
Now I need the related item of "SelectedItem" to be displayed in the Label Control.

For this ., do u need get the value from Database like below query for EG

select distinct Username from Table1 where Id='SelectedItem from Autocomplete'


if so., then on lost focus of that textbox .,u can use AJax  webmethod (which take value from DB) and get array of values for selected value..
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Hi AkilaPalanimuth,

Yes, I have the query listed below that I need to use to retrieve the related value from DB.
SELECT DISTINCT pn.product_name, mn.model_name FROM ProductNumbers AS pn LEFT JOIN ModelNumbers AS mn ON pn.product_id = mn.product_id WHERE product_name LIKE '%'+@SearchText+'%'

Open in new window


>> if so., then on lost focus of that textbox .,u can use AJax  webmethod (which take value from DB) and get array of values for selected value..

That is the part that I know nothing about. I have no idea what to use or how to code that part :(
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
@AkilaPalanimuth,

Are you still able to assist?
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
I've requested that this question be deleted for the following reason:

I need to delete this post. I did not get the help needed even after a request for attention. I'm going to repost this question in hope that someone else can help.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
What is the ID of the label?
0
 
LVL 82

Assisted Solution

by:leakim971
leakim971 earned 500 total points
Comment Utility
Assuming the ID of the label is : Label1
The following work for me :
    function SearchText() {
        $(".autosuggest").autocomplete({
            source: function (request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "create.aspx/GetAutoCompleteData",
                    data: JSON.stringify({ username: $('#txtProductNumber').val() }),
                    dataType: "json",
                    success: function (data) {
                        var wf = [];
                        for (var i = 0; i < data.d.length; i++) {
                            var json = {};
                            json.value = data.d[i].split(":")[0];
                            json.label = data.d[i].split(":")[1];
                            wf.push(json);
                        }
                        response(wf);
                    },
                    error: function (error) {}
                });
            },
            select: function (event, ui) {
                $("#<%= Label1.ClientID %>").text(ui.item.label);
            }
        });
    }

Open in new window

With :
    [WebMethod]
    public static List<string> GetAutoCompleteData(string username)
    {
        List<string> result = new List<string>();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT pn.product_name, mn.model_name FROM ProductNumbers AS pn LEFT JOIN ModelNumbers AS mn ON pn.product_id = mn.product_id WHERE product_name LIKE '%'+@SearchText+'%'", conn))
            {
                conn.Open();
                cmd.Parameters.AddWithValue("@SearchText", username);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    //result.Add(dr["product_name"].ToString());
                    result.Add(dr["product_name"].ToString() + ":" + dr["model_name"].ToString());
                }

                return result;
            }
        }
    }

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Hi leakim971,

That worked partially. When I start typing the value in txtProductNumber it displays the related item in the txtProductNumber TextBox Control. I need to display the product_name value as I type into the txtProductNumber TextBox control.

Then once the value has been selected in txtProductNumber TextBox control then display the model_name in the Label Control.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
replace Line 14, 15  by :
                          json.value = data.d[i].split(":")[1];
                            json.label = data.d[i].split(":")[0];

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Hi leakim971,

Sorry, not sure what I have to add / remove from the code/jquery below. Can you show me please?

jQuery:
    <script type="text/javascript">
        $(document).ready(function () {
            SearchText();
        });

        function SearchText() {
            $(".autosuggest").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        url: "create.aspx/GetAutoCompleteData",
                        data: "{'username':'" + document.getElementById('txtProductNumber').value + "'}",
                        dataType: "json",
                        success: function (data) {
                            response(data.d);
                        },

                        error: function (result) {
                            alert("Error");
                        }
                    });
                }
            });
        }
    </script>

Open in new window


WebMethod:
    [WebMethod]
    public static List<string> GetAutoCompleteData(string username)
    {
        List<string> result = new List<string>();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT pn.product_name, mn.model_name FROM ProductNumbers AS pn LEFT JOIN ModelNumbers AS mn ON pn.product_id = mn.product_id WHERE product_name LIKE '%'+@SearchText+'%'", conn))
            {
                conn.Open();
                cmd.Parameters.AddWithValue("@SearchText", username);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    result.Add(dr["product_name"].ToString() + ":" + dr["model_name"].ToString());
                }

                return result;
            }
        }
    }

Open in new window

0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
don't touch youe web method, take the javascript code snippet ID: 38831834
and change indice of the array (1,0) instead (0,1)
let me know if you don't understand what I mean about changing the array
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Ok, I did not touch the web method. But the jquery that you supplied works but with some issues (no errors).

I'm going to try to explain the best I can with data that I currently have in my Database. You are SO CLOSE to figureing this out and I'm sure this is something easy that you will understand that I don't.

ProductNumbers Table:
product_id      product_name
1                       AP789A
2                       491332-001
3                       470064-552
etc...

ModelNumbers Table:
model_id      product_id      model_name
50                  1                      Visual Studio 2008
51                  2                      Visual Studio 2010
52                  3                      Visual Studio 2012
etc...

Now when I start to type in the value "4" into the AutoComplete TextBox Control I see the value "Visual Studio 2008" instead of seeing the other possible values to choose such from such as "491332-001" and "470064-552".

Now if I were to select "Visual Studio 2008" then it displays the correct value for the AutoComplete TextBox and then assigns "Visual Studio 2008" to the label control.

So the things that need fixed are the following:
- retrieve all values from ProductNumbers Table based on the data entered.
- do not show the model_name value in the AutoComplete TextBox control. The model_name value should only be assigned to the label control once the user selects the correct value from the autoCompelte textbox control.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
check pictures attached
Clipboard02.jpg
Clipboard03.jpg
0
 
LVL 82

Accepted Solution

by:
leakim971 earned 500 total points
Comment Utility
    function SearchText() {
        $(".autosuggest").autocomplete({
            source: function (request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "create.aspx/GetAutoCompleteData",
                    data: JSON.stringify({ username: $('#txtProductNumber').val() }),
                    dataType: "json",
                    success: function (data) {
                        var wf = [];
                        for (var i = 0; i < data.d.length; i++) {
                            var json = {};
                            json.value = data.d[i].split(":")[1];
                            json.label = data.d[i].split(":")[0];
                            wf.push(json);
                        }
                        response(wf);
                    },
                    error: function (error) {
                    }
                });
            },
            select: function (event, ui) {
                event.preventDefault();
                $(".autosuggest").val(ui.item.label);
                $("#<%= Label1.ClientID %>").text(ui.item.value);
            }
        });
    }

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
THAT WORKED...

Thank you....

So happy and gratefull that you came back to finish helping me with the post. Thank you so much...
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
you're welcome
0
 
LVL 4

Author Closing Comment

by:asp_net2
Comment Utility
Thank you again for helping with this. Also, what should I be reading or looking at to learn this type of stuff jQuery with JSON, AutoComplete against database...
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
The rules are here : http://json.org/
Play with a debugger : http://ajaxian.com/archives/ajax-debugging-with-firebug
Depending the widget you're using, check left pane here : http://jqueryui.com/
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Scrolling the scrollbar in a list in html 9 22
Check my code out(2) 1 23
gif overlay 3 32
Achieve json result 2 20
PROBLEM: The other day I was working on adding an ajax request to a webpage that already had a dialog box on the page.  The dialog box was using relative positioning to be positioned next to a form field I had on the page.  Everything was working…
Introduction Chart.js, used properly, can visually add a difference to your charting applications. It engages your visitors and allows them to interact with data they otherwise wouldn't be able to without expensive and complicated systems. For this…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now