Input string not in correct formate when query return null t

Problem

when convert null to decimal i get error input string not in correct format

how to solve it

public static object ExecuteScalar(string sql, DbParameter[] @params = null)
{
if (sql == "") return "";
object result = null;
lock (synObj)
{
sql = AnalyizeBooleanFields(sql);
cmd.CommandText = sql;
cmd.Parameters.Clear();
if (@params != null)
{
for (int i = 0; i < @params.Length; i++)
{
cmd.Parameters.Add(@params[i]);
}
}
if (Connection.State != ConnectionState.Open)
Connection.Open();
if (WithTransaction)
cmd.Transaction = _transaction;
result = cmd.ExecuteScalar();
if (!WithTransaction)
Connection.Close();
}
return (result == null || result == DBNull.Value) ? "" : result;
}
private decimal GetLastReading(string UnitCode, string Year, string Month)
{
string sqlquery = "";
if (txtMonth.Text == "1")
{
sqlquery = "select CurrentMeterReading from WAHInvoice where UnitCode=" + UnitCode + " and Month = 12 And Year =" + (Convert.ToInt32(Year) - 1).ToString() + "";
}
else
{
sqlquery = "select CurrentMeterReading from WAHInvoice where UnitCode='" + UnitCode + "' and Month = '" + (Convert.ToInt32(Month) - 1).ToString() + "' And Year ='" + Year + "'";
}
return ConvertToDecimal(DataAccess.ExecuteScalar(sqlquery));
}

when call i do as following

decimal LastMeterReading = GetLastReading(dt.Rows[i]["UnitCode"].ToString(), txtYear.Text, txtMonth.Text).ToString();

Now if sqlquery not return any row it give me exception from function GetLastReading

Input string not in correct format when query return null to decimal value .

so that how to avoid this error please.

I think we can write like following:

private decimal GetLastReading(string UnitCode, string Year, string Month)
{
decimal result = 0;
try
{
string sqlquery = "";
if (txtMonth.Text == "1")
{
sqlquery = "select CurrentMeterReading from WAHInvoice where UnitCode=" + UnitCode + " and Month = 12 And Year =" + (Convert.ToInt32(Year) - 1).ToString() + "";
}
else
{
sqlquery = "select CurrentMeterReading from WAHInvoice where UnitCode='" + UnitCode + "' and Month = '" + (Convert.ToInt32(Month) - 1).ToString() + "' And Year ='" + Year + "'";
}
result = ConvertToDecimal(DataAccess.ExecuteScalar(sqlquery));
}
catch(Exception ex)
{
//Log Exception
}
return result;
}

Regards,

Mukesh Nayak

1 Reply

Know the answer? Post it — somebody with the same question will find it here.