• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1132
  • Last Modified:

SQLException: Must declare the scalar variable

SQLException: Must declare the scalar variable "@resellerRoleID"

Hi,
I'm getting a strange error as shown above with the code outlined below.  Running through the debugger, I can see a value assigned to intResellerRoleID, so I know the function is receiving the value.  Does this error message come from the vb code, or from the sql stored procedure? Thanks

Private Sub DeleteTestAssoc(ByVal intResellerRoleID As Integer, ByVal strTestIDs As String)
        Dim objConn As SqlConnection = Utilities.GetConn()
        Dim objComm As SqlCommand = New SqlCommand()
        With objComm
            .Connection = objConn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "dbo.DeleteResellerRoleTestAssoc"
            .Parameters.AddWithValue("@resellerRoleID", intResellerRoleID)
            .Parameters.AddWithValue("@testIDs", strTestIDs)
        End With
        objComm.ExecuteNonQuery()
        Utilities.CloseConn(objConn)
        objConn = Nothing
        objComm = Nothing
    End Sub

USE [MeasureUp_Master]
GO
/****** Object:  StoredProcedure [dbo].[DeleteResellerRoleTestAssoc]    Script Date: 07/22/2007 08:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DeleteResellerRoleTestAssoc](
      @resellerRoleID int,
      @testIDs nvarchar(4000) = ''
)
AS
BEGIN
      IF @testIDs <> ''
            BEGIN
                  DECLARE @sql varchar(8000)
                  
                  SET @sql = 'DELETE FROM ResellerRolesAssoc WHERE ResellerRoleID = @resellerRoleID AND TestID NOT IN (' + @testIDs + ')'
                  EXEC (@sql)
            END
      ELSE
            BEGIN
                  DELETE FROM ResellerRolesAssoc
                  WHERE ResellerRoleID = @resellerRoleID
            END
END
0
byrd48
Asked:
byrd48
  • 2
1 Solution
 
hongjunCommented:
try change
                  SET @sql = 'DELETE FROM ResellerRolesAssoc WHERE ResellerRoleID = @resellerRoleID AND TestID NOT IN (' + @testIDs + ')'

to
                  SET @sql = 'DELETE FROM ResellerRolesAssoc WHERE ResellerRoleID = ' + @resellerRoleID + ' AND TestID NOT IN (' + @testIDs + ')'
0
 
dbbishopCommented:
Actually,

SET @sql = 'DELETE FROM ResellerRolesAssoc WHERE ResellerRoleID = ' + @resellerRoleID + ' AND TestID NOT IN (' + @testIDs + ')'

should be:
SET @sql = 'DELETE FROM ResellerRolesAssoc WHERE ResellerRoleID = ' + CAST(@resellerRoleID AS NVARCHAR(20) + ' AND TestID NOT IN (' + @testIDs + ')'

since @resellerRoleID is INT.

Is your string @testIDs delimited values when it is built? In other words, if there are 4 test IDs with values of ABC, DEF, GHI and JKL, then the value of strTestIDs in your VB method should be "'ABC', 'DEF', 'GHI', 'JKL'" (each ID containing a single quote (') and the whole string within a double-quote ("). If not, you can pass it as a non-delimited string, but you'd have to write some code in T-SQL to parse the string and add delimiters.
0
 
dbbishopCommented:
Sorry - CAST(@resellerRoleID AS NVARCHAR(20) should be CAST(@resellerRoleID AS NVARCHAR(20))
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now