I am retrieving a moderate amount of data and processing it - nothing unique there. What was odd at first was that with some sets of data, it worked fine, and with others, I got the following err msg:
This err msg seems to be total hogwash, though (misleading, at any rate), because there is no more data with the failing set than with the successful set, and so it shouldn't take any longer to run the "bad" data than the good.
Mor enlightening, perhaps, are the other err msgs that appear after that:
Note: I get these verbose err dialogs, rather than something more concise, because of the debugging code you can see in the catch block of the code below.
So it seems apparent that it's not really a "too much data" issue, as the initial err msg indicates. It's also not a "missing data" issue, because if I query for data from December 2014 through December 2015 (for which upcoming month there is no data), it runs fine -- it just returns all 0s for December 2015; so it must be a "bad (not just missing) data" issue. How can I determine what the bad data is and defensively prevent it from ruining the run of the app?
Line 601, implicated in the err msg above, contains this code:
private void ReadData(string _unit, string monthBegin, string monthEnd, string beginYear, string endYear)
{
try
{
String dateBegin = UsageRptConstsAndUtils.GetYYYYMMDD(monthBegin, beginYear, true);
String dateEnd = UsageRptConstsAndUtils.GetYYYYMMDD(monthEnd, endYear, false);
DateTime dtBegin = UsageRptConstsAndUtils.DatifyYYYYMMDD(dateBegin);
DateTime dtEnd = UsageRptConstsAndUtils.DatifyYYYYMMDD(dateEnd);
DataTable dtUsage = SqlDBHelper.ExecuteDataSet("sp_ViewProductUsage_MappingRS", CommandType.StoredProcedure,
new SqlParameter() { ParameterName = "@Unit", SqlDbType = SqlDbType.VarChar, Value = _unit },
new SqlParameter() { ParameterName = "@BegDate", SqlDbType = SqlDbType.DateTime, Value = dtBegin },
new SqlParameter() { ParameterName = "@EndDate", SqlDbType = SqlDbType.DateTime, Value = dtEnd }
);
SqlDBHelper.ExecuteDataSet() is:
public static DataTable ExecuteDataSet(string sql, CommandType cmdType, params SqlParameter[] parameters)
{
using (DataSet ds = new DataSet())
using (SqlConnection connStr = new SqlConnection(UsageRptConstsAndUtils.CPSConnStr))
using (SqlCommand cmd = new SqlCommand(sql, connStr))
{
cmd.CommandType = cmdType;
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
try
{
cmd.Connection.Open();
new SqlDataAdapter(cmd).Fill(ds);
}
catch (SqlException sqlex)
{
for (int i = 0; i < sqlex.Errors.Count; i++)
{
var sqlexDetail = String.Format("From ExecuteDataSet(), SQL Exception #{0}{1}Source: {2}{1}Number: {3}{1}State: {4}{1}Class: {5}{1}Server: {6}{1}Message: {7}{1}Procedure: {8}{1}LineNumber: {9}",
i + 1, // Users would get the fantods if they saw #0
Environment.NewLine,
sqlex.Errors[i].Source,
sqlex.Errors[i].Number,
sqlex.Errors[i].State,
sqlex.Errors[i].Class,
sqlex.Errors[i].Server,
sqlex.Errors[i].Message,
sqlex.Errors[i].Procedure,
sqlex.Errors[i].LineNumber);
MessageBox.Show(sqlexDetail);
}
}
catch (Exception ex)
{
String exDetail = String.Format(UsageRptConstsAndUtils.ExceptionFormatString, ex.Message, Environment.NewLine, ex.Source, ex.StackTrace);
MessageBox.Show(exDetail);
}
return ds.Tables[0];
}
}
Line 396 (referenced in the last err msg) is the first line of code here:
private String GetContractedItemsTotal()
{
var allContractRecords = _itemsForMonthYearList.Where(x => x.ContractItem);
var totalContractItemPurchases = allContractRecords.Sum(x => x.TotalPurchases);
return totalContractItemPurchases.ToString("C");
}
What could be causing this code to sometimes crash with the "Cannot find Table 0" and "Value cannot be null" exceptions? Or more to the point, how can I prevent it from wreaking such havoc when a value is null?
Some more context:
_itemsForMonthYearList is defined like this:
private List<ItemsForMonthYear> _itemsForMonthYearList;
..and populated like so:
var ifmy = new ItemsForMonthYear();
int qty = Convert.ToInt32(productUsageByMonthDataRow["TotalQty"]);
// TotalPrice as Decimal for calculation
Decimal totPrice = Convert.ToDecimal(productUsageByMonthDataRow["TotalPrice"]);
Decimal avgPrice = Convert.ToDecimal(productUsageByMonthDataRow["AvgPrice"]);
String monthYear = productUsageByMonthDataRow["MonthYr"].ToString();
ifmy.ItemDescription = desc;
ifmy.TotalPackages = qty;
ifmy.TotalPurchases = totPrice;
ifmy.AveragePrice = avgPrice;
ifmy.monthYr = monthYear;
ifmy.ContractItem = contractItem; // added 11/16/2016
if (null == _itemsForMonthYearList)
{
_itemsForMonthYearList = new List<ItemsForMonthYear>();
}
_itemsForMonthYearList.Add(ifmy);
