Parameterized query which sends NULL value, with C# and SQL Server

Question

I tried to pass some variables into a parameterized query but when the value is NULL it throws an exception that says the value is not provided.

How can I fix that without an if or something like that?

My code is this

var cmdPersona_Log = new SqlCommand();
cmdPersona_Log.Parameters.Clear();
cmdPersona_Log.Connection = mySqlConnection;
cmdPersona_Log.CommandType = CommandType.Text;
cmdPersona_Log.CommandText = @"INSERT INTO [Tomin].[TominRH].[Persona_Log] "
+ "([Id_Action],[Id_User],[Id_Date],[Id_Entidad],[Nombre],[Paterno],[Materno],[Sexo],[Id_Nacionalidad])"
+ " Values (1, 'Admin', @fecha, @id_entidad, @nombre, @paterno, @materno, 1, 52)";

cmdPersona_Log.Parameters.Add("@fecha", DateTime.Now);
cmdPersona_Log.Parameters.Add("@id_entidad", dbRow["CUENTA"]);
cmdPersona_Log.Parameters.Add("@nombre", nombre);
cmdPersona_Log.Parameters.Add("@paterno", paterno);
cmdPersona_Log.Parameters.Add("@materno", materno);

cmdPersona_Log.ExecuteNonQuery();

@materno, the thing is, when I do the query without parameters there is no problem, but there will be some times that those parameters will be null — poz2k4444
– poz2k4444, Commented Aug 28, 2012 at 0:18

Claudio Redi · Accepted Answer · 2012-08-28 00:24:39Z

3

I will assume that your problem is that the underlying field doesn't support nulls. If this is the case, you could do something like this

cmdPersona_Log.Parameters.Add("@nombre", nombre ?? string.Empty);
cmdPersona_Log.Parameters.Add("@paterno", paterno ?? string.Empty); 
cmdPersona_Log.Parameters.Add("@materno", materno ?? string.Empty);

You'll be inserting an empty string in case you have a null using the null-coalescing operator.

answered Aug 28, 2012 at 0:24

Claudio Redi

68.6k15 gold badges138 silver badges161 bronze badges

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

Collectives™ on Stack Overflow

Parameterized query which sends NULL value, with C# and SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related