Assign null to a SqlParameter

Question

The following code gives an error -  No implicit conversion from DBnull to int    SqlParameter   parameters   new SqlParameter 1       SqlParameter planIndexParameter   new SqlParameter   AgeIndex   SqlDbType Int   planIndexParameter Value    AgeItem AgeIndex   null    DBNull Value   AgeItem AgeIndex  parameters 0    planIndexParameter

User · Answer

You need pass DBNull Value as a null parameter within SQLCommand  unless a default value is specified within stored procedure  if you are using stored procedure   The best approach is to assign DBNull Value for any missing parameter before query execution  and following foreach will do the job   foreach  SqlParameter parameter in sqlCmd Parameters        if  parameter Value    null                parameter Value   DBNull Value            Otherwise change this line   planIndexParameter Value    AgeItem AgeIndex   null    DBNull Value   AgeItem AgeIndex    As follows   if  AgeItem AgeIndex   null      planIndexParameter Value   DBNull Value  else     planIndexParameter Value   AgeItem AgeIndex    Because you can t use different type of values in conditional statement  as DBNull and int are different from each other  Hope this will help

User · Answer

if   id categoria padre  gt  0        objComando Parameters Add  id categoria padre   SqlDbType Int  Value    id categoria padre    else       objComando Parameters Add  id categoria padre   DBNull Value  Value   DBNull Value

User · Answer

The accepted answer suggests making use of a cast   However  most of the SQL types have a special Null field which can be used to avoid this cast    For example  SqlInt32 Null  Represents a DBNull that can be assigned to this instance of the SqlInt32 class    int  example   null  object exampleCast    object  example    DBNull Value  object exampleNoCast   example    SqlInt32 Null

User · Answer

A simple extension method for this would be       public static void AddParameter this SqlCommand sqlCommand  string parameterName           SqlDbType sqlDbType  object item                sqlCommand Parameters Add parameterName  sqlDbType  Value   item    DBNull Value

User · Answer

if  AgeItem AgeIndex   null        cmd Parameters Add new SqlParameter  ParaMeterName   SqlDbType DateTime  Value   DBNull     else       cmd Parameters Add new SqlParameter  ParaMeterName   SqlDbType DateTime  Value   AgeItem AgeIndex

User · Answer

try something like this   if   id categoria padre  gt  0        objComando Parameters Add  id categoria padre   SqlDbType Int  Value    id categoria padre    else       objComando Parameters Add  id categoria padre   DBNull Value  Value   DBNull Value

User · Answer

My code  working in real project Look the ternary operator beafore make the sqlparameter this is the best way for me  withou problems       public bool Key AddExisting                 string clave           int  idHito FileServer           int  idTipoDocumental Almacen           string tipoExp CHJ           int idTipoExp Verti2           int idMov Verti2                     List lt SqlParameter gt  pars   new List lt SqlParameter gt                            new SqlParameter   ParameterName     Clave   Value   clave       LOOK - gt    idHito FileServer    null   new SqlParameter   ParameterName     IdHito FileServer   Value   DBNull Value     new SqlParameter   ParameterName     IdHito FileServer   Value   idHito FileServer       LOOK - gt    idTipoDocumental Almacen    null   new SqlParameter   ParameterName     IdTipoDocumental Almacen   Value   DBNull Value     new SqlParameter   ParameterName     IdTipoDocumental Almacen   Value   idTipoDocumental Almacen                 new SqlParameter   ParameterName     TipoExp CHJ   Value   tipoExp CHJ                 new SqlParameter   ParameterName     IdTipoExp Verti2   Value   idTipoExp Verti2                 new SqlParameter   ParameterName     IdMov Verti2   Value   idMov Verti2                       string sql    INSERT INTO  dbo   Enlaces ClavesCHJ MovimientosVerti2                                         Clave                      IdHito FileServer                      IdTipoDocumental Almacen                      TipoExp CHJ                      IdTipoExp Verti2                      IdMov Verti2                                      VALUES                                       Clave                    IdHito FileServer                    IdTipoDocumental Almacen                    TipoExp CHJ                    IdTipoExp Verti2                    IdMov Verti2                              return DbBasic ExecNonQuery ref this conn  sql  pars

User · Answer

With one line of code  try this   var piParameter   new SqlParameter   AgeIndex   AgeItem AgeIndex     object DBNull Value

User · Answer

Try this   if  AgeItem AgeIndex    null       SqlParameter   parameters   new SqlParameter 1      SqlParameter planIndexParameter   new SqlParameter   AgeIndex   SqlDbType Int      planIndexParameter Value   AgeItem AgeIndex     parameters 0    planIndexParameter      In other words  if the parameter is null just don t send it to your stored proc  assuming  of course  that the stored proc accepts null parameters which is implicit in your question

User · Answer

Consider using the Nullable T  structure available  It ll let you only set values if you have them  and your SQL Command objects will recognize the nullable value and process accordingly with no hassle on your end

User · Answer

The problem is that the    operator cannot determine the return type because you are either returning an int value or a DBNull type value  which are not compatible   You can of course cast the instance of AgeIndex to be type object which would satisfy the    requirement   You can use the    null-coalescing operator as follows  SqlParameter   parameters   new SqlParameter 1        SqlParameter planIndexParameter   new SqlParameter   AgeIndex   SqlDbType Int   planIndexParameter Value    object AgeItem AgeIndex    DBNull Value  parameters 0    planIndexParameter     Here is a quote from the MSDN documentation for the    operator that explains the problem     Either the type of first expression and second expression must be the same  or an implicit conversion must exist from one type to the other

User · Answer

dynamic psd   DBNull Value               if  schedule pushScheduleDate  gt  DateTime MinValue                                psd   schedule pushScheduleDate                              sql DBController RunGeneralStoredProcedureNonQuery  SchedulePush                        new string       PushScheduleDate                         new object     psd    10   PushCenter

User · Answer

Try this   SqlParameter   parameters   new SqlParameter 1       SqlParameter planIndexParameter   new SqlParameter   AgeIndex   SqlDbType Int    planIndexParameter IsNullable   true     Add this line  planIndexParameter Value    AgeItem AgeIndex   null    DBNull Value   AgeItem AgeIndex     parameters 0    planIndexParameter

User · Answer

If you use the conditional ternary  operator the compiler needs an implicit conversion between both types  otherwise you get an exception    So you could fix it by casting one of both to System Object   planIndexParameter Value    AgeItem AgeIndex   null    DBNull Value    object  AgeItem AgeIndex    But since the result is not really pretty and you always have to remember this casting  you could use such an extension method instead   public static object GetDBNullOrValue lt T gt  this T val        bool isDbNull   true      Type t   typeof T        if  Nullable GetUnderlyingType t     null          isDbNull   EqualityComparer lt T gt  Default Equals default T   val       else if  t IsValueType          isDbNull   false      else         isDbNull   val    null       return isDbNull   DBNull Value    object  val      Then you can use this concise code   planIndexParameter Value   AgeItem AgeIndex GetDBNullOrValue

User · Answer

I use a simple method with a null check       public SqlParameter GetNullableParameter string parameterName  object value                if  value    null                        return new SqlParameter parameterName  value                     else                       return new SqlParameter parameterName  DBNull Value

User · Answer

int  nullableValue   null  object nullableValueDB      get         if nullableValue  null            return DBNull Value         else           return  int nullableValue           I m solving like that

User · Answer

This is what I simply do             var PhoneParam   new SqlParameter   Phone   DBNull Value           if  user User Info Phone    null                        PhoneParam SqlValue   user User Info Phone                     return this Database SqlQuery lt CustLogonDM gt   UpdateUserInfo  UserName   NameLast   NameMiddle   NameFirst   Address   City   State   PostalCode   Phone               UserNameParam  NameLastParam  NameMiddleParam  NameFirstParam  AddressParam  CityParam  StateParam  PostalParam  PhoneParam  Single

User · Answer

In my opinion the better way is to do this with the Parameters property of the SqlCommand class   public static void AddCommandParameter SqlCommand myCommand        myCommand Parameters AddWithValue            AgeIndex            AgeItem AgeIndex   null    DBNull Value   AgeItem AgeIndex

[c#] Assign null to a SqlParameter

Examples related to c#

Examples related to dbnull

Examples related to sqlparameter