Products

Solutions

Resources

Partners

Community

About

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportsError with @UserIDError with @UserID
Previous
 
Next
New Post
12/22/2009 4:57 PM
 

I have searched this forum and others and have not found a solution. I want to use the reports module to show projects associates with the users's custom profile field. The backend is SQL Server 2005. The query works if I use an actual UserId, but not when I use @UserId.

 The following results in the error message: There is an error in your SQL at line 3: Must declare the scalar variable "@UserID". Must declare the scalar variable "@UserID".

Select projects.projectid as [Project ID],onlninappid as [Online ID], participants.company as [Company],projects.projecttype as [Type], projects.QPStatus as [Status],Convert(varchar,preapprovaldate,101) as [Pre-Approved On], '$' + Convert(varchar,Convert(money,PreApprovedIncentiveAmount)) as [Pre-Approved For] FROM effrt.dbo.projects inner join effrt.dbo.participants on effrt.dbo.projects.participantid= effrt.dbo.participants.participantid WHERE tradeallyid in (Select PropertyValue From gdsrts.dbo.par_UserProfile Where PropertyDefinitionID = 58 AND UserId=@UserID) And datereceived > '10/01/2009' OR tradeallyid2 in (Select PropertyValue From gdsrts.dbo.par_UserProfile Where PropertyDefinitionID = 58 AND UserId=@UserID) And datereceived > '10/01/2009' ORDER BY DATERECEIVED DESC

This SQL works fine:

Select projects.projectid as [Project ID],onlninappid as [Online ID], participants.company as [Company],projects.projecttype as [Type], projects.QPStatus as [Status],Convert(varchar,preapprovaldate,101) as [Pre-Approved On], '$' + Convert(varchar,Convert(money,PreApprovedIncentiveAmount)) as [Pre-Approved For] FROM effrt.dbo.projects inner join effrt.dbo.participants on effrt.dbo.projects.participantid= effrt.dbo.participants.participantid WHERE tradeallyid in (Select PropertyValue From gdsrts.dbo.par_UserProfile Where PropertyDefinitionID = 58 AND UserId=10) And datereceived > '10/01/2009' OR tradeallyid2 in (Select PropertyValue From gdsrts.dbo.par_UserProfile Where PropertyDefinitionID = 58 AND UserId=10) And datereceived > '10/01/2009' ORDER BY DATERECEIVED DESC

Test successful, 22 records returned. 

Can someone please tell me what I am missing here? It seems like it is something simple...

 

 

 
New Post
12/22/2009 7:00 PM
 

make sure to save your text encoded in UTF-8 prior to pasting it into any Reports text box.


Cheers from Germany,
Sebastian Leupold

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
12/23/2009 9:38 AM
 

Sebastian,

 Thanks for your reply. I have done this, but still get the error:

There was an error executing the data source: There is an error in your SQL at line 1: Must declare the scalar variable "@UserID". Must declare the scalar variable "@UserID".
 

 
New Post
12/23/2009 3:07 PM
 

I got around this issue by purchasing and installing SQL Deluxe 1.3 Standard Edition. My query ran with no problems with this module.

 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportsError with @UserIDError with @UserID


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out