I have below code behind side code.gettin error :String or binary data would be truncated. The statement has been terminated.
public void gridPaymentBind(int pageIndex)
{
try
{
//open the db connection if it is closed...
if (connection.State == ConnectionState.Closed)
connection.Open();
command = new SqlCommand();
command.CommandText = "sp_Get_Payment";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
command.Parameters.AddWithValue("@PageIndex", pageIndex);
command.Parameters.AddWithValue("@PageSize", int.Parse(ddlRecordPayment.SelectedValue));
command.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
command.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
SqlParameter outParamDisc = command.Parameters.Add("@discount", SqlDbType.Float);
outParamDisc.Direction = ParameterDirection.Output;
SqlParameter outParamAmnt = command.Parameters.Add("@paidamount", SqlDbType.Float);
outParamAmnt.Direction = ParameterDirection.Output;
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds); //Here throwing an error: String or binary data would be truncated. The statement has been terminated.
IDataReader idr = command.ExecuteReader();
gridPaySearch.DataSource = idr;
gridPaySearch.DataBind();
idr.Close();
connection.Close();
int recordCount = Convert.ToInt32(command.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
lblDiscounts.Text = (command.Parameters["@discount"].Value).ToString();
lblTotPaids.Text = (command.Parameters["@paidamount"].Value).ToString();
lblDiscounts.Visible = true;
lblTotPaids.Visible = true;
}
}
//storeprocedure qury
ALTER PROCEDURE [dbo].[sp_Get_Payment]
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
,@discount float output
,@paidamount float output
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #result
(
rownum INT,
payId INT,
userid INT,
NAME VARCHAR(100),
username VARCHAR(100),
packageperiod VARCHAR(15),
packagename VARCHAR(100),
installCharge float,
amountToPay float,
balance float,
pyingamount float,
discountToPay float,
areaName varchar(50),
mobno VARCHAR(20),
createddate DATE,
empname VARCHAR(100),
PayBy VARCHAR(50),
bankName varchar(50),
chequeNo varchar(50),
chequeDate date
)
Insert INTO #result
SELECT ROW_NUMBER() OVER
(
ORDER BY [PayId] ASC
)AS rownum
,recpay.PayId
,recpay.UserId
,[Name]
,[UserName]
,[PackageName]
,[PackagePeriod]
,[InstallCharge]
,[AmountToPay]
,[PyingAmount]
,[Balance]
,[DiscountToPay]
,[AreaName]
,[MobNo]
,[CreatedDate]
,[EmpName]
,[PayBy]
,[BankName]
,[ChequeNo]
,[ChequeDate]
FROM Receive_Payment as recpay INNER JOIN
C_Register as creg ON recpay.UserId = creg.UserId INNER JOIN
Packages as pack ON recpay.PackageId = pack.PackageId INNER JOIN
Area as area ON creg.AreaId = Area.AreaId INNER JOIN
Employee as emp ON recpay.EmpId = emp.EmpId
SELECT @RecordCount = COUNT(*)
FROM #result
SELECT * FROM #result
WHERE rownum BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
select @discount = sum(discountToPay) FROM #result
select @paidamount = sum(pyingamount) FROM #result
DROP TABLE #result
print @discount
print @paidamount
END
Azaad AbbasPosted Mar 19, 2015, 7:29 AM
My table cols
PayId intUnchecked
UserIdintChecked
Namevarchar(50) Unchecked
UserNamevarchar(50) Unchecked
PackagePeriodvarchar(20) Checked
PackageNamevarchar(50)Checked
InstallChargefloatChecked
AmountToPayfloatChecked
BalancefloatChecked
PyingAmountfloatChecked
DiscountToPayfloatChecked
AreaNamevarchar(50)Checked
MobNovarchar(20)Checked
CreatedDatedateChecked
EmpNamevarchar(50)Checked
PayByvarchar(50)Checked
BankNamevarchar(50)Checked
Khargesh RajputPosted Mar 19, 2015, 7:07 AM
and check column data length is not greater than datatype in #Result
Azaad AbbasPosted Mar 19, 2015, 7:01 AM
Khargesh RajputPosted Mar 19, 2015, 6:51 AM
Insert INTO #result
check any data is inserting more then size declared for that column
either increase size for that variable or
sure that inserted data should not be more then declared size for it