Object For Jquery Autocomplete

I have an Enrollments table that is mostly integer foreign key fields. One of these fields requires the end user to look up eligible names to enroll. What I would like to do using the jquery autocomplete widget is display rows showing name, gender and age e.g.(Jon Smith Male 10). I would then like to take the selected row's ID value and place it in a field to insert into a new enrollments record. The code I have come up with so far builds a sql statement that is straightforward except for using a sql server function that determines age before returning the list. The sql works fine. However, the code doesn't compile because I seem to be trying to convert an int to an object, or generic object[] to a parameter type object[]. Questions: What is the proper object[] type to accomplish fetching this data? Or what else do I need to correct?

 public JsonResult GetPlayer(string term)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("SELECT Id, FirstName + ' ' + LastName AS Player, Gender, dbo.ufn_GetAge(Birthdate, GETDATE()) AS Age");
        sb.Append(" FROM Individuals ");
        sb.Append("WHERE (dbo.ufn_GetAge(Birthdate,<wbr ></wbr> GETDATE()) < 19) AND (FirstName LIKE '%{0}%' OR LastName LIKE '%{0}%')");
        sb.Append(" ORDER BY LastName, FirstName");
        List<object[]> player = new List<object[]>();
        string ConnectionString = "Server=LocalServer;initia<wbr ></wbr>l catalog=LocalDatabase;inte<wbr ></wbr>grated security=True;MultipleActi<wbr ></wbr>veResultSe<wbr ></wbr>ts=True;Ap<wbr ></wbr>p=EntityFr<wbr ></wbr>amework";
        using (SqlConnection con = new SqlConnection(ConnectionSt<wbr ></wbr>ring))
        {
            string query = string.Format(sb.ToString(<wbr ></wbr>), term);
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
// Here an int argument is being asked for by complier. Do I need a different method here?
                    player.Add(reader.GetSqlValues();                 }
            }
            con.Close();
        }
        return Json(player, JsonRequestBehavior.AllowG<wbr ></wbr>et);
    }

Open in new window

wdarnellgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ambienceCommented:
Try this

columns = new Object[4] {};
reader.GetSqlValues(columns);    
player.Add(columns);

OR

player.Add(new Object[] {
      reader.GetValue(0),
      reader.GetValue(1,
      reader.GetValue(2),
      reader.GetValue(3)
});

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wdarnellgAuthor Commented:
Hi Ambience. and thank you. I added the code you suggest, but out of the blue, I am having javascript issues. For the moment, I will say that intellisense likes the code at least. :-) I will try to work through the js then let you know how it works asap. I just don't want to leave you hanging.
wdg
wdarnellgAuthor Commented:
Ok, I fixed the jquery problem. @Ambience, that object enumeration seems to be working, but I am not seeing the autocomplete menu, so the displaying the object list seems to need addressing. When I step through iterations the player object increments. I must be missing something in autocomplete function.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

ambienceCommented:
Without looking into the client side code its almost impossible to speculate what might be happening.

Are you able to see proper JSON being returned to the client? You may have to use developer tools like FireBug to have a close look. Please post the JS and JSON.
wdarnellgAuthor Commented:
in firebug I can see the data from the query on the client. I an trying to find how to tell auto complete to display it and store the selected ID value into the hidden field. It doesn't like me using the index property. It doesn't map that way.


<script type="text/javascript">
    $(function() {
        $("#txtPlayer").autocomplete({
                source: '@Url.Action("GetPlayer")',
                minLength: 2,
                select: function(event, ui) {
                    $("#$txtPlayer").val(ui.item.index(0));
                    $("#$txtPlayer-id").val(ui.item.index(1));
                    $("#$player-description").html(ui.item.index(2));
                    $("#$player-age").html(ui.item.index(3));
                    return false;
                }
            })
            .data("ui-autocomplete")._renderItem = function(ul, item) {
                return $("<li>")
                    .append("<a>" + item.index(0) + " " + item.index(1) + " Gender: " + item.index(2) + " Age:" + item.index(3) + "</a>")
                    .append(ul);
            };
    });
</script>

Open in new window

ambienceCommented:
Oh well, you forgot to paste the JSON. Also, since you are using Firebug, do you see any errors in the console tab?

Also, do you have a link for documentation for autocomplete? Its not obvious, which format it expects.
ambienceCommented:
My hunch: item.index(0) should be item[0] and so on
wdarnellgAuthor Commented:
Apologies. I thought you meant the JsonResult code above. The JSON in firebug is populated with each typed entry as well.
Firebug JSON after entering chars
 I am reading the api documentation from http://api.jqueryui.com/autocomplete/. It is a little over my head, but it is making some sense. I have the makings of a dropdown menu, but the data still isn't posting. I am not sure, but I think I may need to tell it to post somewhere in the code.

<script type="text/javascript">
    $(function() {
        $("#txtPlayer").autocomplete({
                source: '@Url.Action("GetPlayer")',
            minLength: 2,
            _renderMenu: function(ul, items) {
                var self = this;
                $.each(items, function(index, item) {
                    self._renderItemData(ul, item);
                });
            },
                select: function(event, ui) {
                    $("#$txtPlayer").val(ui.item.index[0]);
                    $("#$txtPlayer-id").val(ui.item.index[1]);
                    $("#$player-description").html(ui.item.index[2]);
                    $("#$player-age").html(ui.item.index[3]);
                    return false;
                }
            })
            .data("ui-autocomplete")._renderItem = function(ul, item) {
                return $("<li>")
                    .append("<a>" + item.index[0] + " " + item.index[1] + " Gender: " + item.index[2] + " Age:" + item.index[3] + "</a>")
                    .append(ul);
            };
    });
</script>

Open in new window

wdarnellgAuthor Commented:
Errors on the .data line:
TypeError item.index undefined.
When I remove the .data line, no errors, but data not posted... drop down visible.

DropDown panel visible
<script type="text/javascript">
    $(function() {
        $("#txtPlayer").autocomplete({
            source: '@Url.Action("GetPlayer")',
            minLength: 2,
            _renderMenu: function(ul, items) {
                var self = this;
                $.each(items, function(index, item) {
                    self._renderItemData(ul, item);
                });
            },
            select: function(event, ui) {
                $("#$txtPlayer").val(ui.item.index[0]);
                $("#$txtPlayer-id").val(ui.item.index[1]);
                $("#$player-description").html(ui.item.index[2]);
                $("#$player-age").html(ui.item.index[3]);
                return false;
            }
        });
        //.data("ui-autocomplete")._renderItem = function(ul, item) {
        //    return $("<li>")
        //        .append("<a>" + item[0] + " " + item[1] + " Gender: " + item[2] + " Age:" + item[3] + "</a>")
        //        .append(ul);
        //};
    });
</script>

Open in new window

<script type="text/javascript">
    $(function() {
        $("#txtPlayer").autocomplete({
                source: '@Url.Action("GetPlayer")',
            minLength: 2,
            _renderMenu: function(ul, items) {
                var self = this;
                $.each(items, function(index, item) {
                    self._renderItemData(ul, item);
                });
            },
                select: function(event, ui) {
                    $("#$txtPlayer").val(ui.item.index[0]);
                    $("#$txtPlayer-id").val(ui.item.index[1]);
                    $("#$player-description").html(ui.item.index[2]);
                    $("#$player-age").html(ui.item.index[3]);
                    return false;
                }
            })
            .data("ui-autocomplete")._renderItem = function(ul, item) {
                return $("<li>")
                    .append("<a>" + item.index[0] + " " + item.index[1] + " Gender: " + item.index[2] + " Age:" + item.index[3] + "</a>")
                    .append(ul);
            };
    });
</script>

Open in new window

ambienceCommented:
I can still see item.index[1]

you have to change all these occurances
wdarnellgAuthor Commented:
You are right. So I tried this modification and simply get the same result. No error, but no data displayed. Still see the object in json on firebug.

<script type="text/javascript">
    $(function() {
        $("#txtPlayer").autocomplete({
            source: '@Url.Action("GetPlayer")',
            minLength: 2,
          
            _renderItem: function(ul, item) {
                return $("<li>")
                    .attr("data-value", item.value)
                    .append(item.label)
                    .appendTo(ul);
            
            },
            _renderMenu: function (ul, items) {
                var self = this;
                $.each(items, function (index, item) {
                    self._renderItemData(ul, item);
                });
            },
            data: function (data) {
                response($.map(data, function (item) {
                    return {
                        value: item[0],
                        label: item[1, 2, 3]
                    };
                }));
            },
            select: function(event, ui) {
                $("#$txtPlayer").val(ui.item[1,2,3]);
                $("#$txtPlayer-id").val(ui.item[0]);
                return false;
            }
        });
    });
</script>

Open in new window

wdarnellgAuthor Commented:
Ok, a little bit of progress...
I tried getting data with an ajax call. The list populates, but it is all a single record. The json in firebug shows that all of the appropriate records are being fetched, but the display is not showing them.

<script type="text/javascript">
    $(function() {
        $("#txtPlayer").autocomplete({
            source: function(request, response) {
                $.ajax({
                    url: '@Url.Action("GetPlayer")',
                    datyType: "json",
                    data: {
                        term: request.term
                    },
                    success: function(data) {
                        var itemArray = new Array();
                        for (p = 0; p < data.length; p++) {
                            itemArray[p] = {label: data[1,2,3], value: data[0], data: data[p] }
                        }
                        console.log(itemArray);
                        response(itemArray);
                    },
                    error: function(data, type) {
                        console.log(type);
                    }
                });
            },
        minLength: 2,

    select: function(event, ui) {
        $("#$txtPlayer").val(ui.item[1,2,3]);
        $("#$txtPlayer-id").val(ui.item[0]);
        return false;
    }
    });
    });
</script>

Open in new window


List represents correct record count but duplicates a single record.
A new object list shows
ambienceCommented:
I'm not sure what you are trying here:

ui.item[1,2,3]

It should be ui.item[1].

If you are trying to concatenate then use ui.item[1] + ui.item[2] etc. Same goes for data[1,2,3]. Actually, the whole loop looks suspiciously wrong

                        for (p = 0; p < data.length; p++) {
                            itemArray[p] = {label: data[1,2,3], value: data[0], data: data[p] }
                        }

Shouldnt it be?

                        for (p = 0; p < data.length; p++) {
                            itemArray[p] = {label: data[1], value: data[p], data: data[p] }
                        }
wdarnellgAuthor Commented:
You were almost perfect. The loop was wrong, but
for (p = 0; p < data.length; p++) {
                             itemArray[p] = {label: data[1], value: data[p], data: data[p] }

didn't change anything. Still got single record multiple times. However, following your lead, I discovered that

for (p = 0; p < data.length; p++) {
                            itemArray[p] = {label: data[p], value: data[1], data: data[p] }

does work. As you can see below, there are multiple records.
Displays proper list
Now to solve why when a choice is selected, I get an empty textbox with no error message.
wdarnellgAuthor Commented:
@Ambience
You were right about concatenation, except I have to use itemArray or at least that is working. I am able to show the name, gender and age without showing the ID. I still don't have it displaying in the txtPlayer textbox.

                <div class="input-group">
                   <label for="txtPlayer">Player</label>

                    <input type="text" id="txtPlayer" class="input-group"/>
                    <input type="hidden" id="txtPlayer-id" class="input-group"/>
                </div>

Open in new window


<script type="text/javascript">
    $(function() {
        $("#txtPlayer").autocomplete({
            source: function(request, response) {
                $.ajax({
                    url: '@Url.Action("GetPlayer")',
                    type: "POST",
                    datyType: "json",
                    data: {
                        term: request.term
                    },
                    success: function(data) {
                        var itemArray = new Array();
                        for (p = 0; p < data.length; p++) {
                            itemArray[p] = {label: data[p], value: data[1], data: data[p] }
                        }
                        console.log(itemArray);
                        response($.map(data, function(itemArray) {
                            return {
                                value: itemArray[0],
                                label: itemArray[1] + ", " + itemArray[2] + ", Age " + itemArray[3]
                            }
                        }));
                    },
                    error: function(data, type) {
                        console.log(type);
                    }
                });
            },
        minLength: 2,

    select: function(event, ui) {
        $("#txtPlayer").val(ui.item[p]);
        $("#txtPlayer-id").val(ui.item[p]);
        return false;
    }
    });
    });
</script>

Open in new window


Show name, gender, and age with hidden Id.
                    success: function(data) {
                        var itemArray = new Array();
                        for (p = 0; p < data.length; p++) {
                            itemArray[p] = {label: data[p], value: data[1], data: data[p] }
                        }
                        console.log(itemArray);
                        response($.map(data, function(itemArray) {
                            return {
                                value: itemArray[0],
                                label: itemArray[1] + ", " + itemArray[2] + ", Age " + itemArray[3]
                            }
                        }));
                    },

Open in new window

wdarnellgAuthor Commented:
Ok, We have pieced an autocomplete that works. I had to modify the select function to get the data from the label and value. Now the textbox displays the selected object.

    select: function(event, ui) {
        $("#txtPlayer").val(ui.item.label);
        $("#txtPlayer-id").val(ui.item.value);
        return false;
    }
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
jQuery

From novice to tech pro — start learning today.