Mixing parameter syntax in Execute SQL task

Hi all,

As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.

I can do this in two separate Execute SQL tasks as follows:

Task 1 syntax

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'

Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0

UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'

However I cannot make this work with a single SQL statement such as

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'

because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.

Is this behaviour by design, is it a bug, or is there something I've missed?

Thanks as ever,

Richard

Richard,

What error message do you get?

-Jamie

Missing SP Parameter?

I've got a completely goofy problem on one development machine running Vista, SQL 2005 sp2, and Orcas Beta 2. I'm calling a stored proc from within my C# and my ExecuteDataReader() command fails saying I'm missing a parameter (which I'm not).

The c# looks like this:

if (SqlContext.IsAvailable)

{

using (System.Data.SqlClient.SqlConnection cnx =

new System.Data.SqlClient.SqlConnection("context connection=true"))

{

cnx.Open();

using (SqlCommand xCmd = new SqlCommand("dbo.TestMe", cnx))

{

xCmd.CommandType = CommandType.StoredProcedure;

xCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("myParam", SqlDbType.Int));

xCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("myStr",

SqlDbType.VarChar, 150));

xCmd.Parameters["myParam"].Value = 123;

xCmd.Parameters["myStr"].Value = "Hello";

xCmd.ExecuteNonQuery();

}

}

}

My Stored Proc look like this:

CREATE PROCEDURE [dbo].[TestMe]

@.myParam as int,

@.myStr as nvarchar(150)

AS

BEGIN

SELECT 1 as myCol

END

The result is a an exception that looks like this:

A .NET Framework error occurred during execution of user-defined routine or aggregate "StoredProcedure1":

System.Data.SqlClient.SqlException: Procedure or function 'TestMe' expects parameter '@.myParam', which was not supplied.

If I run the SP from the Sql Workbench, it looks fine. Any ideas? I just repaved SQL and Orcas on this machine to see if I can fix the problem -- no luck.

The problem is with how you have created the parameters in your C# code. When you are using the Sql provider and you are clling parameterized queries or stored proc with queries, the parameters are sent in by name. Your T-SQL code has two parameters: @.myParam and @.myStr, in your T-SQL code you create a param called myParam and myStr - you need the @. as well in the parameter name.

Niels
|||

Well that certainly fixed the problem. Thanks!

But has something changed in the SqlClient plumbing? I went back to look at our test machine, which is a Windows Server 2003 R2 / SQL 2005 SP2 (64-bit) system. The C# code works without the "@." in the param names as it does on other similarly configured systems. I even took the little routine from my code sample and deployed it -- sans "@." -- on our test machines and it works. Hmm.

Again thanks for the reply.

-Erik

Missing something with report filter / parameter

It must be obvious, but I'm just not seeing my mistake.
I'm running this query against an Oracle db as the driving dataset
Select Count(id), value1,value2,groupid
from mytable
where createDate between :startDate and :endDate
group by value1,value2,groupid
my second dataset comes from this query
select groupid,groupname
from mygroups
I've got start and end date set as report parameters and they work just
fine. What I've been trying to do is make a full pull of the primary
dataset and allow users to filter it at the report level.
When I add a group filter to the driving dataset...
CStr(Fields!groupid.Value) = =Parameters!groupid.Value,
the report viewer returns an Oracle error that not all parameters have
been passed. How the heck is that happening? Isn't the full dataset
supposed to be pulled to the report and at that point supposed to be
filterable? What am I missing ?
Should I be passing the groupid in the driving data set with the Union
"All" work around and then filter or ?
--
Garth H
webdev511@.spamcop.net
Microsoft Certified Technology Specialist
Microsoft Certified Professional
Macromedia Certified DeveloperI think I figured this one out.
It ended up working when i explicitly pulled the groupid's that I want
to filter on.
It's not how I thought it should work, but there it is.
Garth H
webdev511@.spamcop.net
Microsoft Certified Technology Specialist
Microsoft Certified Professional
Macromedia Certified Developer

Missing Resultset

Hi Guys
I have a report where I pass a parameter to a stored procedure that then
returns a resultset to the report.
Everything works well apart from when I choose my *All option. If this *All
option is chosen no data is returned. If run the Dataset and enters the *All
option manually it returns data. If I capture what is sent to the SQLServer
and execute that in Query Analizer it returns data.If I run the report it
comes up empty no errors just no data.
If I run the report with any other option than the *All data is shown the
way it should.
Any Ideas?
Thanks.
Regards
JonasFound the error/mistake.
When you have a report parameter and want to use a Stored Procedure (that at
this stage are not able to recieve a parameter) RS automatically creates a
filter.
Once the filter were deleted everything worked.
Jonas
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:eGr76HPgEHA.396@.TK2MSFTNGP12.phx.gbl...
> Hi Guys
> I have a report where I pass a parameter to a stored procedure that then
> returns a resultset to the report.
> Everything works well apart from when I choose my *All option. If this
*All
> option is chosen no data is returned. If run the Dataset and enters the
*All
> option manually it returns data. If I capture what is sent to the
SQLServer
> and execute that in Query Analizer it returns data.If I run the report it
> comes up empty no errors just no data.
> If I run the report with any other option than the *All data is shown the
> way it should.
> Any Ideas?
> Thanks.
> Regards
> Jonas
>
>

Missing parameter, no chart displayed

I've got this problem: (with Reporting Services 2005)

I've got a report with a chart with some values that are needed to get the chart displayed.
If one of this parameters is missing (not selected) the program doesn't display the report (it shows a blank page)
Is it possible in this case to get instead a textbox with a message requiring to select the missing parameter (if I insert a textbox below the chart in the layout, this textbox isn't displayed if one parameter is missing)

ps sorry for my poor english

thanks in advance for your helpI believe what you want can be accomplished by making sure each parameter is set to NOT allow blank value. Just go to "Report" -> "Report Parameters" and make sure the "Allow blank value" checkbox is unchecked for each parameter. Then, the report will not be generated and a popup box will be displayed if the user doesn't enter a value.|||

Thank you for your answer.
But the problem is that I would like to display a warning to the user to suggest him to select the missing parameter (in my case choosing from a scrolling menu).
The parameter is multivalue so i would like to avoid to displaying a default value, but I would like the user to choose.
The application does not seem to allow this.
In fact if I add a textbox, it's not displayed unless also the chart is displayed.
So if the parameter is missing, no chart will be displayed and so the textbox too.
No object is being shown unless all the parameters are selected and only in this case the chart can be displayed... but as I said I would like to display this warning when a parameter is not selected.
How can I do this?

ps: sorry if I'm a little redundant

|||

If what you want is a message to be displayed to the user to choose a value, then you should be able to accomplish this by unchecking the "Allow blank value" option as stated above.

For example, if there are two parameters say "Type" and "Subtype" but the user only selects "Type" and leaves "Subtype" blank, then once the user clicks "View Report" a popup message will be displayed to the user saying...

"Please enter a value for the parameter 'Subtype'. The parameter cannot be blank."

|||Thanks for helping me :)
The pop up appears but I would like this message to show in the report layout but BEFORE ckliking on "view report"...just a simple generical message saying to the user something like "please choose a value for the X parameter and THEN click on view report"
The message is a sort of "guide".
I thought I can do this by placing a textbox but it seems that the textbox is linked to the chart (no chart no textbox)...i know it seems a stupid problem but unfortunatly I was asked to do this work this way.|||

Well, there is one way to generate a textbox in the report even without having a paremeter selected. Simply make sure all parameters are set to "Allow blank value" and "Allow null value". Then, create a textbox where the visibility is set to an expression, where it will only be displayed if any parameter is blank. Also, be sure to make the rest of the report items the opposite of that expression, i.e. they are only visible if all values are entered.

For example, Visibility -> Hidden = Iif(IsNothing(Parameters!ParameterName.Value) OrElse Parameters!ParameterName.Value.Equals(""), False, True)

This way the textbox will show up in the report whenever the user doesn't select a parameter value. However, the report will not autodetect the new values entered until the user clicks "View Report."

Good luck!!

|||Hi, thank you for your answer.
Unfortunatly I don't seem to be able to set "allow null value" for the parameter I'm interested in, cause the application says that the properties for that object are invalid.
That object is a multi-value string available values from query.
So your suggestion does not work. :(

Missing parameter values error

I am using a CR viewer in VS 2005 (VB 2005). I am using the ReportDocument class to print the report. I get a "Missing parameter values" error when the code gets to the "PrintToPrinter" method. Here's my code:

Dim rpt2 As New CrystalDecisions.CrystalReports.Engine.ReportDocument

Dim param1Fields As New CrystalDecisions.Shared.ParameterFields

Dim param1Field As New CrystalDecisions.Shared.ParameterField()

Dim param1Range As New CrystalDecisions.Shared.ParameterDiscreteValue()

param1Field.ParameterFieldName = "CaseIDin" ' Parameter Name In Crystal Report

param1Range.Value = strCaseID.ToString ' value For Parameter Field

param1Field.CurrentValues.Add(param1Range)

param1Fields.Add(param1Field) ' To add parameter in parameterslist

rpt2.Load("C:\CaseHistory.rpt")

rpt2.DataDefinition.ParameterFields("CaseIDin").CurrentValues.Clear()

rpt2.DataDefinition.ParameterFields("CaseIDin").CurrentValues.Add(param1Range)

rpt2.PrintToPrinter(1, False, 0, 0)

What's wrong with this code? How do I get the parameters correct.

TIA,

SteveOne of the parameters is not given value. Make sure you supplied values for all the parameters

Missing parameter on postback

II6.0
SQL server 2005
I have a report with toggle option, it work fine except the toggle, when
click on + I receive error rsReportParameterValueNotSet.
The URL of toggle is construct without parameter needs to produce the report.
The toggle work fine in my old server with IIS 5 and SQL Server 2000.
I don't know where search, I find nothing on forum or help.
Give me idea please.
Ã?ricViewstate mac in machine.config?
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:50693565-A718-496E-8DF3-A54924322035@.microsoft.com...
> II6.0
> SQL server 2005
> I have a report with toggle option, it work fine except the toggle, when
> click on + I receive error rsReportParameterValueNotSet.
> The URL of toggle is construct without parameter needs to produce the
> report.
> The toggle work fine in my old server with IIS 5 and SQL Server 2000.
> I don't know where search, I find nothing on forum or help.
> Give me idea please.
> Éric