Problem with parameterized SELECT statement

I'm trying to use a parameterized SELECT statement, but I must not have it right - the code below gives this compile error: System.Data.SqlClient.SqlException: Must declare the scalar variable "@.UserID".

string

strUserID = (string)Session["UserID"];string strSelectRatings ="SELECT [CommentID], [GameID], [UserID], [Rating], LEFT(Comment,40) as Comment FROM [Comments] WHERE [UserID] = @.UserID";SqlConnection myConnection =newSqlConnection("...");SqlCommand myCommand =newSqlCommand(strSelectRatings, myConnection);

myCommand.Parameters.Add(

"@.UserID", strUserID);

MySqlDataSource.SelectCommand = strSelectRatings;

GridView1.DataBind();

Add the parameters to your SqlDataSource object

MySqlDataSource.SelectParameters.Add("@.UserID", strUserID);

Problem with Parameterized reports - changing default values

Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
1. When I run CRM3 parameterized reports using the RS Report Manager
interface i get error messages like " The 'CRM_Locale' parameter is missing
a value " or "The 'CRM_Locale' parameter is missing a value", however when
these reports are run from the CRM application or ReportServer URL interface
the reports work fine.
2. I inadvertantly changed one of the report parameter default values in
one of the reports to blank, how can i change it back to its original setting
"Override Default". The report now prints with a blank Prepared By: User Name
value.
Any suggestions would be greatly appreciated
GsBring the report up in the web front end- report manager. Go to the
properties and you can change the default parameters.
Doing so from the Development IDE will NOT change the parameters once the
report has been deployed.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Graham Smith" wrote:
> Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> 1. When I run CRM3 parameterized reports using the RS Report Manager
> interface i get error messages like " The 'CRM_Locale' parameter is missing
> a value " or "The 'CRM_Locale' parameter is missing a value", however when
> these reports are run from the CRM application or ReportServer URL interface
> the reports work fine.
> 2. I inadvertantly changed one of the report parameter default values in
> one of the reports to blank, how can i change it back to its original setting
> "Override Default". The report now prints with a blank Prepared By: User Name
> value.
> Any suggestions would be greatly appreciated
> Gs|||Hi Wayne
> Bring the report up in the web front end- report manager. Go to the
> properties and you can change the default parameters.
>
Tried that, No Joy!
> > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > these reports are run from the CRM application or ReportServer URL interface
> > the reports work fine.
The reports do not render at all. It appears that these parameter values are
not being populated are missing when reports are run using the Report Manager
Interface. Reports without parameters run OK.
> 2. I inadvertantly changed one of the report parameter default values in
> one of the reports to blank, how can i change it back to its original setting
> "Override Default". The report now prints with a blank Prepared By: User Name
> value.
>
I cannot change this back to its original state. The report is CRM 3.0
standard Report "User Summary" the parameter is "CRM_FullName" It originally
had a
"Override Default" command button in the default value for this parameter.
When the reports are run from the CRM 3.0 web interface ot the ReportServer
URL web interface the report renders ok but without the value for the User
Name.
regards
GS
"Wayne Snyder" wrote:
> Bring the report up in the web front end- report manager. Go to the
> properties and you can change the default parameters.
> Doing so from the Development IDE will NOT change the parameters once the
> report has been deployed.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Graham Smith" wrote:
> > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> >
> > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > these reports are run from the CRM application or ReportServer URL interface
> > the reports work fine.
> >
> > 2. I inadvertantly changed one of the report parameter default values in
> > one of the reports to blank, how can i change it back to its original setting
> > "Override Default". The report now prints with a blank Prepared By: User Name
> > value.
> >
> > Any suggestions would be greatly appreciated
> > Gs|||What means "No Joy" exact.
Any error message after clicking "Apply" in the properties page?
You need to go to properties after open the report and don't click on execute.
"Graham Smith" wrote:
> Hi Wayne
> > Bring the report up in the web front end- report manager. Go to the
> > properties and you can change the default parameters.
> >
> Tried that, No Joy!
> > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > these reports are run from the CRM application or ReportServer URL interface
> > > the reports work fine.
> The reports do not render at all. It appears that these parameter values are
> not being populated are missing when reports are run using the Report Manager
> Interface. Reports without parameters run OK.
> >
> > 2. I inadvertantly changed one of the report parameter default values in
> > one of the reports to blank, how can i change it back to its original setting
> > "Override Default". The report now prints with a blank Prepared By: User Name
> > value.
> >
> I cannot change this back to its original state. The report is CRM 3.0
> standard Report "User Summary" the parameter is "CRM_FullName" It originally
> had a
> "Override Default" command button in the default value for this parameter.
> When the reports are run from the CRM 3.0 web interface ot the ReportServer
> URL web interface the report renders ok but without the value for the User
> Name.
> regards
> GS
>
> "Wayne Snyder" wrote:
> > Bring the report up in the web front end- report manager. Go to the
> > properties and you can change the default parameters.
> >
> > Doing so from the Development IDE will NOT change the parameters once the
> > report has been deployed.
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> >
> > I support the Professional Association for SQL Server ( PASS) and it''s
> > community of SQL Professionals.
> >
> >
> > "Graham Smith" wrote:
> >
> > > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> > >
> > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > these reports are run from the CRM application or ReportServer URL interface
> > > the reports work fine.
> > >
> > > 2. I inadvertantly changed one of the report parameter default values in
> > > one of the reports to blank, how can i change it back to its original setting
> > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > value.
> > >
> > > Any suggestions would be greatly appreciated
> > > Gs|||Hi
No Joy means it did not work!
There were no error messages after hitting the "Apply" button in the
Properties.
I cant set the Default Value back to its original setting, so the reports
being rendered by the CRM 3.0 Reports function and Reports Web browser URL
Interface are incorrect.
I can send screen shots of the problem if you can send an email alias to
reply to.
Thanks
GS
"Dev Main" wrote:
> What means "No Joy" exact.
> Any error message after clicking "Apply" in the properties page?
> You need to go to properties after open the report and don't click on execute.
> "Graham Smith" wrote:
> > Hi Wayne
> >
> > > Bring the report up in the web front end- report manager. Go to the
> > > properties and you can change the default parameters.
> > >
> >
> > Tried that, No Joy!
> >
> > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > these reports are run from the CRM application or ReportServer URL interface
> > > > the reports work fine.
> >
> > The reports do not render at all. It appears that these parameter values are
> > not being populated are missing when reports are run using the Report Manager
> > Interface. Reports without parameters run OK.
> >
> > >
> > > 2. I inadvertantly changed one of the report parameter default values in
> > > one of the reports to blank, how can i change it back to its original setting
> > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > value.
> > >
> >
> > I cannot change this back to its original state. The report is CRM 3.0
> > standard Report "User Summary" the parameter is "CRM_FullName" It originally
> > had a
> > "Override Default" command button in the default value for this parameter.
> >
> > When the reports are run from the CRM 3.0 web interface ot the ReportServer
> > URL web interface the report renders ok but without the value for the User
> > Name.
> >
> > regards
> > GS
> >
> >
> >
> > "Wayne Snyder" wrote:
> >
> > > Bring the report up in the web front end- report manager. Go to the
> > > properties and you can change the default parameters.
> > >
> > > Doing so from the Development IDE will NOT change the parameters once the
> > > report has been deployed.
> > > --
> > > Wayne Snyder MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > >
> > > I support the Professional Association for SQL Server ( PASS) and it''s
> > > community of SQL Professionals.
> > >
> > >
> > > "Graham Smith" wrote:
> > >
> > > > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> > > >
> > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > these reports are run from the CRM application or ReportServer URL interface
> > > > the reports work fine.
> > > >
> > > > 2. I inadvertantly changed one of the report parameter default values in
> > > > one of the reports to blank, how can i change it back to its original setting
> > > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > > value.
> > > >
> > > > Any suggestions would be greatly appreciated
> > > > Gs|||Sorry, I don't have the CRM Application.
But I had similare experience with parameters. If I changed parameter
settings on deployed report, the changes where not deployed.
Since this I allways need to change the parameter in the Report Manager
manuell after redeploy those reports. After manuell change of the parameters
I can see the changes in the parameter section of the report. And the URL
Access works again.
Whats a bit difficult is the way "allow null" settings are displayed in the
report manager. If I like to allow null I need to activate "have dafault" and
the value field must be empty. In the designer it is required to set Default
values to non and activate "Allow Null" and "Allow blank".
I assume if you apply the correct settings in the report manager for the
parameter, the url access must work.
"Graham Smith" wrote:
> Hi
> No Joy means it did not work!
> There were no error messages after hitting the "Apply" button in the
> Properties.
> I cant set the Default Value back to its original setting, so the reports
> being rendered by the CRM 3.0 Reports function and Reports Web browser URL
> Interface are incorrect.
> I can send screen shots of the problem if you can send an email alias to
> reply to.
> Thanks
> GS
>
>
> "Dev Main" wrote:
> > What means "No Joy" exact.
> >
> > Any error message after clicking "Apply" in the properties page?
> >
> > You need to go to properties after open the report and don't click on execute.
> >
> > "Graham Smith" wrote:
> >
> > > Hi Wayne
> > >
> > > > Bring the report up in the web front end- report manager. Go to the
> > > > properties and you can change the default parameters.
> > > >
> > >
> > > Tried that, No Joy!
> > >
> > > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > > these reports are run from the CRM application or ReportServer URL interface
> > > > > the reports work fine.
> > >
> > > The reports do not render at all. It appears that these parameter values are
> > > not being populated are missing when reports are run using the Report Manager
> > > Interface. Reports without parameters run OK.
> > >
> > > >
> > > > 2. I inadvertantly changed one of the report parameter default values in
> > > > one of the reports to blank, how can i change it back to its original setting
> > > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > > value.
> > > >
> > >
> > > I cannot change this back to its original state. The report is CRM 3.0
> > > standard Report "User Summary" the parameter is "CRM_FullName" It originally
> > > had a
> > > "Override Default" command button in the default value for this parameter.
> > >
> > > When the reports are run from the CRM 3.0 web interface ot the ReportServer
> > > URL web interface the report renders ok but without the value for the User
> > > Name.
> > >
> > > regards
> > > GS
> > >
> > >
> > >
> > > "Wayne Snyder" wrote:
> > >
> > > > Bring the report up in the web front end- report manager. Go to the
> > > > properties and you can change the default parameters.
> > > >
> > > > Doing so from the Development IDE will NOT change the parameters once the
> > > > report has been deployed.
> > > > --
> > > > Wayne Snyder MCDBA, SQL Server MVP
> > > > Mariner, Charlotte, NC
> > > >
> > > > I support the Professional Association for SQL Server ( PASS) and it''s
> > > > community of SQL Professionals.
> > > >
> > > >
> > > > "Graham Smith" wrote:
> > > >
> > > > > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> > > > >
> > > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > > these reports are run from the CRM application or ReportServer URL interface
> > > > > the reports work fine.
> > > > >
> > > > > 2. I inadvertantly changed one of the report parameter default values in
> > > > > one of the reports to blank, how can i change it back to its original setting
> > > > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > > > value.
> > > > >
> > > > > Any suggestions would be greatly appreciated
> > > > > Gs

Problem with parameterized query.

I'm trying to use a parameterized query where the parameter is the table I'm querying from (i.e. "select * from @.datatable). Whenever I try to execute the report, I get an error stating that I "... must declare the table variable @.datatable". I think that's because my sql is not legal -- I haven't defined the variable to be an acutal table structure. Is that correct?

If so is there any other way to select from a table name that is dynamic using the paramteter structure that RDL supports? I have been able to do what I want by making the value of the commandtext element an expression that evaluates to the proper sql. Parameters help cut down on possible malicious SQL and I'm hoping to be able to make use of the supported parameter system in RS if possible.

Any help is appreciated.

You can't substitute a table name using standard parameterization. Basically, you would either need to write some sort of stored proc that constructed the SQL and used an EXEC statement or you can use an expression string for the query.

="SELECT * FROM [" & Parameters!TableName.Value & "]"

You need to be careful here about SQL injection. If someone passes in "FOO GO DROP DATABASE XXX" as the parameter, you are in for trouble. You would want to write some sort of function to vaildate the input.