Error in using Multi varied parameters!

Hi,

I am tired of troubleshooting this. If I use sa MVP called say states and on the top of the page write

Parameters!orgstate.Value

I get the familiar "#Error" message. This works fine for any other parameter except MVP.? Anything that i am forgetting to do?

Let me know ASAP if anyone has an idea about this.

Thanks

Because it is a multi-value parameter you are getting back an array of values. You need to do something like:

Parameters!orgstate.Value(0)

-Daniel

|||

Once you mark a parameter as "multi-value", the .Value property will return an object[] with all selected values. If only one value is selected, it will be an object array of length = 1. Object arrays cannot be directly compared with Strings.

To access individual values of a multi value parameter you can use expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used e.g. for drillthrough parameters, subreports, or query parameters)

See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp

-- Robert

|||

Thanks Daniel!

Error in using Multi varied parameters!

Hi,

I am tired of troubleshooting this. If I use sa MVP called say states and on the top of the page write

Parameters!orgstate.Value

I get the familiar "#Error" message. This works fine for any other parameter except MVP.? Anything that i am forgetting to do?

Let me know ASAP if anyone has an idea about this.

Thanks

Because it is a multi-value parameter you are getting back an array of values. You need to do something like:

Parameters!orgstate.Value(0)

-Daniel

|||

Once you mark a parameter as "multi-value", the .Value property will return an object[] with all selected values. If only one value is selected, it will be an object array of length = 1. Object arrays cannot be directly compared with Strings.

To access individual values of a multi value parameter you can use expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used e.g. for drillthrough parameters, subreports, or query parameters)

See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp

-- Robert

|||

Thanks Daniel!

Error in stored procedure

I have a stored procedure to which I pass the following parameters
@.Date smalldatetime,
@.Amount decimal(15,3)
@.Exg_Rate decimal(5,3)
Inside this stored procedure I call another one passing to it those parameters like that
EXECUTE dbo.[Opening_Balance_AfterInsert] @.Date, @.Amount*@.Exg_Rate
I receive an error at the above line saying: Incorrect syntax near '*'
if I try to put the expression between rounded brackets I receive the error Incorrect syntax near '('
How can I pass that expression?create procedure myproc
@.Date smalldatetime,
@.Amount decimal(15,3)
@.Exg_Rate decimal(5,3)
as
declare @.combo float
set @.combo = @.Amount*@.Exg_Rate
execute do.opening_balance_afterInsert @.date, @.combo
Nicksql

error in parameters when using stored procedure...

hi, all
I met a problem, I use OleDB to connect database, and using stored procedure with 3 parameters to getting data, including 2 datetime parameters named DateFrom and DateTo.

Everything is fine in my desktop, but in my customer's side, there is an error-"DateFrom is not a parameter for procedure XXXX", I don't know why is this happened, is it related to the date format thing? I'm not sure what the enviroment is in my customer's side, does anyone ever met this error?

Many thanks!!!

Might be a format issue. They might be entering the date as 11-14-06. It might be interpreting it as a string datatype.

Adamus

|||

Adamus Turner wrote:

Might be a format issue. They might be entering the date as 11-14-06. It might be interpreting it as a string datatype.

Adamus

but, i notice that in the report screenshot which my customer sent me, the DateFrom and DateTo parameter had been converted to the standard format "9/1/2006 12:00:00 AM" and "11/1/2006 12:00:00 AM", and because I set these two parameters to DateTime in report, if the user input the invalid string, it shouldn't be passed anyway.

thanks.

|||

Then the problem is in the SQL sp_

I'm curious, did you test the sp_ by using EXEC in query analyzer/management studio or through a front end form?

Please post the sp_ instantiation along with the parameter declarations.

Adamus

|||

Adamus Turner wrote:

Then the problem is in the SQL sp_

I'm curious, did you test the sp_ by using EXEC in query analyzer/management studio or through a front end form?

Please post the sp_ instantiation along with the parameter declarations.

Adamus

Yes, I've tried and it works. the stored procedure is,,,

CREATE PROCEDURE [dbo].[usp_XXXX]
(
@.DateFrom datetime,
@.DateTo datetime,
@.ParentItem varchar(50)
)
AS
......

thank you.

|||

I see. Well I'm trying to remember where I've experience this issue. I believe it was in ASP.net where I used ADO.net to set the command text to execute the sp_ but set the command type incorrectly (i.e. cmdquery not cmdsp)

This is probably the case if you did not design the front end form. The client designed the form to execute a non-parameterized query such as a View. So when they try to pass parameters, it says, "Your command type doesn't support parameterized execution. Please use the correct command type." ...which in your case would be a stored procedure not plain T-SQL.

You might want to consult the ASP.net forum or ADO.net for a resolution to your problem.

Adamus

|||

Thank you, Adamus!

I got the reason, it is the connection type's problem, when using OLE DB, everything is ok...