How to make Sqlcommand accept null values

Question

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;
            }
        }
    }

Welcome to Stack Overflow, future me. – Nick Vaccaro Apr 29 '13 at 13:07 — Nick Vaccaro, Apr 29 '13 at 13:07

score 4 · Answer 1 · edited May 23 '17 at 11:49

4

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();
    ....

edited May 23 '17 at 11:49

Community

1
1

answered Apr 29 '13 at 13:07

Steve

213,761
22
232
286

Thanks for the quick reply! I fixed up the string concatenation and that worked no problem, although when I replaced 'int rest hours' and list.add(resthours) with those lines I get Argument 1: cannot convert from 'int' to 'string'. Is there something I'm missing? – Nick V Apr 29 '13 at 13:34

score 2 · Accepted Answer · answered Apr 29 '13 at 13:08

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.

Yeah I guess that's what I was aiming for, but now I'm getting an Unable to cast object of type 'System.Int32' to type 'System.String' error. Do you know what I have to fix? Thanks — Nick V, Apr 29 '13 at 13:39
@NickV, on what line? What is the value of the field in the `reader` at that time? Because we can go a different route. — Mike Perrenoud, Apr 29 '13 at 13:51
@NickV, fantastic! Feel free to edit the answer and update the code with your changes. — Mike Perrenoud, Apr 29 '13 at 14:00

score 1 · Answer 3 · answered Apr 29 '13 at 13:08

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.

How to make Sqlcommand accept null values

3 Answers3