jQuery Autocomplete and ASP.NET

Question

Friend sent me a spreadsheet that they downloaded and have been using for a while, now anytime they click on a cell they get:
Run-time error '1004': Method 'OLEObjects' of object'_Worksheet' failed.

I am getting the same error. I have absolutely zero knowledge of visual basic, so I figured while I try and figure out what is going on in the code I would post my error on here. Any help is appreciated. Thankyou.

error debugger highlights the line Set cboTemp = ws.OLEObjects("TempCombo")

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Set cboTemp = ws.OLEObjects("TempCombo")
    On Error GoTo errHandler

    If Target.Column <> 3 Then
        If cboTemp.Visible = True Then
            With cboTemp
                .Visible = False
                .Top = 10
                .Left = 10
                '.ListFillRange = ""
                .LinkedCell = ""
                TempCombo.Value = ""
                TempCombo.Clear
            End With
        End If
    End If

    On Error GoTo errHandler
    If Target.Column = 3 Then
        With cboTemp
            'show the combobox with the list
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width
            .Height = Target.Height + 5
            '.ListFillRange = ws.Range("pickdata").Address 
            'ws.Range("AB2", ws.Range("AB2").End(xlDown).Address).Address 
            ' ws.Range("AB2").End(xlDown).Address 
            ' ws.Range("AB2", "ab300").Address 
            ' ws.Range(str).Address
            .LinkedCell = Target.Address
            .Visible = True
        End With
        cboTemp.Activate
        TempCombo_Change 'force the combobox to fill
    End If

exitHandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
errHandler:
    MsgBox Err.Number & Err.Description
    GoTo exitHandler '   Application.EnableEvents = True
    Exit Sub
End Sub


Private Sub TempCombo_Change()
Dim cboTemp As OLEObject
Dim s As String, i As Long
Dim rng As Range, cell As Range, j As Long, fullmatch As Boolean
Dim ar() As String
If TempCombo.ListIndex >= 0 Then 'we have a selection

Else
    'TempCombo.DropDown

    If pickdata Is Nothing Then
        SetPickData
    Else
        If LCase(Left(ActiveCell.Offset(0, 1).Value, 9)) = "cleanable" And activedbclean = False Then
            SetPickData
        Else
            If LCase(Left(ActiveCell.Offset(0, 1).Value, 9)) <> "cleanable"  And activedbclean = True Then
                SetPickData
            End If
        End If
    End If
    Set rng = pickdata
    'Set cboTemp = ActiveSheet.OLEObjects("TempCombo")
    s = TempCombo.Value
    TempCombo.Clear
    i = 0
    For Each cell In rng
        ar = Split(s, " ", -1, vbBinaryCompare)
        j = 0
        fullmatch = True
        Do While j <= UBound(ar) And fullmatch = True
            If InStr(1, LCase(cell.Value), LCase(ar(j)), vbTextCompare) <= 0     Then
                fullmatch = False
            End If
            j = j + 1
        Loop
        If fullmatch = True Then 'InStr(1, LCase(cell.Value), LCase(s), vbTextCompare) > 0 Then
        'If InStr(1, LCase(cell.Value), LCase(s), vbTextCompare) > 0 Then
            TempCombo.AddItem cell.Value
            'If s = "" Then
                i = i + 1
            'End If
        End If
        If i > 50 Then
            Exit For
        End If
    Next cell
    TempCombo.Enabled = False
    TempCombo.Enabled = True
    TempCombo.DropDown
End If
End Sub

Solution 1

I just recently implemented autocomplete, and it looks fairly similar. I'm using an ashx (Generic Handler) instead of the aspx, but it's basically the same code in the code behind.

Using the ashx, it'll look something like this:

<script type="text/javascript">
  $(document).ready(function(){
      $("#txtSearch").autocomplete('autocompletetagdata.ashx');
  });      
</script>

[WebService(Namespace = "http://www.yoursite.com/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class AutocompleteTagData : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        // Note the query strings passed by jquery autocomplete:
        //QueryString: {q=a&limit=150&timestamp=1227198175320}

        LookupTagCollection tags = Select.AllColumnsFrom<LookupTag>()
            .Top(context.Request.QueryString["limit"])
            .Where(LookupTag.Columns.TagDescription).Like(context.Request.QueryString["q"] + "%")
            .OrderAsc(LookupTag.Columns.TagDescription)
            .ExecuteAsCollection<LookupTagCollection>();

        foreach (LookupTag tag in tags)
        {
            context.Response.Write(tag.TagDescription + Environment.NewLine);
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Solution 2

I just posted this on another question, but you can override the parse function on the jQuery autocomplete plug-in to make it support any output.

Example:

    $("#<%= TextBox1.ClientID %>").autocomplete("/Demo/WebSvc.asmx/SuggestCustomers", {
        parse: function(data) {
            var parsed = [];

            $(data).find("string").each(function() {
                parsed[parsed.length] = {
                    data: [$(this).text()],
                    value: $(this).text(),
                    result: [$(this).text()]
                };
            });
            return parsed;
        },
        dataType: "xml"
    });

All this expects is a string array to XML... Very easy to do... If your using SubSonic, you should check out the RESTHandler (It's a hidden GEM!!!), it supports basic queries on all your objects and can return JSON/XML. Here is an example query using it...

/Demo/services/Customers/list.xml?CustomerName=JOHN

If you change list.xml to list.json it will change the results to JSON. The request above will return the a strongly typed "Customer" entity. You can change the parameter to support LIKE, NOT LIKE, etc... Very powerful and all the plumbing is arleady done...

Here is a video on it: http://subsonicproject.com/tips-and-tricks/webcast-using-subsonic-s-rest-handler/

Solution 3

The web service or a WCF service will give you the potential for a better interface. Both can also be set up to do Json serialization.

Since I'm taking a WCF class as I write (I'm on a break, really!), I'll sketch the WCF method.

[OperationContract]
[WebInvoke(RequestFormat=WebMessageFormat.Json,
           ResponseFormat=WebMessageFormat.Json)]
public LookupTagCollection LookupTags( int limit, string q )
{
     return Select.AllColumnsFrom<LookupTag>()
                  .Top(limit)
                  .Where(LookupTag.Columns.TagDescription)
                  .Like(q+ "%")
                  .OrderAs(LookupTag.Columns.TagDescription)
                  .ExecuteAsCollection<LookupTagCollection>();    
}

LookupTagCollection needs to be Serializable.

Solution 4

Jquery 1.8 Autocomplete uses "term" not "q" as the querystring param. this is the short and sweet version that I implemented. Hope this helps someone.

Javascript:

$(function () {
    $("#autocomplete").autocomplete({
        source: "/pathtohandler/handler.ashx",
        minLength: 1,
        select: function (event, ui) {
            $(this).val(ui.item.value);
        }
    });
});

ASHX Handler:

public class SearchHandler : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        var term = context.Request.QueryString["term"].ToString();

        context.Response.Clear();
        context.Response.ContentType = "application/json";

        var search = //TODO implement select logic based on the term above

        JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
        string json = jsSerializer.Serialize(search);
        context.Response.Write(json);
        context.Response.End();
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

View more solutions

Comments

[Alexander Bell]

Please provide the business logic for this code snippet: what exactly you expect it to do (btw, where is the Sub TempCombo_Change)? Thanks and regards,

[tea_pea]

Perhaps the active sheet is incorrect? Is there a choice to select a different sheet before setting the macro running?

[Jacob Baird]

I have tried on all of the sheets, and I am just trying to change the data inside the cell currently, other than that. This is a file with a sheet for every room in a house saving prices of stuff in the house quantities and what not. I am unsure of what the macros are doing. I added the sub TempCombo_Change() into the code (its at the bottom i seperated it with 4 new lines). Sorry that i am of little help.

[Jacob Baird]

From what I am told the only thing in the sheet that should be edited is the individual cell the user clicks on. The second you click on a cell, you get the error.

[bonCodigo]

Is there a tempCombo control in this workbook of yours?

[Jacob Baird]

I am unsure, I found out that the person I was trying to fix this for didnt need any of the background stuff going on, they just wanted the template, so i copy pasted it all into a new excel sheet and it is working fine. Thank you all for the help.

[bdukes]

Unfortunately, the Autocomplete widget doesn't expect JSON, it just wants text, each item on a new line. But for JSON stuff that looks nice. What URL would you use to access that?

[eduncan911]

This is the actual answer to handle XML with a name/value pairing. The marked answer above is only for the display text - you can't set/get a value pairing.

[atconway]

I think the new jQuery AutoComplete uses the property "term" instead of "limit", usless this code is based off of a different plug-in. From jQuery API docs: "A request object, with a single property called "term", which refers to the value currently in the text input. For example, when the user entered "new yo" in a city field, the Autocomplete term will equal "new yo". jqueryui.com/demos/autocomplete/#remote

[bdukes]

limit is the maximum number of results to return. However, it might send term instead of q, which this example uses...