How to set NULL in JSON object in C#

Guillaume
 
on Mar 27, 2018 07:19 AM
2784 Views
I am using a dropdown list as a filter for an sql query in webmethod that I call with AJAX. The dropdown options values are "",1,2,3,4,5... where "" is when nothing is selected.  The query doesn't work when "" is selected, because the NULL is not sent to the query with this function:  JSON.stringify({ 'fkidregion': $('[id$=ddlRegion]').val() })
 
Here is the problem:
exec sp_executesql N'SELECT region, customer FROM Tregion WHERE (@fkidregion is null or fkidregion = @fkidregion)',N'@fkidregion nvarchar(4000)',@fkidregion=N''

But this is how is should be:  

exec sp_executesql N'SELECT region, customer FROM Tregion WHERE (@fkidregion is null or fkidregion = @fkidregion)',N'@fkidregion nvarchar(4000)',@fkidregion=NULL
So my question is how can I pass a null value within the JSON data or indicate that a null should be used for an empty string.
 
Code behind
 
[System.Web.Services.WebMethod]
public static BOPData[] PopulateBOPDataFields(string fkidregion)
{
    DataTable dt = new DataTable();
    List<BOPData> list = new List<BOPData>();

    String strConnString = ConfigurationManager.ConnectionStrings["DBConnString"].ConnectionString;
    String strQuery = @"SELECT region, customer FROM Tregion  WHERE (@fkidregion is null or fkidregion = @fkidregion)";
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@fkidregion", fkidregion);
            cmd.CommandText = strQuery;
            cmd.Connection = con;
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            con.Close();
            foreach (DataRow dtrow in dt.Rows)
            {
                BOPData data = new BOPData();
                data.region = dtrow["region"].ToString();
                data.customer = dtrow["customer"].ToString();
                list.Add(data);
            }
        }
    }
    return list.ToArray();
}

public class BOPData
{
    public string region { get; set; }
    public string customer { get; set; }
}

Ajax

 

function PopulateBOPDataFields() {
        $.ajax({
            type: "POST",
            url: pageUrl + '/PopulateBOPDataFields',
            contentType: "application/json; charset=utf-8",
            data: JSON.stringify({ 'fkidregion': $('[id$=ddlRegion]').val() }),
            dataType: "json",
            success: function (data) {
                for (var i = 0; i < data.d.length; i++) {
                    $('[id$=gvBOP]').append(
                        "<tr><td>" + data.d[i].region
                        + "</td><td>" + data.d[i].customer
                        + "</td></tr>");
                }
            },
            failure: function (response) {
                alert(response.d);
            },
            error: function (response) {
                alert(response.d);
            }
        });
    }

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Mudassar
 
on Mar 28, 2018 07:32 AM

In Json you need to leave it blank and if integer then 0.

Guillaume
 
on Apr 02, 2018 01:49 PM

Finally, I found a workaround. In my SqlCommand, I changed this:

cmd.Parameters.AddWithValue("@fkidregion", fkidregion);

to this: 

cmd.Parameters.AddWithValue("@fkidregion", fkidregion != "" ? fkidregion : Convert.DBNull);

Now the NULL is well interpreted by the query. 

Thank you for your time.