How to set NULL in JSON object in C#

Last Reply one year ago By Guillaume

Posted one year ago
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);
            }
        });
    }

 

You are viewing reply posted by: Guillaume one year ago.
Posted one year ago

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.