troubleshooting Question

SQLException: Must declare the scalar variable

Avatar of byrd48
byrd48 asked on
Microsoft SQL ServerASP.NET
3 Comments1 Solution1173 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
hongjun

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros