0

I'm using a Web Service page to make a textbox an "auto complete" control. In order to do that, I'm using a web service.

My code in the web service looks like this:

    public string[] ISGetCompletionList(string prefixText)
    {
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["CLTDPL"].ConnectionString;
        SqlConnection conn = new SqlConnection(connectionString);

        List<string> Payers = new List<string>();
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT [PAYER_ID], [PAYER_TYPE] FROM [mos_Payer] WHERE " +
            "PAYER_TYPE like '%' + @SearchText + '%' ORDER BY PAYER_TYPE ASC";
            cmd.Parameters.AddWithValue("@SearchText", prefixText);
            cmd.Connection = conn;
            conn.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    Payers.Add(string.Format("{0}|{1}", sdr["PAYER_TYPE"], sdr["PAYER_ID"]));
                }
            }
            conn.Close();
        }
        return Payers.ToArray();
    }

Everything works perfectly. However, now I need to filter the CommandText by one of the fields. So, I know I need to change the cmd.CommandText line to something like:

cmd.CommandText = "SELECT [PAYER_ID], [PAYER_TYPE] FROM [mos_Payer] WHERE " +
"PAYER_TYPE like '%' + @SearchText + '%' and PAYER_DATE = " + MyForm.PayerDate + 
" ORDER BY PAYER_TYPE ASC";

I'm trying to figure out how I'd reference that field on my aspx page.

The javascript that sends all that info over looks like this:

    function SetAutoComplete() {
        $("#<%=txtPayer.ClientID %>").autocomplete({
            source: function (request, response) {
                $.ajax({
                    url: '<%=ResolveUrl("~/Autocomplete.asmx/ISGetCompletionList") %>',
                     data: "{ 'prefixText': '" + request.term + "'}",
                     dataType: "json",
                     type: "POST",
                     contentType: "application/json; charset=utf-8",
                     success: function (data) {
                         response($.map(data.d, function (item) {
                             return {
                                 label: item.split('|')[0],
                                 val: item.split('|')[1]
                             }
                         }))
                     },
                     error: function (response) {
                         alert(response.responseText);
                     },
                     failure: function (response) {
                         alert(response.responseText);
                     }
                 });
             },
            select: function (e, i) {
                $("#<%=hfPayer.ClientID %>").val(i.item.val);
             },
             minLength: 1
        });
   };
Bharata
  • 13,509
  • 6
  • 36
  • 50
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • you mean the `MyForm.PayerDate`? I'd suggest to read the value on client-side in the `SetAutoComplete` javascript function, and pass it as an additional argument in the 'data' attribute, the same one where you also set the `prefixText`. – Cee McSharpface Jan 14 '18 at 20:27
  • So how would I add 2 values to that "data:" parameter? Javascript is my weak link, it was sort of a miracle I got the autocomplete to work. There's another textbox on that form called txtDate. – Johnny Bones Jan 14 '18 at 20:43
  • 1
    obtain the value: `var payerdate = $("#<%= PayerDate.ClientID %>").val();`. then pass it on: `data: "{ 'prefixText': '" + request.term + "', 'payerDate': payerdate }"`. I suppose it is a date? then use a string to transport the raw value, and `DateTime.TryParse` it serverside. Use a command parameter for the @payerdate just like you did with the @SearchText. – Cee McSharpface Jan 14 '18 at 21:02
  • I slid the "var" statement in as the line just below the "function SetAutoComplete() {" line, and I was getting an "Invalid JSON primitive: payerdate" error. I edited the data line slightly to data: "{ 'prefixText': '" + request.term + "', 'payerDate': '" + payerdate + "' }" and it worked, but on the backend when I put a code break in it has a value for prefixText but none for payerDate. Any ideas what I'm doing wrong? – Johnny Bones Jan 15 '18 at 01:33
  • see [this thread](https://stackoverflow.com/q/5048026/1132334) – Cee McSharpface Jan 15 '18 at 11:52

1 Answers1

1

Can't remember if you can do this, but try adding another param to your method.

public string[] ISGetCompletionList(string prefixText, string payerDate)

then in the ajax call

data: "{ 'prefixText': '" + request.term + "', 'payerDate': '" + formDate + "' }", etc.

or,

add the info to prefixText in the ajax call, similar to how the webmethod is combining data:

data: "{ 'prefixText': '" + request.term + "|" + formDate + "' }", etc

then split it in code behind.

wazz
  • 4,953
  • 5
  • 20
  • 34