Passing a variable from Javascript into C# for use in SQL SELECT, then passing result back to Javascript

Question

I am trying to run some code in my Code Behind to query my SQL Server (using a JavaScript variable) and then pass the result as an Integer back to my Javascript.

I have attempted to do this, by initially running some Javascript to get a variable for the SQL SELECT statement, then passing this to a hidden ASP field (currently a textbox for test purposes).

However everytime I run the code, refreshHTML(), the hidden field (textbox) value is blank and it returns the value 999999999.

When I run the application, I can see the textbox is populated with the correct value.

Here is my Code Behind in C#

    public int ucount
    {
    get
    {
        if (String.IsNullOrEmpty(invoicenumberhidden.Text))
        {
            return 999999999;
        }

        else { 

        int invoicenumber = int.Parse(invoicenumberhidden.Text);

        string commandText = "SELECT COUNT(*) AS distinct_count FROM (SELECT DISTINCT [inv_section],[invoice_number], [cust_po] FROM [Indigo].[dbo].[invoice_items] WHERE invoice_number=" + invoicenumber + ") AS I;";
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Indigo2.Properties.Settings.Constr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(commandText, conn))
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                int uniquecount = (Int32)cmd.ExecuteScalar();
                conn.Close();
                return uniquecount;
            }
        }          
    }
}

Here is my JavaScript which is triggered on an ASP OnClientClick event function refreshHtml()

        //Get Primary Key from Grid1
        var grid = jQuery("#<%= JQGrid1.ClientID %>");
        var rowKey = grid.getGridParam("selrow");

        document.getElementById('<%= invoicenumberhidden.ClientID %>').value = rowKey;

        var jsucount = '<%=this.ucount%>';
        alert(jsucount);

This is my relevant ASP code

<div hidden> <asp:Button ID="btnDownload" runat="server" OnClientClick="refreshHtml();" OnClick="btnDownloadButton_Click" Text="Export PDF"></asp:Button></div>
<asp:TextBox ID="invoicenumberhidden" runat="server"></asp:TextBox>

Answer 1

Replace var jsucount = '<%=this.ucount%>'; with var jsucount = document.getElementById('<%= invoicenumberhidden.ClientID %>').value;.

You are facing this issue because in your html page you'll be having var jsucount = 999999999. Getting 999999999 value here because .net will set '<%=this.ucount%>' value when it send page to your browser. It will not reflect any further change carried out at server.

Edit 2:

As you want to do server trip for getting latest ucount value. You may try PageMethods for it.

You can move ucount logic to method like GetUCount and give [WebMethod] annotation. But you can not use controls here so you need to pass value of hidden field in parameter. And also need to make some change in javascript as described below.

Code behind:

[WebMethod]
public int GetUCount(string invoicenumberhidden)
{       
    if (String.IsNullOrEmpty(invoicenumberhidden))
    {
        return 999999999;
    }

    else 
    { 

        int invoicenumber = int.Parse(invoicenumberhidden);

        string commandText = "SELECT COUNT(*) AS distinct_count FROM (SELECT DISTINCT [inv_section],[invoice_number], [cust_po] FROM [Indigo].[dbo].[invoice_items] WHERE invoice_number=" + invoicenumber + ") AS I;";
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Indigo2.Properties.Settings.Constr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(commandText, conn))
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                int uniquecount = (Int32)cmd.ExecuteScalar();
                conn.Close();
                return uniquecount;
            }
    }               
}

Javascript:

function refreshHtml() {
    //Get Primary Key from Grid1
    var grid = jQuery("#<%= JQGrid1.ClientID %>");
    var rowKey = grid.getGridParam("selrow");

    document.getElementById('<%= invoicenumberhidden.ClientID %>').value = rowKey;

    PageMethods.GetUCount(rowKey, onSuccess, onFailure);        
}

function onSuccess(result) {
    alert(result);
}

function onFailure(result) {
    alert("Failed!");
}