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