CODEWITHSUNDEEP
c#mysql
Naomi
Naomi

Reputation: 139

Data is Null. This method or property cannot be called on Null value (mysql to textbox)

My friend wants to transfer her data from the database to a textbox to her program in c# but it gets an error of this:

"Data is Null. This method or property cannot be called on Null values."

Here is the code by the way:

sql_command = new MySqlCommand("select sum(lt_min_hours) as TotalLate from tbl_late where (late_date between '" + date_start + "' and '" + date_to + "' and empid = '" + empid + "')", sql_connect);
sql_reader = sql_command.ExecuteReader();            

if (sql_reader.Read())
{
    textBox_tlate.Text = sql_reader.GetString("TotalLate");
}
else
{
    MessageBox.Show("No Data.");
}

Upvotes: 0

Views: 541

Answers (2)

Steve
Steve

Reputation: 216293

You need to test for DbNull.Value against your field before assigning it to the textbox

if (sql_reader.Read())
{
    if(!sql_reader.IsDbNull(sql_reader.GetOrdinal("TotalLate")))
        textBox_tlate.Text = sql_reader.GetString("TotalLate");
}

EDIT
According to your comment, nothing happens, so the WHERE condition fails to retrieve any record and the result is a NULL.
Looking at your query I suppose that your variables containing dates are converted to a string in an invalid format. This could be fixed using a ToString and a proper format string (IE: yyyy-MM-dd) but the correct way to handle this is through a parameterized query

sql_command = new MySqlCommand(@"select sum(lt_min_hours) as TotalLate 
                 from tbl_late 
                 where (late_date between @init and @end and empid = @id", sql_connect);
sql_command.Parameters.Add("@init", MySqlDbType.Date).Value = date_start;
sql_command.Parameters.Add("@end", MySqlDbType.Date).Value = date_end;
sql_command.Parameters.Add("@id", MySqlDbType.Int32).Value = empid;
sql_reader = sql_command.ExecuteReader();

This assumes that date_start and date_end are DateTime variables and empid is an integer one. In this way, the parsing of the parameters is done by the MySql engine that knows how to handle a DateTime variable. Instead your code uses the automatic conversion made by the Net Framework that, by default, uses your locale settings to convert a date to a string.

Upvotes: 0

Soner Gönül
Soner Gönül

Reputation: 98750

From documentation;

SUM() returns NULL if there were no matching rows.

But first of all, You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

After that, you can use ExecuteScalar instead of ExecuteReader which is returns only one column with one row. In your case, this is exactly what you want.

textBox_tlate.Text = sql_command.ExecuteScalar().ToString();

Also use using statement to dispose your connection and command automatically.

Upvotes: 1

Related Questions