Click here to Skip to main content
15,394,479 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Updated 21-Jun-22 21:44pm
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.

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
   
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.
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;
   
v2

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