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>

Have you looked in the Network tab of your browser's developer tools? What data is travelling back and forth? Is the SQL statement itself returning as expected? — IronFlare
– IronFlare, Commented Aug 9, 2019 at 12:50
The SQL statement never runs, the issue being the that the hiddenfield (textbox( value is NULL. Although when I run the application the Textbox shows as being populated. — user3580480
– user3580480, Commented Aug 9, 2019 at 13:00
As an aside, it's bad practice and plain dangerous to use non-parameterised variables in your SQL commands/Queries - you leave yourself wide open to SQL injection attack — Sean T
– Sean T, Commented Aug 9, 2019 at 13:36
Also, why not use a nullable int instead of returning 999999999? — Lee Taylor
– Lee Taylor, Commented Aug 9, 2019 at 13:52

Karan · Accepted Answer · 2019-08-09 14:19:17Z

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!");
}

edited Aug 9, 2019 at 14:19

answered Aug 9, 2019 at 13:43

Karan

12.7k3 gold badges31 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3580480 Over a year ago

Thanks Karan, but it is not the invoicenumberhidden value that I am trying to return. That value is sent to the code behind and used as part of the SQL statement. The value I want to return is the COUNT result from the SQL statement which is an int returned from the function, ucount

Karan Over a year ago

Updated my answer. I hope PageMethods can solve your issue.

user3580480 Over a year ago

Thank you PageMethods was what I needed :)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related