<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:
USE [SERVER_NAME]
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):
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