4

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;
            }
        }
    }
Improve this question
1
  • Welcome to Stack Overflow, future me. Commented Apr 29, 2013 at 13:07

3 Answers 3

Reset to default
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();
    ....
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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?
2

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.

Improve this answer

3 Comments

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
@NickV, on what line? What is the value of the field in the reader at that time? Because we can go a different route.
@NickV, fantastic! Feel free to edit the answer and update the code with your changes.
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.