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