none
Must declare the scalar variable error in Microsoft Visual Studio

    Question

  • I'm creating a report using Microsoft Visual Studio 2008 and my query union query works fine if I hardcode values in the query.   However, when I try to use a parameters for the values in the query I get the error ERROR [42000] [Microsoft] [ODBC SQL Server Driver] [SQL Server] Must declare the scalar variable "@CollID1"  .  ERROR [42000] [Microsoft] [ODBC SQL Server Driver] [SQL Server] Must declare the scalar variable "@CollID2".     I have declared the parameters, so I don't understand why I am getting the error. 
    Wednesday, June 20, 2012 8:44 PM

Answers

  • With ODBC (and OLE DB), specify parameter markers as a question mark instead of variable names as the link Tony referenced suggestes. The parameters are mapped by ordinal position rather than by name.

    SELECT     c.[Device Manufacturer] AS 'DEVICE MANUFACTURER', c.[Device Product Name] AS 'DEVICE PRODUCT NAME', c.[Device Model] AS 'DEVICE MODEL',
                           SUM(c.[Collection 1]) AS 'COLLECTION 1', SUM(c.[Collection 2]) AS 'COLLECTION 2', (SUM(c.[Collection 2]) - SUM(c.[Collection 1]))
                           AS 'COLLECTION DELTA'
     FROM         (SELECT     a.[Device Manufacturer] AS 'DEVICE MANUFACTURER', a.[Device Product Name] AS 'DEVICE PRODUCT NAME',
                                                   a.[Device Model] AS 'DEVICE MODEL', COUNT(a.[Device Record ID]) AS 'Collection 1', 0 AS 'Collection 2'
                            FROM          BDNA_Reporting.dbo.Device A
                           WHERE      a.[collection id] = ?
                            GROUP BY a.[Device Manufacturer], a.[Device Product Name], a.[Device Model]
                            UNION
                            SELECT     b.[Device Manufacturer] AS 'DEVICE MANUFACTURER', b.[Device Product Name] AS 'DEVICE PRODUCT NAME', 
                                                 b.[Device Model] AS 'DEVICE MODEL', 0 AS 'Collection 1', COUNT(b.[Device Record ID]) AS 'Collection 2'
                            FROM         BDNA_Reporting.dbo.Device B
                             WHERE     b.[Collection ID] = ?
                            GROUP BY b.[Device Manufacturer], b.[Device Product Name], b.[Device Model]) AS C
     GROUP BY C.[Device Manufacturer], C.[Device Product Name], C.[Device Model]
     ORDER BY 6 DESC, 1, 2, 3


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by PDix2 Thursday, June 21, 2012 5:02 PM
    Thursday, June 21, 2012 4:37 PM

All replies

  • Hi PDix2,

    As the error message shows, I would like to know if there is any misspell with the parameter? If there isn’t any misspelling then I would suggest that you should go through the following articles which well explanation about how to use parameters with command:

    Please take note with section of Using Parameters with an OleDbCommand or OdbcCommand

    Hope this could help you.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, June 21, 2012 9:54 AM
  • Do you have any code or a SQL statement to post?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, June 21, 2012 12:04 PM
  • Hi Paul,

    Here is the query..

    SELECT     c.[Device Manufacturer] AS 'DEVICE MANUFACTURER', c.[Device Product Name] AS 'DEVICE PRODUCT NAME', c.[Device Model] AS 'DEVICE MODEL',
                          SUM(c.[Collection 1]) AS 'COLLECTION 1', SUM(c.[Collection 2]) AS 'COLLECTION 2', (SUM(c.[Collection 2]) - SUM(c.[Collection 1]))
                          AS 'COLLECTION DELTA'
    FROM         (SELECT     a.[Device Manufacturer] AS 'DEVICE MANUFACTURER', a.[Device Product Name] AS 'DEVICE PRODUCT NAME',
                                                  a.[Device Model] AS 'DEVICE MODEL', COUNT(a.[Device Record ID]) AS 'Collection 1', 0 AS 'Collection 2'
                           FROM          BDNA_Reporting.dbo.Device A
                          WHERE      a.[collection id] = [@CollID1]
                           GROUP BY a.[Device Manufacturer], a.[Device Product Name], a.[Device Model]
                           UNION
                           SELECT     b.[Device Manufacturer] AS 'DEVICE MANUFACTURER', b.[Device Product Name] AS 'DEVICE PRODUCT NAME',
                                                 b.[Device Model] AS 'DEVICE MODEL', 0 AS 'Collection 1', COUNT(b.[Device Record ID]) AS 'Collection 2'
                           FROM         BDNA_Reporting.dbo.Device B
                            WHERE     b.[Collection ID] = [@CollID2]
                           GROUP BY b.[Device Manufacturer], b.[Device Product Name], b.[Device Model]) AS C
    GROUP BY C.[Device Manufacturer], C.[Device Product Name], C.[Device Model]
    ORDER BY 6 DESC, 1, 2, 3

    I'm new to SSRS...   For another report, I was getting the same error.  I removed the where claus from my query and when I selected the parameter value when the report ran, I guess it would pass the value selected to the parameter set up in the Dataset Properties, and it worked fine!!   However, this won't work with this query because I have two parameters because it's a union query and I need to use one parameter value for the 1st part of the query and the other parameter for the 2nd part of the query.   If I remove the where clauses, like I did for my other report, it only uses one parameter value for both parts of the query, so it doesn't do what I need the report to do.   How do I make the Union query use one parameter value for the 1st part of the query and the other parameter for the 2nd part of the query?? 

    Thursday, June 21, 2012 4:29 PM
  • With ODBC (and OLE DB), specify parameter markers as a question mark instead of variable names as the link Tony referenced suggestes. The parameters are mapped by ordinal position rather than by name.

    SELECT     c.[Device Manufacturer] AS 'DEVICE MANUFACTURER', c.[Device Product Name] AS 'DEVICE PRODUCT NAME', c.[Device Model] AS 'DEVICE MODEL',
                           SUM(c.[Collection 1]) AS 'COLLECTION 1', SUM(c.[Collection 2]) AS 'COLLECTION 2', (SUM(c.[Collection 2]) - SUM(c.[Collection 1]))
                           AS 'COLLECTION DELTA'
     FROM         (SELECT     a.[Device Manufacturer] AS 'DEVICE MANUFACTURER', a.[Device Product Name] AS 'DEVICE PRODUCT NAME',
                                                   a.[Device Model] AS 'DEVICE MODEL', COUNT(a.[Device Record ID]) AS 'Collection 1', 0 AS 'Collection 2'
                            FROM          BDNA_Reporting.dbo.Device A
                           WHERE      a.[collection id] = ?
                            GROUP BY a.[Device Manufacturer], a.[Device Product Name], a.[Device Model]
                            UNION
                            SELECT     b.[Device Manufacturer] AS 'DEVICE MANUFACTURER', b.[Device Product Name] AS 'DEVICE PRODUCT NAME', 
                                                 b.[Device Model] AS 'DEVICE MODEL', 0 AS 'Collection 1', COUNT(b.[Device Record ID]) AS 'Collection 2'
                            FROM         BDNA_Reporting.dbo.Device B
                             WHERE     b.[Collection ID] = ?
                            GROUP BY b.[Device Manufacturer], b.[Device Product Name], b.[Device Model]) AS C
     GROUP BY C.[Device Manufacturer], C.[Device Product Name], C.[Device Model]
     ORDER BY 6 DESC, 1, 2, 3


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by PDix2 Thursday, June 21, 2012 5:02 PM
    Thursday, June 21, 2012 4:37 PM
  • Hi Dan,

    Changing the parameter names to a ? fixed the problem!!!   Thank you so much!!

    Thursday, June 21, 2012 5:05 PM