SqlDataReader null exception

Question

I have SqlDataReader which fills multiple textboxes, but the issue is when they are NULL I get an exception which I dont know how to handle.

 SqlCommand command = new SqlCommand("SELECT * FROM zajezd WHERE akce='" + akce + "' and rocnik='" + klientClass.Rocnik() + "'", spojeni);
                spojeni.Open();
                SqlDataReader read= command .ExecuteReader();

                if (read.Read())
                {

                s_ub_cen.Text = read.GetDecimal(59).ToString();
                object nulldate = (s_ub_dat.Text = read.IsDBNull(61) ?
                    string.Empty : read.GetDateTime(61).ToShortDateString());
 }

Exception: System.Data.SqlTypes.SqlNullValueException: Data are null.

I have like 20 Textboxes, is there any easy solution? I would like to leave the textboxes empty when the value is null, everything is working fine for ShortDatString.

I need to figure out how to handle this like when value from DB is NULL:

s_ub_cen.Text = precti2.GetDecimal(59).ToString();

Thank you so much.

You check for null on the second column - why not the first? — Marc Gravell
– Marc Gravell, Commented Aug 26, 2013 at 7:32
I found out that that it would be hard-coding doing this for 20 textboxes. I just wanted to ask whether there exist better solution. — Marek
– Marek, Commented Aug 26, 2013 at 7:33

Tim Schmelter · Accepted Answer · 2013-08-26 08:03:55Z

2

You need to check for IsDBNull:

if(!precti2.IsDBNull(59))
{
    s_ub_cen.Text = precti2.GetDecimal(59).ToString();
}

Also, prevent SQL-Injection by using sql-parameters, don't concatenate strings to build the sql query.

Instead of:

SqlCommand command = new SqlCommand("SELECT * FROM zajezd WHERE akce='" + akce + "' and rocnik='" + klientClass.Rocnik() + "'", spojeni);

this:

using(var command = new SqlCommand("SELECT * FROM zajezd WHERE akce=@akce and rocnik=@rocnik", spojeni))
{
    command.Paramaters.AddWithValue("@akce", akce);
    command.Paramaters.AddWithValue("@rocnik", klientClass.Rocnik());
    // ....
}

Edit

is there any solution that would solve this for 20 textboxes ?

In general there is no automatism. You have to provide a sql-parameter for every dynamic input.

However, if you are looking for an elegant way to get a "safe-string" you could use this extension:

public static class DataExtensions
{
    public static string GetSafeString(this SqlDataReader reader, int colIndex)
    {
        if (!reader.IsDBNull(colIndex))
            return reader[colIndex].ToString();
        else
            return string.Empty;
    }
}

You use this method in this way:

_ub_cen.Text = reader.GetSafeString(59);

edited Aug 26, 2013 at 8:03

answered Aug 26, 2013 at 7:30

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marek Over a year ago

Thanks for answers, is there any solution that would solve this for 20 textboxes ? As this should be implemented to each textbox read right?

Tim Schmelter Over a year ago

@Marek: You have to add a sql-parameter for every dynamic (user-)input , so no, there is no automatism.

Tim Schmelter Over a year ago

@Marek: Edited my answer to provide a method that may help to shorten your code.

Collectives™ on Stack Overflow

SqlDataReader null exception

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related