I am selecting max(id) from database which are grouped by date. Sometimes there might be situation where date does not have any id. That time it rising error so how to handle it n assign to int variable.
SqlCommand maxid = new SqlCommand("select max(block) from blocks_allocation where date='" + DR.ItemArray.GetValue(7).ToString() + "'", con);
SqlDataReader maxrd = maxid.ExecuteReader();
if (maxrd.Read())
block_no = int.Parse(maxrd["block"].ToString()) + 1;
maxrd.Close();
SqlCommand returns DBNull.Value if the value is null, so if you have a query that could return null values you need to test against DBNull.Value first, like this:
var date = DR.ItemArray.GetValue(7).ToString();
const string sql = "SELECT MAX(block) FROM blocks_allocation WHERE date = @date";
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@date", date);
var maxBlock = cmd.ExecuteScalar();
block_no = maxBlock == DBNull.Value ? null : (int?) maxBlock;
}
(This assumes that block_no is a nullable int). I've also changed a few other things:
ExecuteScalar instead of Read etc...using blocks instead of manually closing / disposing of objects.I've used the inline-if syntax to set block_no, but you can also use a standard if should you prefer:
if (maxBlock == DBNull.Value)
{
block_no = null;
}
else
{
block_no = (int?) maxBlock;
}
Check whether the max(id) is null, if it so then return 1 using ISNULL()
select isnull(max(block),1) from blocks_allocation
DateTimevalues as a string since you have a column named asdate. Don't do that. It is a bad desing.