Click here to Skip to main content
15,885,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

SQL
USE [TestVariousInfo_WithData]
GO
/****** Object:  StoredProcedure [dbo].[sp_InvoiceReportItems]    Script Date: 06-10-2021 09:46:34 AM ******/
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,
		--</Invoice detail>
		--<Item Detail>
		aid.*
		--</Item Detail>
	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



C#
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);
        }
Posted
Updated 7-Oct-21 1:02am

When you create SqlParameter objects, ALWAYS specify the SQL data type of what you're passing in.

From the documentation of the SqlParameter constructor you're using:
Quote:
When you specify an Object in the value parameter, the SqlDbType is inferred from the Microsoft .NET Framework type of the Object.

Use caution when you use this overload of the SqlParameter constructor to specify integer parameter values. Because this overload takes a value of type Object, you must convert the integral value to an Object type when the value is zero, as the following C# example demonstrates.
Take a close look at what you're passing into your SqlParameter objects.

For the Reduction parameter, your stored procedure is looking for a Decimal value, and in the C# code, you're passing in a string.
 
Share this answer
 
Comments
Devendra Sarang 7-Oct-21 7:46am    
converted all the SqlParameter data type like

Stored procedure data type

@JobTrackDetailId BIGINT,
@InvoiceNo NVARCHAR(MAX),
@Reduction DECIMAL(10,2)=0.0,
@ItemReduc BIT=0,
@TimeReduc BIT=0,
@ExpReduc BIT=0

From my code

double s1 = 1.0;

new SqlParameter("@JobTrackDetailId",Convert.ToDecimal(JobtrackInvoiceId)),
new SqlParameter("@InvoiceNo", Convert.ToString(InvoiceNo)),
new SqlParameter("@Reduction", Convert.ToDecimal(s1)),
new SqlParameter("@ItemReduc", Convert.ToBoolean(chkItemReduc.CheckState)),
new SqlParameter("@TimeReduc", Convert.ToBoolean(chkTimeReduc.CheckState)),
new SqlParameter("@ExpReduc", Convert.ToBoolean(chkExpReduc.CheckState))

but still getting parameter missing error
Dave Kreskowiak 7-Oct-21 7:56am    
Again, you might want to try the constructors for SqlParameter that lets you specify the SqlDbType for each parameter.

You're not doing that. You're just using Convert and allowing the SqlParameter to try and determine the SqlDbType itself from what you're passing in. It's basically guessing and getting it wrong.

SqlParameter Constructor (System.Data.SqlClient) | Microsoft Docs[^]
Devendra Sarang 7-Oct-21 8:40am    
i think it is something else which is throwing this error

because i have changed the code as per your suggestions but still it is saying

'The parameterized query '(@JobTrackDetailId bigint,@InvoiceNo ntext,@ItemReduc bit,@TimeR'
expects the parameter '@JobTrackDetailId', which was not supplied.'




new SqlParameter("@JobTrackDetailId",SqlDbType.BigInt,JobtrackInvoiceId),
new SqlParameter("@InvoiceNo",SqlDbType.NText,1),
new SqlParameter("@Reduction",SqlDbType.Decimal,1),
new SqlParameter("@ItemReduc",SqlDbType.Bit,Convert.ToInt32(chkItemReduc.CheckState)),
new SqlParameter("@TimeReduc",SqlDbType.Bit,Convert.ToInt32(chkTimeReduc.CheckState)),
new SqlParameter("@ExpReduc",SqlDbType.Bit,Convert.ToInt32(chkExpReduc.CheckState)),
Dave Kreskowiak 7-Oct-21 8:50am    
Looking are how you're calling the stored procedure, it looks like you may have to change the "Sql" you're passing to "GetListDT":
...StMethod.GetListDT<string>("sp_InvoiceReportItems", ... 
to something more like this
...StMethod.GetListDT<string>("exec sp_InvoiceReportItems @JobTrackDetailId, @InvoiceNo, @Reduction, @ItemReduc, @TimeReduc, @ExpReduc", ...
Devendra Sarang 7-Oct-21 10:42am    
not working. I think i have to rewrite the stored procedure and C# code from the scratch.

StMethod.GetListDT<string>("exec sp_InvoiceReportItems @JobTrackDetailId, @InvoiceNo, @Reduction, @ItemReduc, @TimeReduc, @ExpReduc",

this one also giving same error
At a guess, one of the value you are passing to the SP is NULL - but we can't tell without your code running with your data.

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it then a quick Google for "Visual Studio debugger" should give you the info you need.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!
 
Share this answer
 
Comments
Devendra Sarang 7-Oct-21 6:58am    
i am already using it.
See error is coming for "JobTrackDetailId" field.

and this is what i am getting at the time of debug.

SQL
Paramneter
Paramneter with value


One more question, @JobTrackDetailId data type in stored procedure is BIGINT
and i am passing data to it using "Convert.ToInt64(JobtrackInvoiceId)"
so is it possible that data type may be creating some issue

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900