• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

jQuery AutoComplete retrieve related value

Hello Experts,

I have the following code below. The code searches my table in my database as you type into the AutoComplete TextBox (this part works fine). But, I need to retrieve the value that I'm typing in initialy into the AutoComplete TextBox by itself and then have the related value that is associated in the AutoCompelte TextBox to be displayed in a Label Control. As of now both values appear in the same AutoComplete TextBox.

So to recap, I need the value "model_name" to returned to a Label Control and not on the same TextBox control as the other value "product_name".

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());
                    result.Add(dr["product_name"].ToString() + ":" + dr["model_name"].ToString());
                }

                return result;
            }
        }
    }

Open in new window


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


Thank you in advance!!!
0
asp_net2
Asked:
asp_net2
  • 18
  • 14
1 Solution
 
KyleSWCommented:
I'm not 100% sure what you're trying to do, is it something like this?

http://jsfiddle.net/EdKcQ/
0
 
asp_net2Author Commented:
Hi KyleSW,

Thank you for choosing to assist with this issue. I will try to explain without overcomplicating which I tend to do sometimes :)

Step 1: User types into TextBox control and it fetches value from Table 1 based on what they type. So if I start typing in 43 it will retrieve all values starting with the first two characters (this part works fine).

Step 2: I need to retrieve the related value from my database based on the value from Step 1. So if I select 43345 from Step 1 then I need to place the value from Table 2 which would be two_name to a label control or another TextBox Control.

Below is sample data from Table 1 and Table 2. You can see the relation.

Table 1:
t_id     t_name
1         43345
2         32200
3         88899
4         23332
etc...

Table 2:
two_id      t_id      two_name
100           1          Product Name One
101           2          Product Name Two
102           3          Product Name Three
103           4          Product Name Four
etc...
0
 
asp_net2Author Commented:
Hi KyleSW,

Yes, your example is how I need it to work but with a Database instead. But the concept is correct.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
KyleSWCommented:
The concept is actually exactly the same, regardless of where your data comes from. It will be easier if you change the output from the serverside, C# ain't really my thing, but I think it would look something like (assuming \ is an escape character:

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

                return result;
            }

Open in new window


Then your JQuery would look something like:
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");
                }
            });
        },
        response: function (event, ui) {
            var s = $("#test").html('');
            $.each(ui.content, function (index, value) {
                s.append(value.value + '<br />');
            });
        }
    });

Open in new window

0
 
asp_net2Author Commented:
Ok, I added exactly what you supplied and now it's not even retrieving data from the first AutoComlpete TextBox :(
0
 
KyleSWCommented:
do you get any errors?
0
 
asp_net2Author Commented:
I found why the first part did not work. Please see my updated code below based on what you supplied. Also, note the output that it's displaying back. It's stil putting all data into the initial TextBox control but with other formatting applied.

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");
                        }
                    });
                },
                response: function (event, ui) {
                    var s = $("#test").html('');
                    $.each(ui.content, function (index, value) {
                        s.append(value.value + '<br />');
                    });
                }
            });
        }
    </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());
                    //result.Add(dr["product_name"].ToString() + ":" + dr["model_name"].ToString());
                    result.Add("\"label\":" + dr["product_name"].ToString() + ",\"value\":" + dr["model_name"].ToString());
                }

                return result;
            }
        }
    }

Open in new window

0
 
asp_net2Author Commented:
Sorry, forgot to attache the formating of data from above.

"label":491332-001,"value":Product 350

491322-001 is the value from Table 1
Product 350 is related to the value above in the database
0
 
KyleSWCommented:
Is this: http://jsfiddle.net/EdKcQ/10/

Not what you want then? I'm not sure what you mean by "It's stil putting all data into the initial TextBox control", could you clarify exactly what it's putting where and what you don't want it to.
0
 
KyleSWCommented:
You need to wrap quotation marks around: Product 350 and 491332-001.

It should return something that looks like the below:
[{"label":"Product 350","value":"491332-001"},{"label":"Product 351","value":"491332-005"}]

Open in new window


Paste your json output here: http://jsonlint.com/ it will tell you if it is valid
0
 
asp_net2Author Commented:
Hi KyleSW,

>> Is this: http://jsfiddle.net/EdKcQ/10/
Yes, but instead of the value blue going into the TextBox it needs to be placed below the TextBox into a Label control.

So the outcome of your example should look like this below. So Corsa will stay in the TextBox control and the value blue will assigned to the label control.

Search Textbox: Corsa
Label Control: blue
0
 
KyleSWCommented:
So why do you need autocomplete? :confused:
0
 
asp_net2Author Commented:
Below is what you have now for the WebMethod C#. Do I need to modify that? Also, how do I assign "model_name" to the Label Control? The name of the Label control is called lblModelNumber. I'm also using the TextBox and Label controls in ASP.NET.

Code:
result.Add("\"label\":" + dr["product_name"].ToString() + ",\"value\":" + dr["model_name"].ToString());
0
 
asp_net2Author Commented:
>> So why do you need autocomplete? :confused:

I'm using autocomplete to retrieve the first value from my database.

Then, I need to retrieve the related value from the autocomplete TextBox to a label control (ASP.NET Label Control).

So, if I type in 491332-001 it's retrieving that value from my database. Then I have an SQL relation that retrieves the related value of 491332-001 and that value needs to be displayed in a Label Control below.
0
 
asp_net2Author Commented:
PLEASE NOTE:

The following code below works fine for retrieving the values from the database that I need. The problem with the solution below is that I need to get the returned value dr["model_name"].ToString()); to an ASP.NET Label control called lblModelNumber instead of both of those values in the txtProductNumber TextBox Control.

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


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

0
 
KyleSWCommented:
Ok, got it, so you want this: http://jsfiddle.net/EdKcQ/18/

To get that, you need to output this string from your C# code:
[{"label":"product_name", "value":"product_name", "model":"model_name"},{"label":"product_name", "value":"product_name", "model":"model_name"},{"label":"product_name", "value":"product_name", "model":"model_name"}]

Open in new window


So I think the C# would be:
result.Add("\"label\":\"" + dr["product_name"].ToString() + "\",\"value\":\"" +dr["product_name"].ToString() + "\",\"model\":\"" + dr["model_name"].ToString() + "\"");

Open in new window


However, are you only wanting the label to update once the user has made a selection? If so, it's less complex: http://jsfiddle.net/EdKcQ/21/
0
 
asp_net2Author Commented:
>>However, are you only wanting the label to update once the user has made a selection? If so, it's less complex: http://jsfiddle.net/EdKcQ/21/ 

Yes, I only need the label to update once the user has made a selection.

Sorry, but I'm confused as to how to modify my existing jQuery below or what I have to add to it :(

Once the jQuery is working right then I will add the C# string to test.

Once again to make sure we are on the same page I'm going to repost my jQuery code.

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

0
 
KyleSWCommented:
Your jQuery won't do what you want until you fix the C# code, once that's done, your jquery will look like this:
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);
                },
                error: function (result) {
                    alert("Error");
                }
            });
        },
    },
    select: function (event, ui) {
        $("#lblModelNumber").html(ui.item.model);
    }
    });
}

Open in new window


The reason the code won't do what you want is down to the properties the autocomplete widget needs, it needs a label property for the dropdown, a value property for the textbox selection and you can add custom properties after that, but it needs those as a minimum, so your json needs to output:
[
    {
        "label": "product_name",
        "value": "product_name",
        "model": "model_name"
    },
    {
        "label": "product_name",
        "value": "product_name",
        "model": "model_name"
    },
    {
        "label": "product_name",
        "value": "product_name",
        "model": "model_name"
    }
]

Open in new window


The select handler then takes the selected object and puts it in the label control:
    select: function (event, ui) {
        $("#lblModelNumber").html(ui.item.model);
    }

Open in new window

0
 
asp_net2Author Commented:
I'm getting syntax errors, expecited identifier, and expected ')' messages with the jQuery code you supplied.
0
 
asp_net2Author Commented:
Ok, i just modified the jquery and C# code below but I get the same value in the TextBox Control. No other values are getting added to the Label Control and it's adding the following output below.

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");
                        }
                    });
                },
                select: function (event, ui) {
                    $("#lblModelValue").html(ui.item.model);
                }

            });
        }
    </script>

Open in new window


C#:
    [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("\"label\":\"" + dr["product_name"].ToString() + "\",\"value\":\"" + dr["product_name"].ToString() + "\",\"model\":\"" + dr["model_name"].ToString() + "\"");
                }

                return result;
            }
        }
    }

Open in new window

    [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());
                    result.Add("\"label\":\"" + dr["product_name"].ToString() + "\",\"value\":\"" + dr["product_name"].ToString() + "\",\"model\":\"" + dr["model_name"].ToString() + "\"");
                }

                return result;
            }
        }
    }

Open in new window

0
 
KyleSWCommented:
Are you using webforms? If so have you checked the name of the label after the page has rendered. I thought webforms renamed everything?
0
 
asp_net2Author Commented:
Yes, I'm using webforms.
0
 
KyleSWCommented:
And what's the rendered control name, have you updated the hairy match the rendered label id?
0
 
asp_net2Author Commented:
Please see the results below.

The label control doed not change it's name when the page is rendered.

The value that I see in the AutoComplete TextBox looks as follows.

"label":"491332-001","value":"491332-001","model":"DL 360"

I should see the following results below.

If I select the value from the AutoComplete TextBox  (Product Number TextBox) then I should see the following below.

Product Number:
491332-001

Label Control:
DL 360
0
 
asp_net2Author Commented:
KyleSW,

Ok, take a look below. I modified the following below and now when I select a value from the AutoComplete TextBox control it adds the same thing to the Label Control. So now it appears that we need to remove the formating from the AutoComplete TextBox and the formatting from the Label Control.

Update jQuery:
        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");
                        }
                    });
                },
                select: function (event, ui) {
                    //$("#lblModelValue").html(ui.item.model);
                    $("#<%= lblModelValue.ClientID %>").text(ui.item.label);
                }
            });
        }

Open in new window


See what I get now below and how I need it to look.

What I get back now:

Product Number:
"label":"491332-001","value":"491332-001","model":"DL 360"

Label Control:
"label":"491332-001","value":"491332-001","model":"DL 360"

What I need it to look like:

Product Number:
491332-001

Label Control:
DL 360
0
 
KyleSWCommented:
Yes, I understand what should happen, but im still not convinced that your c# code is correct. Can you paste the actual output of the page when it returns more than one result. By that, I mean the actual json returned from your c# code rather than what is put in the textbox.
0
 
asp_net2Author Commented:
I'm not able to paste the actual output when it returns more than one result. the values disappear if I right click to view source.

I don't believe the C# is wrong. I believe the issue may have to do with the line of code you had me apply below.

result.Add("\"label\":\"" + dr["product_name"].ToString() + "\",\"value\":\"" + dr["product_name"].ToString() + "\",\"model\":\"" + dr["model_name"].ToString() + "\"");
0
 
asp_net2Author Commented:
Ok, I was able to get help with this post and found the following below. The following code below works as needed.

    <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: 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);
                    $("#<%= lblModelValue.ClientID %>").text(ui.item.value);
            }
        });
    }
    </script>

Open in new window


    [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
 
KyleSWCommented:
Ajax value aren't in the source, to view them in chrome, open your page, hit f12 then start typing in the textbox. You'll see the requests in the network tab, you'll get a new line for each request. If you click on one of these, it will show the request data. Click on the response tab and paste the data.

My code is right since it works in the example, the only part I can't check is the data you're sending from the server, so it's a process of elimination.
0
 
KyleSWCommented:
Just read your comment above and if the code works as you suggest, it is indeed the format of the data you are sending from the server. .net appears to be doing something odd when it returns the json, it appears to be wrapping it in another object called 'd'. Glad you got it sorted though :)
0
 
asp_net2Author Commented:
Can you copy/paste the code that I said worked so I can credit you for your time and so I know for future reference.
0
 
KyleSWCommented:
Sure :)

    <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: 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);
                    $("#<%= lblModelValue.ClientID %>").text(ui.item.value);
            }
        });
    }
    </script>

Open in new window

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 18
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now