While debugging in WCF method getting error such as {"must declare the scalar variable \"@POL_NBR\"."}

Question

1.00/5 (1 vote)

See more:

Error type is System.Data.Sqlclient.SqlException - {"Must declare the scalar variable \"@POL_NBR\"."}

I am have one column name with POL_NBR in gridview in Winform and I am trying to debug while updating few columns value meanwhile this error is throwing up like must declare a scalar variable. But I have already declared it in my SPs and also naming convention perfect there is no spelling mistake.

What I have tried:

Here it is in my WCF method
sqlParameter[2] = new SqlParameter("@POL_NBR", SqlDbType.VarChar,50, ParameterDirection.Input, false, 10, 0, "POL_NBR", DataRowVersion.Current, POL_NBR);

and My SP is as below
CREATE PROCEDURE [UpdateInputData]
@tbl_pol_id int,
@LOB varchar (50) = null,
@POL_NBR varchar(50)=null

AS
BEGIN
BEGIN TRANSACTION

Declare @LOBID INT

SELECT @LOBID = TBL_LOB_TYP_LKUP_ID FROM TBL_LOB_TYP_LKUP WHERE Upper(ltrim(rtrim(LOB_NM))) = Upper(ltrim(rtrim(@LOB)))

BEGIN

Declare @sql_Query varchar(8000) = null

set @sql_Query = 'Update TPCA set'

if(@POL_NBR is not null and @POL_NBR <> '')
Begin
set @sql_Query = @sql_Query + ' TPCA.POL_NBR = @POL_NBR'
End

Posted 8-Jun-22 3:14am

Thirumadhi T Johnson

Updated 21-Jun-22 21:44pm

Add a Solution

Comments

j snooze 8-Jun-22 17:50pm

Well, not that I understand what your requirements are, but I'm guessing the issue is the @sql_Query you are trying to put together. The @POL_NBR variable needs to be outside the apostrophe like this
set @sql_Query = @sql_Query + ' TPCA.POL_NBR = ' + @POL_NBR, if @POL_NBR is a number, then thats fine otherwise you'll have to concatenate the apostrophes around the variable if its a string.

Although any kind of concatenation of SQL to execute from passed in variables should make you think about coming up with a different way to put this together as that can allow for sql injection even if you are passing the variable in as a parameter from .NET, if you're using that parameter to build an SQL statement inside with other SQL to be executed somewhere else, I'm sure there are people creative enough to find a way to break that.

I admit, I feel like this isn't the entire code for either piece so there could be something I'm missing. Hope the above answer gets you further along.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

#realJSOP · Answer 1 · 2022-06-21T07:27:00

Solution 1

Your query is malformed...

SQL

SET @sql_Query = 'Update TPCA set '

IF ISNULL(@POL_NBR, '') <> ''
BEGIN
    SET @sql_Query = @sql_Query + 'TPCA.POL_NBR=''' + @POL_NBR + '''
END

Posted 21-Jun-22 7:27am

#realJSOP

Comments

Richard Deeming 22-Jun-22 3:45am

Nice SQL Injection vulnerability you've got there. :)

Dynamic SQL parameters need to be passed properly using sp_executesql[^].

#realJSOP 22-Jun-22 5:37am

I was just showing him what he did wrong and why he wasn't getting the result he was expecting. Personally, I abhor dynamic sql, and avoid it at all costs.

Richard Deeming · Answer 2 · 2022-06-21T21:44:00

The code in solution 1 is vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

In the case of dynamic SQL within a stored procedure, that means using sp_executesql[^] with proper parameters.

SQL

DECLARE @sql_Query nvarchar(8000);
SET @sql_Query = N'Update TPCA set ';

If @POL_NBR Is Not Null And @POL_NBR <> ''
Begin
    SET @sql_Query = @sql_Query + N' TPCA.POL_NBR = @POL_NBR';
End;

...

EXEC sp_executesql @sql_Query, 
    N'@tbl_pol_id int, @LOB varchar (50), @POL_NBR varchar(50)',
    @tbl_pol_id = @tbl_pol_id,
    @LOB = @LOB,
    @POL_NBR = @POLNBR;