Click here to Skip to main content
14,921,505 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
<pre><pre><pre><pre>I am trying to fix a bug in someone else's code. The executeNonReader() command below produces the following error:

Must declare the scalar variable "@p1"

I don't understand why or how to fix as both parameters are being passed and having looked in qp3 I can see they both have the expected values. Any help would be appreciated. thanks

Stored Procedure:
SQL
USE [SERVER_NAME]
/****** Object:  StoredProcedure [dbo].[TEST_0]    Script Date: 16/12/2019 11:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TESTshp_0] 
    @p1 nvarchar(100),  
    @n nvarchar(100) 
AS 
EXEC('
    CREATE VIEW dbo.[' + @n + '] AS 
    SELECT 
        RTRIM([FT].[FT_CODE]) As FT_UID ,  
        RTRIM([FT].[FT_NAME]) As FT_UID ,  
        RTRIM([STRL].[FT_CODE]) AS FT_CODE,  
        RTRIM([STRL].[S_D]) AS S_D,  
        RTRIM([STRL].[E_DD]) AS E_DD,  
        RTRIM([STRL].[S_AC]) AS S_AC,  
        RTRIM([STRL].[R_EN_DATE]) AS R_EN_DATE,  
        RTRIM([STRL].[R_CH_DATE]) AS R_CH_DATE,  
        RTRIM([STRL].[R_DE_DATE]) AS R_DE_DATE,  
        RTRIM([STRL].[O_SY_UID]) AS O_SY_UID,  
        RTRIM([STRL].[V_N]) AS V_N,  
        RTRIM([STRL].[R_ID]) AS R_ID,  
        RTRIM([STRL].[WA_UID]) AS WA_UID,  
        RTRIM([STRL].[D_LE]) AS D_LE,  
        RTRIM([UR].[UR_NA]) As LE_UR_UID ,  
        RTRIM([RT].[RT_NAME]) As RT_UID ,  
        RTRIM([ST].[ST_NAME]) As ST_UID ,  
        RTRIM([STRL].[STT_DATE]) AS STT_DATE,  
        RTRIM([STRL_S].[STRL_S_NAME]) As STRL_S_UID ,  
        RTRIM([STRL].[ST_TOL]) AS ST_TOL,  
        RTRIM([A].[A_NAME]) As A_UID ,  
        [STRL].[S_GE] AS S_GE,  
        RTRIM([NSTRL].[STRL_UID]) AS STRL_UID,  
        RTRIM([CT].[CT_NAME]) As CT_UID ,  
        RTRIM([STRL].[CL_UID]) AS CL_UID,  
        RTRIM([STRL].[OW_UID]) AS OW_UID,  
        RTRIM([STRL_CLAS].[STRL_CLAS_NAME]) As STRL_CLAS_UID , 
        RTRIM([STRL].[XP]) AS XP,  
        RTRIM([STRL].[CSTART_X]) AS CSTART_X,  
        RTRIM([STRL].[CSTART_Y]) AS CSTART_Y,  
        RTRIM([STRL].[CEND_X]) AS CEND_X,  
        RTRIM([STRL].[CEND_Y]) AS CEND_Y 
    FROM STRL  
    LEFT OUTER JOIN FT ON STRL.FT_UID = FT.FT_UID 
    LEFT OUTER JOIN FT ON STRL.FT_UID = FT.FT_UID 
    LEFT OUTER JOIN SC ON STRL.LE_UR_UID = SC.UR_UID 
    LEFT OUTER JOIN RT ON STRL.RT_UID = RT.RT_UID 
    LEFT OUTER JOIN STT ON STRL.ST_UID = STT.ST_UID 
    LEFT OUTER JOIN LKP_STRL_S ON STRL.STRL_S_UID = LKP_STRL_S.STRL_S_UID 
    LEFT OUTER JOIN LA ON STRL.A_UID = LA.A_UID 
    LEFT OUTER JOIN NSCOUNT ON STRL.CT_UID = NSCOUNT.CT_UID 
    LEFT OUTER JOIN STRL_CLAS ON STRL.STRL_CLAS_UID = STRL_CLAS.STRL_CLAS_UID 
WHERE  
    ([STRL].[SH_GEL].STIntersects(Geometry::STGeomFromText(' + '@p1' + ', 0).MakeValid())>0) AND  
    ([STRL].[S_GE].STGeometryType() = ''POINT'')  '
)


Exec command (Visual Basic):
VB
Using con As SqlConnection = New SqlConnection("Data Source=" + dif.ServerName + ";Initial Catalog=" + dif.DatabaseName + ";Integrated Security=True")
  Using qp3 As SqlCommand = New SqlCommand("TEST_shp_0", con)
    qp3.CommandText = "TEST_shp_0"
    qp3.CommandType = CommandType.StoredProcedure

    If Not HttpContext.Current.Session("Parameters") Is Nothing Then
       For i = 0 To HttpContext.Current.Session("Parameters").Count - 1
       Dim STRLingParam = "'" + HttpContext.Current.Session("Parameters")(i).value().ToSTRLing() + "'"
       qp3.Parameters.AddWithValue("@p" + (i + 1).ToSTRLing(), STRLingParam)
       Next
    End If

    qp3.Parameters.AddWithValue("@n", "TEST_VIEW_" & name.Replace(" ", "_").Replace(".", "_") & "_" & j)

    con.Open()
    qp3.ExecuteNonQuery()
  End Using
End Using



What I have tried:

- giving the parameters in the stored procedure a value of null
- writing in individual parameters
- using commandType = CommandTypetext, and then adding an exec command as text
Posted
Updated 12-Mar-21 2:53am
v12

My advice is to make it a stored proc and call it from your app. At least that way, you can run it in SSMS and not be forced to run your app in order to watch it fail.
   
Try debugging the calling side. Placing a breakpoint on the first line and executing each line of code one by one should show you if:
- HttpContext.Current.Session("Parameters") is nothing. This would leave the p1 parameter empty
- Or HttpContext.Current.Session("Parameters").Count is 0. This would cause the same problem

If either of these is the problem check that session parameters are set correctly for your HttpContext
   
Comments
Member 14476420 19-Dec-19 3:53am
   
Thanks,
HttpContext.Current.Session("Parameters")
contains the the expected value and the lines within the 'if' statement gets hit
Q: What happens if HttpContext.Current.Session("Parameters") is nothing?
A: Required parameter @p1 is not added to the SQL Command
VB
If Not HttpContext.Current.Session("Parameters") Is Nothing Then
  For i = 0 To HttpContext.Current.Session("Parameters").Count - 1
    Dim stringParam = "'" + HttpContext.Current.Session("Parameters")(i).value().ToString() + "'"
     qp3.Parameters.AddWithValue("@p" + (i + 1).ToString(), stringParam)
  Next
End If
   
Comments
Member 14476420 19-Dec-19 3:51am
   
Thanks, I can confirm that
HttpContext.Current.Session("Parameters")
is not nothing and contains the expected value
No idea why this has popped back into the "active" list, or whether you're still looking for a solution. But the problem is obvious - the query you are passing to EXEC doesn't know anything about the parameters from the calling procedure.

To pass the parameter properly, you need to use sp_executesql[^]
SQL
ALTER PROCEDURE [dbo].[TESTshp_0] 
    @p1 nvarchar(100),  
    @n nvarchar(100) 
As
    EXEC sp_executesql N'CREATE VIEW dbo.' + QUOTENAME(@n) + ' AS 
    SELECT 
        ...
    FROM
        ...
    WHERE  
        ([STR].[SHAPE_GEOMETRY].STIntersects(Geometry::STGeomFromText(@p1, 0).MakeValid()) > 0) 
    AND  
        ([STR].[SHAPE_GEOGRAPHY].STGeometryType() = ''POINT'')
    ;',
    N'@p1 nvarchar(100)',
    @p1 = @p1;
   
Comments
Member 14476420 12-Mar-21 8:55am
   
The post likely popped up again because I had to change the column names for security reasons. The problem was resolved a while back. Thank you

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