Sqlcommand problem - parameter not provided

Question

0.00/5 (No votes)

See more:

Windows 7, Visual Studio 2017, MVC5 app using .Net Framework 4.8:

I'm trying to call a stored proc that accepts a single nvarchar(450) parameter. When I try to call that stored proc from my code, I get this:

Procedure or function 'StoredProcName' expects parameter '@paramName', which was not supplied.

The SqlParameter array inside the SqlCommand object does indeed contain a single parameter with the correct name, with the expected value, and of the expected SqlDbType at the time I call ExecuteQuery. I examined the above with the debugger, and in an immediate console.

The stored proc does indeed have a single parameter defined like so:

SQL

@paramName NVARCHAR(450)

When I execute the stored proc in SSMS, it works as expected.

What I have tried:

I tried the things described in the question. I present the following observed facts:

In the app:
- The SqlCommand.Parameters object contains a single parameter (as intended)
- The parameter is of the correct type (nvarchar)
- The parameter has the correct name ("@paramName")
- The parameter has the expected string value
- The connection to the database succeeds
- The stored proc was found in the database

In the database:
- The stored proc has a single parameter, of type nvarchar
- The stored proc works when I execute it in SSMS with the same parameter value used in the app

Posted 31-May-22 11:52am

#realJSOP

Updated 1-Jun-22 2:05am

v3

Add a Solution

Comments

OriginalGriff 1-Jun-22 0:49am

Without a relevant code fragment?
We can't do anything ...

2 solutions

Solution 1

As I said, without seeing your code, we can't really help - but one thing occurs: if your code is passing a parameter value to SQL but you get this error, it is normally because the value passed is null - so use the debugger to see exactly what is begin passed in the relevant SqlCommand.Parameters.Add / SqlCommand.Parameters.AddWithValue call.
If it's null (and that's different from an empty string as you know but some don't) that'll cause your problem.

Of course, if you aren't calling Add / AddWithValue then that'll do it as well, but you're far to experienced to have missed that! :laugh:

Posted 31-May-22 18:55pm

OriginalGriff

Comments

#realJSOP 1-Jun-22 5:05am

In my question, I noted that I did in fact examine the cmd.parameters object, that it contained the correctly named parameter and it had the expected value, and was the correct SqlDbType. I even examined all the stuff I could think of in an immediate window. Everything seems fine.

OriginalGriff 1-Jun-22 6:13am

Connection string? You are connected to the right DB? I'm guessing here ...

#realJSOP 1-Jun-22 6:38am

Yes. I also tried running the Sql Profiler to see what the database was trying to do, and when I copy the code from the profiler result window into SSMS, it fails with the same error, yet executing the stored proc with "EXEC" still works fine.

BTW, the profiler is trying to do this:

exec sp_executesql N'dbo.UserProfileGet',N'@userID nvarchar(36)',@userID=N'36-character string'

#realJSOP 1-Jun-22 7:00am

More weirdness - If I make the parameter in the stored proc nullable, it works. I shouldn't have to do that.

OriginalGriff 1-Jun-22 7:13am

Why are you using sp_executesql? It expects a SQL statement, not an SP name which may be confusing it.
Try:

exec dbo.UserProfileGet @userID=N'36-character string'

[edit] Forgot to remove enough from your SQL [/edit]

#realJSOP 1-Jun-22 7:19am

I'm not using that - that's what the profiler was showing as the command to be executed

Update - while I don't get the exception any more, it seems that the parameter is being ignore or is unseen by the database, because I put in a fake record and it's not returning anything. FML.

The edited sql that you showed works (that's what "Execute stored procedure" does).

#realJSOP 1-Jun-22 7:41am

If I modify the code that is string to run to the following, it works, but if sql server isn't running it that way, it's not going to do me any good.

exec sp_executesql N'dbo.UserProfileGet @UserID=@userID', N'@userID NVARCHAR(36)',@userID=N'4a39a408-4411-40a0-b916-7122946d3f65'

#realJSOP 1-Jun-22 8:03am

I posted the solution as a new answer.

Add a Solution

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

#realJSOP · Accepted Answer · 2022-06-01T02:05:00

It turns out this was a stupid programmer trick (my face is red).

I was calling the wrong DAL method (face is red). I was calling ExecuteQuery with sets the command type to Text. I should have been calling ExecuteStoredProc which sets the command type to StoredProcedure.

When I called the correct method, everything fell into place. :/

Sqlcommand problem - parameter not provided

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Sqlcommand problem - parameter not provided

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us