编辑功能查询问题解决

问题点：

 报错：
“System.InvalidCastException”类型的异常在 System.Data.dll 中发生，但未在用户代码中进行处理 其他信息: 指定的转换无效。
解决方法：

修改前的代码示例

 public AccessoryInfo GetInfo(Int32 accessoryId)
        {
            AccessoryInfo entity = null;

            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@FieldValue", SqlDbType.NVarChar, 50),
                new SqlParameter("@IsByID", SqlDbType.Bit)
            };

            parms[0].Value = accessoryId;
            parms[1].Value = true;

            using (SqlDataReader rdr = SQLHelper.ExecuteReaderStoredProcedure(SQLHelper.MESConnString, "Prod_Accessory_GetInfo", parms))
            {
                if (rdr.Read())
                {
                    //entity = new AccessoryInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4),
                    //    rdr.GetInt32(5), rdr.GetDouble(6), rdr.GetDateTime(7), rdr.GetString(8), rdr.GetDecimal(9),
                    //    rdr.GetDecimal(10), rdr.GetDateTime(11), rdr.GetString(12), rdr.GetDateTime(13), rdr.GetDateTime(14), rdr.GetString(15), rdr.GetDateTime(20), rdr.GetDateTime(21), rdr.GetDateTime(22));
                    entity = new AccessoryInfo();
                    entity.AccessoryId = rdr.GetInt32(0);
                    entity.AccessoryCodoe = rdr.GetString(1);
                    entity.AccessoryName = rdr.GetString(2);
                    entity.Lot = rdr.GetString(3);
                    entity.SerialNumber = rdr.GetString(4);
                    entity.Status = rdr.GetInt32(5);
                    entity.UserTime = rdr.GetFloat(6);
                    entity.LoseTime = rdr.GetDateTime(7);
                    entity.SupplierCode = rdr.GetString(8);
                    entity.InStockQty = rdr.GetDecimal(9);
                    entity.CurrentQty = rdr.GetDecimal(10);
                    entity.StartThawTime = rdr.GetDateTime(11);
                    entity.CreateBy = rdr.GetString(12);
                    entity.CreateTime = rdr.GetDateTime(13);
                    entity.UnsealTime = rdr.GetDateTime(14);
                    entity.AccessoryTypeName = rdr.GetString(15);
                    entity.SupplierName = rdr.GetString(16);
                    entity.AccessoryType = rdr.GetInt32(17);
                    entity.ProdDateTime = rdr.GetDateTime(18);
                    entity.ItemId = rdr.GetInt32(19);
                    entity.EndThawTime = rdr.GetDateTime(20);
                    entity.EndStirTime = rdr.GetDateTime(21);
                    entity.KaifengTime= rdr.GetDateTime(22);
                }
                rdr.Close();
            }
            return entity;
        }

修改后的代码示例

public AccessoryInfo GetInfo(Int32 accessoryId)
{
    AccessoryInfo entity = null;

    SqlParameter[] parms = new SqlParameter[]{
        new SqlParameter("@FieldValue", SqlDbType.NVarChar, 50),
        new SqlParameter("@IsByID", SqlDbType.Bit)
    };

    parms[0].Value = accessoryId;
    parms[1].Value = true;

    using (SqlDataReader rdr = SQLHelper.ExecuteReaderStoredProcedure(SQLHelper.MESConnString, "Prod_Accessory_GetInfo", parms))
    {
        if (rdr.Read())
        {
            entity = new AccessoryInfo();
            entity.AccessoryId = rdr.GetInt32(rdr.GetOrdinal("AccessoryId"));
            entity.AccessoryCodoe = Convert.ToString(rdr["AccessoryCodoe"]);
            entity.AccessoryName = Convert.ToString(rdr["AccessoryName"]);
            entity.Lot = Convert.ToString(rdr["Lot"]);
            entity.SerialNumber = Convert.ToString(rdr["SerialNumber"]);
            entity.Status = Convert.ToInt32(rdr["Status"]);
            entity.UserTime = Convert.ToDouble(rdr["UserTime"]);
            entity.LoseTime = Convert.ToDateTime(rdr["LoseTime"]);
            entity.SupplierCode = Convert.ToString(rdr["SupplierCode"]);
            entity.InStockQty = Convert.ToDecimal(rdr["InStockQty"]);
            entity.CurrentQty = Convert.ToDecimal(rdr["CurrentQty"]);
            entity.StartThawTime = Convert.ToDateTime(rdr["StartThawTime"]);
            entity.CreateBy = Convert.ToString(rdr["CreateBy"]);
            entity.CreateTime = Convert.ToDateTime(rdr["CreateTime"]);
            entity.UnsealTime = Convert.ToDateTime(rdr["UnsealTime"]);
            entity.AccessoryTypeName = Convert.ToString(rdr["AccessoryTypeName"]);
            entity.SupplierName = Convert.ToString(rdr["SupplierName"]);
            entity.AccessoryType = Convert.ToInt32(rdr["AccessoryType"]);
            entity.ProdDateTime = Convert.ToDateTime(rdr["ProdDateTime"]);
            entity.ItemId = Convert.ToInt32(rdr["ItemID"]);
            entity.EndThawTime = Convert.ToDateTime(rdr["EndThawTime"]);
            entity.EndStirTime = Convert.ToDateTime(rdr["EndStirTime"]);
            entity.KaifengTime = Convert.ToDateTime(rdr["KaifengTime"]);
        }
        rdr.Close();
    }
    return entity;
}

 

之后报错：
entity.EndThawTime = Convert.ToDateTime(rdr["EndThawTime"]); entity.EndStirTime = Convert.ToDateTime(rdr["EndStirTime"]); entity.KaifengTime = Convert.ToDateTime(rdr["KaifengTime"]);

报错： “System.InvalidCastException”类型的异常在 mscorlib.dll 中发生，但未在用户代码中进行处理 其他信息: 对象不能从 DBNull 转换为其他类型。

处理方法：

修改后的 SQL 查询

SELECT 
    [AccessoryId], 
    [AccessoryCodoe], 
    [AccessoryName], 
    [Lot], 
    [SerialNumber], 
    t.[Status], 
    [UserTime], 
    ISNULL([LoseTime], '1900-01-01') AS [LoseTime], 
    [SupplierCode], 
    [InStockQty], 
    [CurrentQty], 
    ISNULL([StartThawTime], '1900-01-01') AS [StartThawTime], 
    t.[CreateBy], 
    t.[CreateTime], 
    ISNULL([UnsealTime], '1900-01-01') AS [UnsealTime], 
    t1.AccessoryTypeName,
    t2.VendorName AS SupplierName,
    t.AccessoryType,
    t.ProdDateTime,
    bi.ItemID,
    ISNULL(t.EndThawTime, '1900-01-01') AS EndThawTime,
    ISNULL(t.EndStirTime, '1900-01-01') AS EndStirTime,
    ISNULL(t.KaifengTime, '1900-01-01') AS KaifengTime 
FROM [Prod_Accessory] t
JOIN dbo.Prod_AccessoryType t1 ON t1.AccessoryTypeId = t.AccessoryType
JOIN dbo.Basal_Supplier t2 ON t2.VendorCode = t.SupplierCode
JOIN dbo.Basal_Item AS bi ON t.AccessoryCodoe = bi.ItemCode
WHERE [AccessoryId] = 52;