Incorrect syntax near?

Question

I work with rdl report in Visual studio 2010.

I write Query and Refresh field then it prompt " Define Parameter for Query "

like this

Parameter Name | Parameter Value

and I set this value

@proj | Parameters!project.Value

then click OK

I get this Error

Incorrect Syntax near '!'

I don't know whrere '!' in my Query

This is example my Query in rdl dataset

DECLARE @sqlCom NVARCHAR(MAX)
SET @sqlCom = '
   SELECT name
   FROM customer
   WHERE id IN ('+ @proj +') 
   '

IF (LEN(@phoneNo) > 0)
BEGIN
    SET @sqlCom = @sqlCom + ' AND phoneNo LIKE ''%'+@phoneNo+'%'' '
END

IF (LEN(@age) > 0)
BEGIN
    SET @sqlCom = @sqlCom + ' AND age LIKE ''%'+@age+'%'' '
END

EXECUTE(@sqlCom)

So. Only @sqlCom is SQL variable @proj , @phoneNo , @age is rdl Parameters

anyone help me how to fix this problem

Thank you.

Yikes...this is wide open to sql injection. You need to parameterize your queries immediately if not sooner. You should also take a look at this article which explains a number of ways to deal with performance issues for these types of queries. sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries — Sean Lange
My guess is "Incorrect Syntax near '!'" is referring to the ! in "Parameters!project.Value". Try running the query again with a hard coded parameter and see what happens. — Bobby

Pரதீப் Pரதீப் · Accepted Answer · 2014-11-17T15:50:14

Problem is you are missing single qoutes in '+ @proj +'. Try something like this

DECLARE @sqlCom NVARCHAR(MAX)
SET @sqlCom = '
   SELECT name
   FROM customer
   WHERE id IN ('''+ @proj +''') 
   '

IF (LEN(@phoneNo) > 0)
BEGIN
    SET @sqlCom = @sqlCom + ' AND phoneNo LIKE ''%'+@phoneNo+'%'' '
END

IF (LEN(@age) > 0)
BEGIN
    SET @sqlCom = @sqlCom + ' AND age LIKE ''%'+@age+'%'' '
END

EXECUTE(@sqlCom)

Incorrect syntax near?

1 Answers