Share via

ADO Error: Must declare the scalar variable

Anonymous
2015-11-17T22:40:26+00:00

I have a ADP project with a 2005 SQL backend.

I have created a huge stored procedure, jumping back and forth between design view and SQL view.  Obviously I changed something, but nothing that should have mattered.  I added another table to my query and now I get the error [ADO Error: Must declare the scalar variable].

The strange part is if I make a change in the design view and try to save it, I get the above error.  If I switch to SQL view and make the exact change, it saves fine.

In my code I have 5 @variables.  If I delete all references to the one variable or even rename it to a different variable in my stored procedure, I can save in design view. 

ALTER PROCEDURE dbo.[Copy Of Blast QA QC]

(@PowderAllowance Float,

@DepthAllowance Float, ***this is the variable that is failing

@WorkLocationID nvarchar(50),

@BenchID int,

@PatternID nvarchar(10))

AS SELECT      ... ... ...

Any help would be great

Thanks

Chad

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-11-17T23:15:22+00:00

    .

    .

    .

    GROUP BY dbo.Blasting_Pattern_Hole.DesignDepth, dbo.Blasting_Pattern_Hole.BlastDepth, dbo.Blasting_Pattern_Hole.PowderDispensedCount, dbo.Blasting_Pattern_Hole.WetDry,

    dbo.Blasting_Pattern_Hole.Collar, dbo.Drilling_Pattern_Hole.DrillDepth, dbo.Blasting_Pattern_Hole.PowderDesignCount, dbo.WorkLocation.WorkLocation, dbo.Bench.Bench, dbo.Pattern.PatternNo,

    dbo.Blasting_Pattern_Hole.Comment, dbo.WorkLocation.WorkLocationID, dbo.Pattern.BenchID, dbo.Pattern.PatternID, dbo.Blasting_Pattern_Hole.CommentFilter, dbo.Hole.HoleNum, dbo.HoleComment.HoleComment,

    CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount > (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance)

    THEN CAST('1' AS int) ELSE '' END,

    CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount > (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance)

    THEN 'OVER' ELSE '' END + CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount < (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance)

    THEN 'UNDER' ELSE '' END + CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount BETWEEN

    (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND

    (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) THEN 'PASS' ELSE '' END,

    CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount < (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance)

    THEN CAST('1' AS int) ELSE '' END, CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount BETWEEN

    (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND

    (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) THEN CAST('1' AS int) ELSE '' END

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-11-17T23:15:08+00:00

    .

    .

    .

    CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount > (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND (dbo.Blasting_Pattern_Hole.CommentFilter IS NULL) THEN

    'OVER' ELSE '' END

    + CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount < (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND (dbo.Blasting_Pattern_Hole.CommentFilter IS NULL) THEN

    'UNDER' ELSE '' END

    + CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount < (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND (dbo.Blasting_Pattern_Hole.CommentFilter <> '') THEN

    'ABNORMAL' ELSE '' END

    + CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount > (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND (dbo.Blasting_Pattern_Hole.CommentFilter <> '') THEN

    'ABNORMAL' ELSE '' END

    + CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount BETWEEN (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND (dbo.Blasting_Pattern_Hole.PowderDesignCount
    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) THEN

    'PASS' ELSE '' END AS POWDERSUMMARY

    FROM dbo.Pattern INNER JOIN

    dbo.Bench INNER JOIN

    dbo.Blasting ON dbo.Bench.BenchID = dbo.Blasting.BenchID INNER JOIN

    dbo.WorkLocation ON dbo.Blasting.Pit_SP_AreaID = dbo.WorkLocation.WorkLocationID ON dbo.Pattern.PatternID = dbo.Blasting.PatternID INNER JOIN

    dbo.Pattern_Hole INNER JOIN

    dbo.Blasting_Pattern_Hole ON dbo.Pattern_Hole.Pattern_HoleID = dbo.Blasting_Pattern_Hole.Pattern_HoleID INNER JOIN

    dbo.Hole ON dbo.Pattern_Hole.HoleID = dbo.Hole.HoleID ON dbo.Pattern.PatternID = dbo.Pattern_Hole.PatternID INNER JOIN

    dbo.Drilling ON dbo.Bench.BenchID = dbo.Drilling.BenchID AND dbo.Bench.BenchLocation = dbo.Drilling.Pit_SP_AreaID AND

    dbo.WorkLocation.WorkLocationID = dbo.Drilling.Pit_SP_AreaID INNER JOIN

    dbo.Pattern Pattern_1 ON dbo.Drilling.PatternID = Pattern_1.PatternID INNER JOIN

    dbo.Pattern_Hole Pattern_Hole_1 ON dbo.Hole.HoleID = Pattern_Hole_1.HoleID AND Pattern_1.PatternID = Pattern_Hole_1.PatternID INNER JOIN

    dbo.Drilling_Pattern_Hole ON Pattern_Hole_1.Pattern_HoleID = dbo.Drilling_Pattern_Hole.Pattern_HoleID LEFT OUTER JOIN

    dbo.HoleComment ON dbo.Blasting_Pattern_Hole.CommentFilter = dbo.HoleComment.HoleCommentID

    WHERE (dbo.WorkLocation.WorkLocationID = @WorkLocationID) AND (dbo.Bench.BenchID = @BenchID) AND (dbo.Pattern.PatternID = @PatternID)

    .

    .

    .

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-11-17T23:13:23+00:00

    Here is the code.  Please don't criticize my coding too much.  :(

    ALTER PROCEDURE [dbo].[Blast QA QC]

    (@PowderAllowance Float,

    @DepthAllowance Float,

    @WorkLocationID nvarchar(50),

    @BenchID int,

    @PatternID nvarchar(10))

    AS SELECT dbo.Hole.HoleNum, dbo.Blasting_Pattern_Hole.DesignDepth, dbo.Drilling_Pattern_Hole.DrillDepth, dbo.Blasting_Pattern_Hole.BlastDepth, dbo.Blasting_Pattern_Hole.PowderDesignCount,

    dbo.Blasting_Pattern_Hole.PowderDispensedCount, dbo.Blasting_Pattern_Hole.WetDry, dbo.Blasting_Pattern_Hole.Collar,

    dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance

    • dbo.Blasting_Pattern_Hole.PowderDesignCount AS DesignHigh,

    dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance AS DesignLow, dbo.WorkLocation.WorkLocation, dbo.Bench.Bench,

    dbo.Pattern.PatternNo, dbo.Blasting_Pattern_Hole.Comment, dbo.WorkLocation.WorkLocationID, dbo.Pattern.BenchID, dbo.Pattern.PatternID, dbo.Blasting_Pattern_Hole.CommentFilter,

    dbo.HoleComment.HoleComment,

    CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount > (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND (dbo.Blasting_Pattern_Hole.CommentFilter <> '') THEN

    CAST('1' AS int) ELSE '' END AS POWDERABNORMAL,

    CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount > (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND (dbo.Blasting_Pattern_Hole.CommentFilter IS NULL) THEN

    CAST('1' AS int) ELSE '' END AS POWDEROVER,

    CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount < (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND (dbo.Blasting_Pattern_Hole.CommentFilter <> '') THEN

    CAST('1' AS int) ELSE '' END AS POWDERABNORMAL2,

    CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount < (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND (dbo.Blasting_Pattern_Hole.CommentFilter IS NULL) THEN

    CAST('1' AS int) ELSE '' END AS POWDERUNDER,

    CASE WHEN dbo.Blasting_Pattern_Hole.PowderDispensedCount BETWEEN

    (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) AND

    (dbo.Blasting_Pattern_Hole.PowderDesignCount

    • dbo.Blasting_Pattern_Hole.PowderDesignCount * @PowderAllowance) THEN CAST('1' AS int) ELSE '' END AS POWDERPASS,

    CASE WHEN dbo.Blasting_Pattern_Hole.BlastDepth > (dbo.Blasting_Pattern_Hole.DesignDepth

    • dbo.Blasting_Pattern_Hole.DesignDepth * @DepthAllowance )

    THEN CAST('1' AS int) ELSE '' END AS DEPTHOVER,

    CASE WHEN dbo.Blasting_Pattern_Hole.BlastDepth < (dbo.Blasting_Pattern_Hole.DesignDepth

    • dbo.Blasting_Pattern_Hole.DesignDepth * @DepthAllowance )

    THEN CAST('1' AS int) ELSE '' END AS DEPTHUNDER,

    CASE WHEN dbo.Blasting_Pattern_Hole.BlastDepth BETWEEN

    (dbo.Blasting_Pattern_Hole.DesignDepth

    • dbo.Blasting_Pattern_Hole.DesignDepth * @DepthAllowance ) AND

    (dbo.Blasting_Pattern_Hole.DesignDepth

    • dbo.Blasting_Pattern_Hole.DesignDepth * @DepthAllowance ) THEN CAST('1' AS int) ELSE '' END AS DEPTHPASS,

    .

    .

    .

    Was this answer helpful?

    0 comments