I am trying to call one sql server stored procedure from my C# code.
I am passing all required SqlParameter but still getting "Procedure or function expects parameter which was not supplied" error
First one is stored procedure which i am using.
2nd one is c# code from which i am calling it.
What I have tried:
USE [TestVariousInfo_WithData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_InvoiceReportItems]
@JobTrackDetailId BIGINT,
@InvoiceNo NVARCHAR(MAX),
@Reduction DECIMAL(10,2)=0.0,
@ItemReduc BIT=0,
@TimeReduc BIT=0,
@ExpReduc BIT=0
AS
BEGIN
SET NOCOUNT ON;
;WITH
cet_AllItemDetail AS
(
SELECT
jtird.JobTrackDetailID,
jtird.Description,
CONVERT(VARCHAR(20), jtird.Date, 101) as Date,
Convert(nvarchar,jtird.Hrs) as Hrs,
dbo.fn_Reduction(jtird.Rate,@Reduction,@ItemReduc) as Rate,
jtird.JobTrackSubName,
jtird.byname,
dbo.fn_Reduction(jtird.Amount,@Reduction,@ItemReduc)as Amount,
'' as Clienttext,
'' as Expenses,
'Item' as ReportType
FROM dbo.JobTrackInvoiceRateDetail jtird LEFT OUTER JOIN dbo.MasterTrackSubItem mtsi
ON jtird.TrackSubID = mtsi.Id
WHERE jtird.JobTrackDetailID=@JobTrackDetailId
UNION ALL
SELECT
ti.JobTrackDetailID,
ti.Description,
CONVERT(VARCHAR(20), ti.Date, 101) as Date,
ti.Time as Hrs,
dbo.fn_Reduction(ti.Rate,@Reduction,@TimeReduc) as Rate,
'' as JobTrackSubName,
Name as byname ,
'' as Amount,
'' as Clienttext ,
'' as Expenses,
'Time' as ReportType
FROM dbo.CRVTimeInvoice ti
WHERE ti.JobTrackDetailID=@JobTrackDetailId
UNION ALL
SELECT
ei.JobTrackDetailID,
ei.Description,
CONVERT(VARCHAR(20), ei.Date, 101) as Date,
'' as Hrs,
'' as Rate,
'' as JobTrackSubName,
ei.byname,
'' as Amount,
'' as Clienttext,
dbo.fn_Reduction(Expenses,@Reduction,@TimeReduc) AS Expenses,
'Expenses' as ReportType
FROM dbo.CRVExpensesInvoice ei
WHERE ei.JobTrackDetailID=@JobTrackDetailId
)
SELECT
jtid.InvoiceNo,
convert(varchar(12),jtid.InvoiceDate,101)as InvoiceDate,
jtid.Jobdescription,
jtid.DueDate,
jtid.Address,
jtid.PhoneNo,
jtid.FaxNo,
jtid.Email,
jtid.JobTrackDetailID,
j.JobNumber,
jtid.PONo,
jtid.PaymentCr,
aid.*
FROM
dbo.JobTrackInvoiceDetail jtid INNER JOIN dbo.JobList j ON jtid.JobListID = j.JobListID
INNER JOIN cet_AllItemDetail aid ON jtid.JobTrackDetailID=aid.JobTrackDetailID
WHERE
jtid.JobTrackDetailID=@JobTrackDetailId AND jtid.InvoiceNo=@InvoiceNo
END
DataTable DT = null;
DT = StMethod.GetListDT<string>("sp_InvoiceReportItems", new List<SqlParameter>(new[]
{
new SqlParameter("@JobTrackDetailId",Convert.ToInt64(JobtrackInvoiceId)),
new SqlParameter("@InvoiceNo", InvoiceNo),
new SqlParameter("@Reduction", txtReduction.Value),
new SqlParameter("@ItemReduc", Convert.ToInt32(chkItemReduc.CheckState)),
new SqlParameter("@TimeReduc", Convert.ToInt32(chkTimeReduc.CheckState)),
new SqlParameter("@ExpReduc", Convert.ToInt32(chkExpReduc.CheckState))
}
));
public static DataTable GetListDT<T>(string Sql, List<SqlParameter> Param, eDatabase _dbType = eDatabase.VariousInfo) where T : class
{
List<T> result;
try
{
using (DbContext db = GetDbConnection(_dbType))
{
result = db.Database.SqlQuery<T>(Sql, Param.ToArray()).ToList();
}
}
catch (Exception)
{
throw;
}
return GenericHelper.ToDataTable(result);
}