Home / Questions / 120721
Accepted
Ivxn
Asked: 2020-11-28 18:06:08 +0800 CST 2020-11-28 18:06:08 +0800 CST

How to send NULL values ​​to SP from .NET

  • 772

I have one stored procedurethat makes a select. I use this SPfor a search form which, if no search parameters are passed, returns all the records. Otherwise some fields are filtered as dates that are of type DATE. In any case, I have to send the fields, if they are not with values, I would have to send them with NULL. The problem is that, as I pass type values DATE​​in NULL, it gives me an error that it cannot be converted. I have tried to do it in the following way, but I have not been able to fill my GV. Is there another way to do this?

protected void BtnSearch_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnection con = new SqlConnection(Util.ObtenerCadenaConexion("ConnectionString"));
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "ProcListarAnuncio";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Titulo", txtTitulo.Text.Trim() == "" ? null : txtTitulo.Text.Trim());
        cmd.Parameters.AddWithValue("@FechaDesde", txtFechaI.Text.Trim() == "" ? null : txtFechaI.Text.Trim());
        cmd.Parameters.AddWithValue("@FechaHasta", txtFechaF.Text.Trim() == "" ? null : txtFechaF.Text.Trim());
        cmd.Connection = con;
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();    

    }
}
c#

1 Answers

  1. Best Answer

    Instead of using null, you must pass DBNull.Value. Although a little weird, that's the way to pass NULLparameters in ADO.NET:

    cmd.Parameters.AddWithValue("@Titulo", txtTitulo.Text.Trim() == "" ? (object)DBNull.Value : txtTitulo.Text.Trim());
cmd.Parameters.AddWithValue("@FechaDesde", txtFechaI.Text.Trim() == "" ? (object)DBNull.Value : txtFechaI.Text.Trim());
cmd.Parameters.AddWithValue("@FechaHasta", txtFechaF.Text.Trim() == "" ? (object)DBNull.Value : txtFechaF.Text.Trim());
    • 2