How can I defensively code against randomly referencing “Table 0” and null values?

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:

enter image description here

  • Column and Row grouping in SQL Server Reporting Services 2008
  • How do I load text files greater than the 64 kb buffersize limit?
  • Enable/Disable Sql Server Agent using a t-sql script
  • How to find who deleted a view from DB with SIMPLE recovery model
  • SQL select print out results of stored procedue
  • Query times out when executed from web, but super-fast when executed from SSMS
  • 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:

    enter image description here

    enter image description here

    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);
    

  • What is the most appropriate data type for storing an IP address in SQL server?
  • How to get no of days from MON-YYYY data
  • What are the advantages of a query using a derived table(s) over a query not using them?
  • Join statement with different conditions on different row set
  • Copy SQL Server Express Database to Another Computer
  • refused my local machine sql connection for 1433 port
  • One Solution collect form web for “How can I defensively code against randomly referencing “Table 0” and null values?”

    As jmcilhinney suggests, tweaking the CommandTimeout value seems to have been the ticket/done the trick.

    I originally made the SqlCommand’s CommandTimeout value 300 (5 minutes), but with that I got “Context Switch Deadlock occurred.” So I then reduced it to 120 (2 minutes), and that seems to be more or less the “sweet spot” for me. I did get “Timeout expired” one time out of several tests, but when I retried the same exact range, it completed successfully the second time, so I guess it’s just “one of those things” – 120 will sometimes not be enough of a timeout, but 300 is apparently too much. IOW, this balancing act between too little and too much doesn’t appear to be “an exact science.”

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.