Truncated data even after altering column

Viewed 33

On production we found bug for truncating data on specific column. I investigated and saw that the we are taking data from procedure from specific table which had column varchar 150 to table with column varchar(50). I altered the table I want to insert data to varchar(150) and still getting the same error. Erorr on picture below.

Data table is created from the data I have got from SP: CreateAgingRepTvp(dataFromSp), then result I set up into parameter and should be inserted into table which column I have altered to accept 150 characters. But still I am getting the data is truncated on Data Table, when I have tried to set max length property on specific data table insert not working any more but I don't get any error. On SQL Server when I try to add manually the same data to the table I have altered it works.

 private DataTable CreateAgeingReportTvp(List<AgeingReportItem> reportItems, Guid reportId)
    {
        DataTable ageingTvp = new DataTable();
        ageingTvp.Columns.Add("Id", typeof(Guid));
        ageingTvp.Columns.Add("ReportId", typeof(Guid));
        ageingTvp.Columns.Add("PrincipalId", typeof(string));
        ageingTvp.Columns.Add("PrincipalName", typeof(string));
        ageingTvp.Columns.Add("Department", typeof(string));
        ageingTvp.Columns.Add("BusinessArea", typeof(string));
        ageingTvp.Columns.Add("AmtLessThan60", typeof(decimal));
        ageingTvp.Columns.Add("Amt61To90", typeof(decimal));
        ageingTvp.Columns.Add("Amt91To180", typeof(decimal));
        ageingTvp.Columns.Add("Amt181To365", typeof(decimal));
        ageingTvp.Columns.Add("AmtGreaterThan365", typeof(decimal));
        ageingTvp.Columns.Add("TotalAR", typeof(decimal));
        ageingTvp.Columns.Add("AdvanceReceived", typeof(decimal));
        ageingTvp.Columns.Add("Provision", typeof(decimal));
        ageingTvp.Columns.Add("NetReceivable", typeof(decimal));
        ageingTvp.Columns.Add("PrevMonthBalance", typeof(decimal));
        ageingTvp.Columns.Add("Remarks", typeof(string));
        foreach (var item in reportItems)
        {
            DataRow row = ageingTvp.NewRow();
            row["Id"] = item.Id;
            row["ReportId"] = item.ReportId = reportId;
            row["PrincipalId"] = item.PrincipalId;
            row["PrincipalName"] = item.PrincipalName.Trim();
            row["Department"] = item.Department;
            row["BusinessArea"] = item.BusinessArea;
            row["AmtLessThan60"] = item.AmtLessThan60 ?? 0;
            row["Amt61To90"] = item.Amt61To90;
            row["Amt91To180"] = item.Amt91To180;
            row["Amt181To365"] = item.Amt181To365;
            row["AmtGreaterThan365"] = item.AmtGreaterThan365;
            row["TotalAR"] = item.TotalAR;
            row["AdvanceReceived"] = item.AdvanceReceived ?? 0;
            row["Provision"] = item.Provision;
            row["NetReceivable"] = item.NetReceivable;
            row["PrevMonthBalance"] = item.PrevMonthBalance;
            row["Remarks"] = item.Remarks;
        }

        return ageingTvp;
    }

  using (TransactionScope tran = new TransactionScope())
        {
            var ageingReportId = SaveAgeingReport(columnAggregates, arAggregates, filter);

            using (DataTable myTvpTable = CreateAgeingReportTvp(reportItems, ageingReportId))
            {
                SqlParameter parameter = new SqlParameter
                {
                    ParameterName = "@tvpValues",
                    Value = myTvpTable,
                    SqlDbType = SqlDbType.Structured,
                    TypeName = "AgeingReportTvp"
                };
                sqlHelper.ExecuteNonQuery("INSERT INTO AgeingReportItem SELECT * FROM @tvpValues;", new List<SqlParameter> { parameter });
            }
            var jobList = reportItems.SelectMany(x => x.JobList).ToList();

            using (DataTable detailsTvpTable = CreateAgeingReportDetailsTvp(jobList))
            {
                SqlParameter detailsParameter = new SqlParameter
                {
                    ParameterName = "@detailsTvpValues",
                    Value = detailsTvpTable,
                    SqlDbType = SqlDbType.Structured,
                    TypeName = "AgeingReportDetailsTvp"
                };
                sqlHelper.ExecuteNonQuery("INSERT INTO AgeingReportItemDetails SELECT * FROM @detailsTvpValues;", new List<SqlParameter> { detailsParameter });
            }

        tran.Complete();
    }
}

enter image description here

0 Answers
Related