CODEWITHSUNDEEP
c#sql
rigamonk
rigamonk

Reputation: 1181

Passing null into a SQL command

I have a class:

public class Intlist
{
    public int yesFlag{ get; set; }
    public int? noFlag { get; set; }
}

I need to update a database table, but sometimes the value is null for the nullable ints. I am using

CommandText = @"UPDATE thetable 
                SET Yes = " + list.yesFlag +
                   ",NoFlag = " + (list.previous == null) ? DBNull.Value : list.previous +
                   ",NextValue = 10"

I'm trying to get it so that if noFlag is null, it enters a null in the database, but I'm getting errors:

Cannot implicitly convert type 'string' to 'bool'

and

Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DBNull' and 'string'

Upvotes: 1

Views: 988

Answers (2)

Abdul Saleem
Abdul Saleem

Reputation: 10612

Using parameters is strongly recommended. But in your case you just enter as 'Null'. It will work.

CommandText = @"UPDATE thetable SET Yes = " + 
             list.yesFlag.ToString() + ",NoFlag = " + (list.previous == null 
             ? "NULL" : list.previous) + ",NextValue = 10";

This query will go as it is.

Upvotes: 1

Gregg Duncan
Gregg Duncan

Reputation: 2725

Sayka was on target. Use parameters.

Not only will you protect your database from sql injection attacks but you can take advantage of .Nets built in functionality to handle these types of issues.

    CommandText = @"UPDATE thetable SET Yes = @yesFlag, NoFlag = @noflag, NextValue = 10";
    Command.Parameters.AddWithValue("@yesFlag", list.yesFlag);
    Command.Parameters.AddWithValue("@noFlag", list.previous);

Upvotes: 4

Related Questions