CODEWITHSUNDEEP
c#sqlcommand
Nick V
Nick V

Reputation: 55

How to make Sqlcommand accept null values

I'm trying to get data in a gridview from a database to show up in text boxes upon clicking and it works fine for the rows with no null data, although since my int columns have some null values my GetInt32 methods keep returning "Data is Null. This method or property cannot be called on Null values."

Is there a simple way to fix or work around this? Do I replace GetInt32 with another method? I'd like for the data that is null to show up blank/empty in the text boxes if possible. Here's my code if you have any suggestions, thanks.

    public ArrayList GetAllPersonnel(int WorkerID) {
        using (var connection = new SqlConnection(connectionString)) {
            connection.Open();
            String query = "Select * FROM Personnel WHERE WorkerID = " + WorkerID;

            using (var command = new SqlCommand(query, connection)) {
                var reader = command.ExecuteReader();
                var list = new ArrayList();
                while (reader.Read()) {
                    String firstname = reader.GetString(1);
                    String lastname = reader.GetString(2);
                    String occupation = reader.GetString(3);
                    String deployment = reader.GetString(4);
                    int disasterid = reader.GetInt32(5);
                    String location = reader.GetString(6);
                    int deployedhours = reader.GetInt32(7);
                    int resthours = reader.GetInt32(8);

                    list.Add(firstname);
                    list.Add(lastname);
                    list.Add(occupation);
                    list.Add(deployment);
                    list.Add(disasterid);
                    list.Add(location);
                    list.Add(deployedhours);
                    list.Add(resthours);
                }
                connection.Close();
                reader.Close();
                return list;
            }
        }
    }

Upvotes: 4

Views: 1172

Answers (3)

Steve
Steve

Reputation: 216273

You should use IsDBNull method of the SqlDataReader

int resthours = (!reader.IsDBNull(8) ? reader.GetInt32(8) : 0);

or, more directly

list.Add((!reader.IsDBNull(8) ? reader.GetInt32(8).ToString(): string.Empty));

Said that, I have noticed that you use a string concatenation to build the sql command text to retrieve records. Please do not do that. It is very dangerous and could lead to Sql Injection 

String query = "Select * FROM Personnel WHERE WorkerID = @wkID";
using (var command = new SqlCommand(query, connection)) 
{
    command.Parameters.AddWithValue("@wkID", WorkerID);
    var reader = command.ExecuteReader();
    ....

Upvotes: 4

Murtuza Kabul
Murtuza Kabul

Reputation: 6514

You have at least two ways to sort this out

  • Modify your sql to select either zero or whatever you think suitable in the place of null value. This will ensure that you always have an integer value in the integer column. It can be done in the following manner

    select ISNULL ( ColumnName , 0 ) as ColumnName from xxx

  • Always fetch object from the reader and check if it is null or not. If it is null then replace it with suitable value.

Upvotes: 1

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

OK, so you're effectively saying that everything you display should be a string type, which is fine, I'm just making that point because you stated you want even integers to show up as an empty string. So how about this code?

String firstname = reader.GetString(1);
String lastname = reader.GetString(2);
String occupation = reader.GetString(3);
String deployment = reader.GetString(4);
String disasterid = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
String location = reader.GetString(6);
String deployedhours = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
String resthours = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);

list.Add(firstname);
list.Add(lastname);
list.Add(occupation);
list.Add(deployment);
list.Add(disasterid);
list.Add(location);
list.Add(deployedhours);
list.Add(resthours);

Now, the reason I stated that you want to leverage everything as a string is because the default value for a int is 0 and that wouldn't meet the empty text box requirement.

Upvotes: 2

Related Questions