Problems displaying default value of first cascading parameters.

I have 5 cascading parameters. They all have default values. I would like the default value of each of these to be selected when the report opens.

It currently works correctly when I preview the report, however when I deploy the report to the server it does not. In the deployed report, the default value of the first parameter is not selected. However, when I select a value for this one, on postback the rest of these parameters get set to their default value. They're all configured the same. I'm confused as to why the first one doesn't default to its default value while the others do.

Got any ideas?

Here's how I have it configured:

Allow null value CHECKED

Allow blank value CHECKED

The rest are unchecked.

Available values: From query

Default Values: Non-queried with a value supplied that exists in the dataset.

This issue got resolved when I deleted all of my reports from the server and then redeployed them. Funny how that works...sql

Problems displaying default value of first cascading parameters.

I have 5 cascading parameters. They all have default values. I would like the default value of each of these to be selected when the report opens.

It currently works correctly when I preview the report, however when I deploy the report to the server it does not. In the deployed report, the default value of the first parameter is not selected. However, when I select a value for this one, on postback the rest of these parameters get set to their default value. They're all configured the same. I'm confused as to why the first one doesn't default to its default value while the others do.

Got any ideas?

Here's how I have it configured:

Allow null value CHECKED

Allow blank value CHECKED

The rest are unchecked.

Available values: From query

Default Values: Non-queried with a value supplied that exists in the dataset.

This issue got resolved when I deleted all of my reports from the server and then redeployed them. Funny how that works...

Problems calling a stored procedures depending on parameters

Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.

I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)
The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).

On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:

ALTERPROCEDURE [dbo].[S_SPROC]
(@.IDint,@.TYPECHAR(10),@.NAMECHAR(20) OUTPUT)
AS

SELECT @.NAME= NAME
FROM SPROCS
WHERE [ID]= @.ID
AND [TYPE]= @.TYPE

Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.
Has anyone done something similar before, or could point me in the right direction to solving this problem.

Thanks
Phil

Since @.NAME is an output parameter, you need to indicate that in your Command object (ParameterDirection.InputOutput or ParameterDirection.Output). That allows the parameter's value to be retrieved after the command has been executed.

Alternatively, you could select the data like you would in a normal data retrieval, and not worry about using an output parameter.

|||

Thanks for your reply Mark, I will try adding the ParameterDirection part.

If I use normal data retrieval how can I select the appropriate stored procedure when I try filling my table adapter from the dataset?

Thanks

|||

I assumed you would be performing an operation to select the stored proc name, then another operation to execute that stored proc.

|||

Yes that is what I am trying to do, but not so sure on how to go about it. Do you have any code examples?

Thanks

|||

hi mate,

Here is a sample

Dim cmd_ObjectpathAsNew SqlCommand("Select * from [" & tabelName &"]", sqlCon)

Dim adapterAsNew SqlDataAdapter(cmd_Objectpath)

Dim resultAsNew DataTable

adapter.Fill(result)

ForEach rowAs DataRowIn result.Rows

////do the process u want

next

|||

The code I have so far is:

Dim IDAs Int32
Dim TypeAsString

PrivateSub SimpleButton1_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles SimpleButton1.Click

ID =Me.TextBox1.Text
Type =Me.TextBox2.Text

Me.SPROCSTableAdapter.Fill(Me.DataSet1.SPROCS, ID, Type)

GetSprocName("AUMS_VALID")

Try

'Logic is a seperate VB file containing further code
Logic.run_SQL_fill_dataset(Me.sqlDataAdapter1, DataSet1.GEN_VALID)
Catch exAs Exception

EndTry

EndSub

PrivateFunction GetSprocName(ByVal st1)AsString
' Gets the names for the sprocs so each table can be filled with differant data. Using value 1 for param 1 just to test
Me.SQLCommand_GetSprocName.Parameters(1).Value = 1
Me.SQLCommand_GetSprocName.Parameters(2).Value = st1.ToString()
Logic.run_SQL_command(Me.sqlConnection1,Me.SQLCommand_GetSprocName)
' This is is where the app seems to fail
ReturnMe.SQLCommand_GetSprocName.Parameters(3).Value.ToString()

EndFunction

**** Code in Logic File: *****

'Sub to run SQLcommand, checks the connection and haddles errors

PublicSharedSub run_SQL_command(ByVal sqlcon1As SqlClient.SqlConnection,ByVal sqlcom1As SqlClient.SqlCommand)

Try
If sqlcon1.State <> ConnectionState.ClosedThen' connection check
sqlcon1.Close()
EndIf

If sqlcon1.State = ConnectionState.ClosedThen' connection check

sqlcon1.Open()

EndIf

sqlcom1.ExecuteNonQuery()

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Catch exAs Exception

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Error_box(ex,"Error on Running SQL Command")'Can place more better code here later

MsgBox(sqlcom1.CommandText.ToString)

EndTry

EndSub

PublicSharedSub run_SQL_fill_dataset(ByVal sqladapterAs SqlClient.SqlDataAdapter,ByVal datatableAs Data.DataTable)

Try

datatable.Clear()

sqladapter.Fill(datatable)

Catch exAs Exception

Error_box(ex,"Error on fill on dataset.")

EndTry

|||

Hi,

I'm afraid that there's something wrong in your code. What we can provide is a general process of communicating with a stored procedure from a .NET application.

Let's take the stored procedure you provided as the sample.

ALTER PROCEDURE [dbo].[S_SPROC]
( @.ID int, @.TYPE CHAR(10), @.NAME CHAR(20) OUTPUT )
AS

SELECT @.NAME = NAME
FROM SPROCS
WHERE [ID] = @.ID
AND [TYPE] = @.TYPE

In your procs, there are 2 input parameters and an output parameter. Then in your application, you should following the steps below:

1. Create the connection which links to the database.
a) Dim myconn As New SqlConnection(ConnectionString)

2. Create the SqlCommand object which execute the procs.
Dim sc As New SqlCommand()
sc.CommandType = CommandType.StoredProcedure
sc.CommandText = "YourProcsName"
sc.Connection = myconn

3. Setting your parameters and add them to SqlCommand object.

Dim sp1 As New SqlParameter()
sp1.ParameterName = "Parameter1"
sp1.Value = ""

Dim sp2 As New SqlParameter()
sp2.ParameterName = "Parameter2"
sp2.Value = ""

Dim sp3 As New SqlParameter()
sp3.ParameterName = "Parameter3"
sp3.Size = 10
sp3.Direction = ParameterDirection.Output

sc.Parameters.Add(sp1)
sc.Parameters.Add(sp2)
sc.Parameters.Add(sp3)

4. Open the connection, execute the process, and get the output parameter.

myconn.Open()
sc.ExecuteNonQuery()
myconn.Close()
Dim c As String = sp.Value.ToString()

After all, you can get the output parameter from the variable C.

Besides, this is a WebForm support forum, if you are developing WindowForm application, it would be better for you to go to MSDN forum where you can get more help.

Thanks.

|||

Thanks for your reply - it has been a big help.

Phil

Problem: Using Custom Code to provide default values for parameter

Hi, I have reports with parameters that use custom code, and when you hide
the parameters in the report manager the reports no longer work.
I thought it was a parameter dependancy problem, but have done the following
test to prove its not:
I have just created a report, with one parameter called prmUserID that gets
its value from my dll. The default non-queried value for this parameter is
set as follows:
=libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True , False,
False )
The report displays a table, and the table references a dataset that uses
the parameter value. This is the string for the dataset:
="Select * from mhgroup.docusers where userid='" &
Parameters!prmUserID.Value & "'"
The report works fine when I deploy it to the report
manager. The parameter shows the value returned from my
dll correctly.
However, I changed the parameter properties of my report in report manager
and took the tick out of the prompt user checkbox. The report no longer
works - I'm pretty sure that my prmUserID is just parsing an empty string now
to the dataset. Maybe once you've hidden a parameter the code from my dll is
no longer called?
I also know its not a problem with my dll as I did the same test above but
used custom code directly in the report (ie added to the code tab of the
report properties dialog). This produced the same result - deployed it,
worked fine, hide the parameter and the code no longer seems to run.
I would be grateful if someone at MS could try and replicate this and let me
know if I'm doing something wrong or if its a bug.
My Firm is VERY keen to move forward with Reporting Services, the printing
issue (which I know is being addressed in SP2) and this parameter problem is
the only thing holding us back at the moment.
Thanks
JeanineHave you installed SP1?... I think that hidden parameters are NOT settable
unless you have SP1 installed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> Hi, I have reports with parameters that use custom code, and when you hide
> the parameters in the report manager the reports no longer work.
> I thought it was a parameter dependancy problem, but have done the
> following
> test to prove its not:
> I have just created a report, with one parameter called prmUserID that
> gets
> its value from my dll. The default non-queried value for this parameter
> is
> set as follows:
> =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> False,
> False )
> The report displays a table, and the table references a dataset that uses
> the parameter value. This is the string for the dataset:
> ="Select * from mhgroup.docusers where userid='" &
> Parameters!prmUserID.Value & "'"
> The report works fine when I deploy it to the report
> manager. The parameter shows the value returned from my
> dll correctly.
> However, I changed the parameter properties of my report in report manager
> and took the tick out of the prompt user checkbox. The report no longer
> works - I'm pretty sure that my prmUserID is just parsing an empty string
> now
> to the dataset. Maybe once you've hidden a parameter the code from my dll
> is
> no longer called?
> I also know its not a problem with my dll as I did the same test above but
> used custom code directly in the report (ie added to the code tab of the
> report properties dialog). This produced the same result - deployed it,
> worked fine, hide the parameter and the code no longer seems to run.
> I would be grateful if someone at MS could try and replicate this and let
> me
> know if I'm doing something wrong or if its a bug.
> My Firm is VERY keen to move forward with Reporting Services, the printing
> issue (which I know is being addressed in SP2) and this parameter problem
> is
> the only thing holding us back at the moment.
> Thanks
> Jeanine|||Thanks for you reply, yes I do have SP1 installed.
"Wayne Snyder" wrote:
> Have you installed SP1?... I think that hidden parameters are NOT settable
> unless you have SP1 installed...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> > Hi, I have reports with parameters that use custom code, and when you hide
> > the parameters in the report manager the reports no longer work.
> >
> > I thought it was a parameter dependancy problem, but have done the
> > following
> > test to prove its not:
> >
> > I have just created a report, with one parameter called prmUserID that
> > gets
> > its value from my dll. The default non-queried value for this parameter
> > is
> > set as follows:
> >
> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> > False,
> > False )
> >
> > The report displays a table, and the table references a dataset that uses
> > the parameter value. This is the string for the dataset:
> >
> > ="Select * from mhgroup.docusers where userid='" &
> > Parameters!prmUserID.Value & "'"
> >
> > The report works fine when I deploy it to the report
> > manager. The parameter shows the value returned from my
> > dll correctly.
> >
> > However, I changed the parameter properties of my report in report manager
> > and took the tick out of the prompt user checkbox. The report no longer
> > works - I'm pretty sure that my prmUserID is just parsing an empty string
> > now
> > to the dataset. Maybe once you've hidden a parameter the code from my dll
> > is
> > no longer called?
> >
> > I also know its not a problem with my dll as I did the same test above but
> > used custom code directly in the report (ie added to the code tab of the
> > report properties dialog). This produced the same result - deployed it,
> > worked fine, hide the parameter and the code no longer seems to run.
> >
> > I would be grateful if someone at MS could try and replicate this and let
> > me
> > know if I'm doing something wrong or if its a bug.
> >
> > My Firm is VERY keen to move forward with Reporting Services, the printing
> > issue (which I know is being addressed in SP2) and this parameter problem
> > is
> > the only thing holding us back at the moment.
> >
> > Thanks
> > Jeanine
>
>|||You need to change the report parameter property. Set the PROMPT string to
"" and the prompt value to TRUE.
It will not display the prompt but at least it will be updatable.
=-Chris
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> Hi, I have reports with parameters that use custom code, and when you hide
> the parameters in the report manager the reports no longer work.
> I thought it was a parameter dependancy problem, but have done the
> following
> test to prove its not:
> I have just created a report, with one parameter called prmUserID that
> gets
> its value from my dll. The default non-queried value for this parameter
> is
> set as follows:
> =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> False,
> False )
> The report displays a table, and the table references a dataset that uses
> the parameter value. This is the string for the dataset:
> ="Select * from mhgroup.docusers where userid='" &
> Parameters!prmUserID.Value & "'"
> The report works fine when I deploy it to the report
> manager. The parameter shows the value returned from my
> dll correctly.
> However, I changed the parameter properties of my report in report manager
> and took the tick out of the prompt user checkbox. The report no longer
> works - I'm pretty sure that my prmUserID is just parsing an empty string
> now
> to the dataset. Maybe once you've hidden a parameter the code from my dll
> is
> no longer called?
> I also know its not a problem with my dll as I did the same test above but
> used custom code directly in the report (ie added to the code tab of the
> report properties dialog). This produced the same result - deployed it,
> worked fine, hide the parameter and the code no longer seems to run.
> I would be grateful if someone at MS could try and replicate this and let
> me
> know if I'm doing something wrong or if its a bug.
> My Firm is VERY keen to move forward with Reporting Services, the printing
> issue (which I know is being addressed in SP2) and this parameter problem
> is
> the only thing holding us back at the moment.
> Thanks
> Jeanine|||Thanks for that. I tried that and it worked - as long as you don't then go
and edit the report parameters in report manager.
What I would really like to do is have linked reports, one with a boolean
parameter set to true and one with a boolean parameter set to false.
So as soon as I use the report manager to alter the report parameter
properties (even though I've done you've said below), the reports don't work
properly again.
I still think this is a bug of some kind, and am hoping MS come up with a
fix for this.
Jeanine
"Christopher Conner" wrote:
> You need to change the report parameter property. Set the PROMPT string to
> "" and the prompt value to TRUE.
> It will not display the prompt but at least it will be updatable.
> =-Chris
>
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> > Hi, I have reports with parameters that use custom code, and when you hide
> > the parameters in the report manager the reports no longer work.
> >
> > I thought it was a parameter dependancy problem, but have done the
> > following
> > test to prove its not:
> >
> > I have just created a report, with one parameter called prmUserID that
> > gets
> > its value from my dll. The default non-queried value for this parameter
> > is
> > set as follows:
> >
> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> > False,
> > False )
> >
> > The report displays a table, and the table references a dataset that uses
> > the parameter value. This is the string for the dataset:
> >
> > ="Select * from mhgroup.docusers where userid='" &
> > Parameters!prmUserID.Value & "'"
> >
> > The report works fine when I deploy it to the report
> > manager. The parameter shows the value returned from my
> > dll correctly.
> >
> > However, I changed the parameter properties of my report in report manager
> > and took the tick out of the prompt user checkbox. The report no longer
> > works - I'm pretty sure that my prmUserID is just parsing an empty string
> > now
> > to the dataset. Maybe once you've hidden a parameter the code from my dll
> > is
> > no longer called?
> >
> > I also know its not a problem with my dll as I did the same test above but
> > used custom code directly in the report (ie added to the code tab of the
> > report properties dialog). This produced the same result - deployed it,
> > worked fine, hide the parameter and the code no longer seems to run.
> >
> > I would be grateful if someone at MS could try and replicate this and let
> > me
> > know if I'm doing something wrong or if its a bug.
> >
> > My Firm is VERY keen to move forward with Reporting Services, the printing
> > issue (which I know is being addressed in SP2) and this parameter problem
> > is
> > the only thing holding us back at the moment.
> >
> > Thanks
> > Jeanine
>
>|||Well first off - users shouldn't have rights to modify the report
parameters.
Second, I set those properties in the report designer - not off the report
manager. It would be a pain to update those reports everytime I did a
deployment.
What happens when you do this inside your report designer with your linked
report?
I'll see if I can replicate your issue here, but otherwise I haven't run
into any problems - albeit just a few quirks like the solution I just gave
you to not show parameters but yet allow them to be updatable.
=-Chris
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:038B93E0-E09F-487E-877F-FC1F2DCDB0E9@.microsoft.com...
> Thanks for that. I tried that and it worked - as long as you don't then
> go
> and edit the report parameters in report manager.
> What I would really like to do is have linked reports, one with a boolean
> parameter set to true and one with a boolean parameter set to false.
> So as soon as I use the report manager to alter the report parameter
> properties (even though I've done you've said below), the reports don't
> work
> properly again.
> I still think this is a bug of some kind, and am hoping MS come up with a
> fix for this.
> Jeanine
>
> "Christopher Conner" wrote:
>> You need to change the report parameter property. Set the PROMPT string
>> to
>> "" and the prompt value to TRUE.
>> It will not display the prompt but at least it will be updatable.
>> =-Chris
>>
>> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
>> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
>> > Hi, I have reports with parameters that use custom code, and when you
>> > hide
>> > the parameters in the report manager the reports no longer work.
>> >
>> > I thought it was a parameter dependancy problem, but have done the
>> > following
>> > test to prove its not:
>> >
>> > I have just created a report, with one parameter called prmUserID that
>> > gets
>> > its value from my dll. The default non-queried value for this
>> > parameter
>> > is
>> > set as follows:
>> >
>> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
>> > False,
>> > False )
>> >
>> > The report displays a table, and the table references a dataset that
>> > uses
>> > the parameter value. This is the string for the dataset:
>> >
>> > ="Select * from mhgroup.docusers where userid='" &
>> > Parameters!prmUserID.Value & "'"
>> >
>> > The report works fine when I deploy it to the report
>> > manager. The parameter shows the value returned from my
>> > dll correctly.
>> >
>> > However, I changed the parameter properties of my report in report
>> > manager
>> > and took the tick out of the prompt user checkbox. The report no
>> > longer
>> > works - I'm pretty sure that my prmUserID is just parsing an empty
>> > string
>> > now
>> > to the dataset. Maybe once you've hidden a parameter the code from my
>> > dll
>> > is
>> > no longer called?
>> >
>> > I also know its not a problem with my dll as I did the same test above
>> > but
>> > used custom code directly in the report (ie added to the code tab of
>> > the
>> > report properties dialog). This produced the same result - deployed
>> > it,
>> > worked fine, hide the parameter and the code no longer seems to run.
>> >
>> > I would be grateful if someone at MS could try and replicate this and
>> > let
>> > me
>> > know if I'm doing something wrong or if its a bug.
>> >
>> > My Firm is VERY keen to move forward with Reporting Services, the
>> > printing
>> > issue (which I know is being addressed in SP2) and this parameter
>> > problem
>> > is
>> > the only thing holding us back at the moment.
>> >
>> > Thanks
>> > Jeanine
>>|||Hi,
1) Users will not be modifying report parameters settings. They will
however be able to use the prms that I set as being visible.
2) What I'm trying to achieve is to have 1 main report and create from this,
several linked reports. By linked reports, I'm referring to the ability to
create a linked report from another report in Report manager. These linked
reports would have different parameter settings. This way I can use the
parameter information to tailor 1 main report, dependant upon the parameter
values. This saves me from having to create multiple reports in the report
designer, that contain basically the same information.
I tried your suggestion, with one of my production reports that has quite a
few parameters, and it worked fine. However to achieve 2) I will need to
create from this a linked report, change the parameter settings - which will
involve things such as changing a boolean prm from true to false etc.
If it is indeed a problem it is very easy to replicate, just create a report
with a parameter and set the default value of the parameter to a string
returned from a function in the custom code of the report. Use this
parameter in the datasource of the report, eg, ="Select * from
mhgroup.docusers where userid='" &
Parameters!prmUserID.Value & "'"
Deploy the report, use the report manager to hide the parameter, and the
report should then no longer display the correct information (because the
default value of the parameter was not populated with the custom code value).
Thanks very much.
"Christopher Conner" wrote:
> Well first off - users shouldn't have rights to modify the report
> parameters.
> Second, I set those properties in the report designer - not off the report
> manager. It would be a pain to update those reports everytime I did a
> deployment.
> What happens when you do this inside your report designer with your linked
> report?
> I'll see if I can replicate your issue here, but otherwise I haven't run
> into any problems - albeit just a few quirks like the solution I just gave
> you to not show parameters but yet allow them to be updatable.
> =-Chris
>
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:038B93E0-E09F-487E-877F-FC1F2DCDB0E9@.microsoft.com...
> > Thanks for that. I tried that and it worked - as long as you don't then
> > go
> > and edit the report parameters in report manager.
> >
> > What I would really like to do is have linked reports, one with a boolean
> > parameter set to true and one with a boolean parameter set to false.
> >
> > So as soon as I use the report manager to alter the report parameter
> > properties (even though I've done you've said below), the reports don't
> > work
> > properly again.
> >
> > I still think this is a bug of some kind, and am hoping MS come up with a
> > fix for this.
> >
> > Jeanine
> >
> >
> > "Christopher Conner" wrote:
> >
> >> You need to change the report parameter property. Set the PROMPT string
> >> to
> >> "" and the prompt value to TRUE.
> >>
> >> It will not display the prompt but at least it will be updatable.
> >>
> >> =-Chris
> >>
> >>
> >>
> >> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> >> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> >> > Hi, I have reports with parameters that use custom code, and when you
> >> > hide
> >> > the parameters in the report manager the reports no longer work.
> >> >
> >> > I thought it was a parameter dependancy problem, but have done the
> >> > following
> >> > test to prove its not:
> >> >
> >> > I have just created a report, with one parameter called prmUserID that
> >> > gets
> >> > its value from my dll. The default non-queried value for this
> >> > parameter
> >> > is
> >> > set as follows:
> >> >
> >> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> >> > False,
> >> > False )
> >> >
> >> > The report displays a table, and the table references a dataset that
> >> > uses
> >> > the parameter value. This is the string for the dataset:
> >> >
> >> > ="Select * from mhgroup.docusers where userid='" &
> >> > Parameters!prmUserID.Value & "'"
> >> >
> >> > The report works fine when I deploy it to the report
> >> > manager. The parameter shows the value returned from my
> >> > dll correctly.
> >> >
> >> > However, I changed the parameter properties of my report in report
> >> > manager
> >> > and took the tick out of the prompt user checkbox. The report no
> >> > longer
> >> > works - I'm pretty sure that my prmUserID is just parsing an empty
> >> > string
> >> > now
> >> > to the dataset. Maybe once you've hidden a parameter the code from my
> >> > dll
> >> > is
> >> > no longer called?
> >> >
> >> > I also know its not a problem with my dll as I did the same test above
> >> > but
> >> > used custom code directly in the report (ie added to the code tab of
> >> > the
> >> > report properties dialog). This produced the same result - deployed
> >> > it,
> >> > worked fine, hide the parameter and the code no longer seems to run.
> >> >
> >> > I would be grateful if someone at MS could try and replicate this and
> >> > let
> >> > me
> >> > know if I'm doing something wrong or if its a bug.
> >> >
> >> > My Firm is VERY keen to move forward with Reporting Services, the
> >> > printing
> >> > issue (which I know is being addressed in SP2) and this parameter
> >> > problem
> >> > is
> >> > the only thing holding us back at the moment.
> >> >
> >> > Thanks
> >> > Jeanine
> >>
> >>
> >>
>
>|||i myself are having a similar problem
i believe its a BUG in report manager,
that changing any of the options for parameters in report manager
(which is what is needed to be done to make a writable hidden
parameter) causes default calculations (even query based) to not work
for parameters that are not pull down boxes.|||Thanks for that. Its nice to know I'm not the only one experiencing this
problem. I'm hoping somone from MS may be able to shed some light on this as
well.
"klumsy@.xtra.co.nz" wrote:
> i myself are having a similar problem
> i believe its a BUG in report manager,
> that changing any of the options for parameters in report manager
> (which is what is needed to be done to make a writable hidden
> parameter) causes default calculations (even query based) to not work
> for parameters that are not pull down boxes.
>|||Please check this related thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5d83b939-71ab-47fa-9f85-9d15d90dacb9
Also check my recent posting in that same thread for a possible workaround
on RS 2000 (specifying hidden parameters through report designer).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:F1B09944-8648-49FD-95AE-66004ECD212D@.microsoft.com...
> Thanks for that. Its nice to know I'm not the only one experiencing this
> problem. I'm hoping somone from MS may be able to shed some light on this
> as
> well.
>
> "klumsy@.xtra.co.nz" wrote:
>> i myself are having a similar problem
>> i believe its a BUG in report manager,
>> that changing any of the options for parameters in report manager
>> (which is what is needed to be done to make a writable hidden
>> parameter) causes default calculations (even query based) to not work
>> for parameters that are not pull down boxes.
>>|||Thanks Robert. I'll look forward to the release of Yukon.
"Robert Bruckner [MSFT]" wrote:
> Please check this related thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5d83b939-71ab-47fa-9f85-9d15d90dacb9
> Also check my recent posting in that same thread for a possible workaround
> on RS 2000 (specifying hidden parameters through report designer).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:F1B09944-8648-49FD-95AE-66004ECD212D@.microsoft.com...
> > Thanks for that. Its nice to know I'm not the only one experiencing this
> > problem. I'm hoping somone from MS may be able to shed some light on this
> > as
> > well.
> >
> >
> > "klumsy@.xtra.co.nz" wrote:
> >
> >> i myself are having a similar problem
> >> i believe its a BUG in report manager,
> >> that changing any of the options for parameters in report manager
> >> (which is what is needed to be done to make a writable hidden
> >> parameter) causes default calculations (even query based) to not work
> >> for parameters that are not pull down boxes.
> >>
> >>
>
>

PROBLEM: Report parameters can not be set. Default values always take priority.

I am trying to create a report with parameters which are prompted by the user and are initially populated with default values. However, whenever I edit the parameters in Report Viewer and click 'View Report', the parameter values revert to their default value. The user-specified parameters never "stick".

Please help!

The xml looks like this:

<ReportParameters>

<ReportParameter Name="ConnectString">

<DataType>String</DataType>

<Prompt>ConnectString</Prompt>

<Hidden>true</Hidden>

</ReportParameter>

<ReportParameter Name="FromDate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>2006-1-1</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>From:</Prompt>

</ReportParameter>

<ReportParameter Name="ToDate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>2006-12-31</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>To:</Prompt>

</ReportParameter>

</ReportParameters>

One more observation: It works within Report Manager. The problem is when the report is hosted in my application. It seems there might be something in my code that is causing the user-specified parameters to be ignored.

I tried eliminating SetParameters call for the one hidden parameter, and I made sure I was setting ShowParameterPrompts attribute once (in case changing value would reset parameters). The problem still exists.

|||One possible cause is that you are setting the report path or report server url after the LoadViewState event on the postback. If you do this, the report viewer control will see this as a change in the report definition and reload the report from the server, ignoring any input from the postback because it applies to the "old report". Try setting the report path/server url during OnInit, or only when Page.IsPostBack is false.|||

Thank you Brian! That worked!

I verified that either setting ServerReport properties in OnInit(), or after checking Page.IsPostBack is false in Page_Load(), will fix this problem. I also had to call SetParameters within these same constraints.

On to new problems...

Problem with using report parameters in SQL query function, SSRS 2

Using Visual Studio to build reports for Reporting Services 2000: is it
allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
select function(@.variable) from table where ..
I get "syntax error or access violation" when trying.
Regards, JouniI think that question would be more appropriate in the sqlserver.programming
group. With that said...
Have you tried running that query in Query Analyzer?
yeah...I don't think that Sql statement will ever run.
from the Books Online for CREATE FUNCTION (transact SQL):
"@.parameter_name
Is a parameter in the user-defined function. One or more parameters can be
declared.
[...]
Specify a parameter name by using an at sign (@.) as the first character. The
parameter name must comply with the rules for identifiers. Parameters are
local to the function; the same parameter names can be used in other
functions. Parameters can take the place only of constants; they cannot be
used instead of table names, column names, or the names of other database
objects. "
Soooo...assuming your user-defined function is a scalar-value function that
returns field names and is owned by "dbo" schema, try using dynamic sql,
something like this:
DECLARE @.sql varchar(8000)
SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
SET @.sql= @.sql + ' FROM MyTable '
SET @.sql= @.sql + ' WHERE ... '
EXEC @.sql
Give it a shot, and if I am way off...reply and let me know. Hope this help
you.
--
Regards,
Thiago Silva
"Jouni" wrote:
> Using Visual Studio to build reports for Reporting Services 2000: is it
> allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> select function(@.variable) from table where ..
> I get "syntax error or access violation" when trying.
> Regards, Jouni|||Hi Thiago,
haven't had time to test this. Actually I already switched to another
approach: defining the whole query as a db procedure and just calling that:
exec jounis_procedure @.report_parameter
I noticed that Microsoft people have chosen this approach themselves: in
"Report Pack for SPS" all the reports use procedure calls.
Regards,
Jouni
"HC" wrote:
> I think that question would be more appropriate in the sqlserver.programming
> group. With that said...
> Have you tried running that query in Query Analyzer?
> yeah...I don't think that Sql statement will ever run.
> from the Books Online for CREATE FUNCTION (transact SQL):
> "@.parameter_name
> Is a parameter in the user-defined function. One or more parameters can be
> declared.
> [...]
> Specify a parameter name by using an at sign (@.) as the first character. The
> parameter name must comply with the rules for identifiers. Parameters are
> local to the function; the same parameter names can be used in other
> functions. Parameters can take the place only of constants; they cannot be
> used instead of table names, column names, or the names of other database
> objects. "
> Soooo...assuming your user-defined function is a scalar-value function that
> returns field names and is owned by "dbo" schema, try using dynamic sql,
> something like this:
> DECLARE @.sql varchar(8000)
> SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
> SET @.sql= @.sql + ' FROM MyTable '
> SET @.sql= @.sql + ' WHERE ... '
> EXEC @.sql
> Give it a shot, and if I am way off...reply and let me know. Hope this help
> you.
> --
> Regards,
> Thiago Silva
> "Jouni" wrote:
> > Using Visual Studio to build reports for Reporting Services 2000: is it
> > allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> >
> > select function(@.variable) from table where ..
> >
> > I get "syntax error or access violation" when trying.
> >
> > Regards, Jouni

problem with Update command with parameters

Hi,

i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.
This happens in event DetailsView1_ItemUpdating with my own code.
It works without parameters (i know, bad way) like this:
SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"

But when using parameters like here below, i get the error:
"Input string was not in a correct format"

Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles
DetailsView1.ItemUpdating
SqlDataSource1.UpdateCommand = "UPDATE mytable set name= @.myname"
SqlDataSource1.UpdateParameters.Add("@.myname", SqlDbType.NVarChar, na)
SqlDataSource1.Update()
End Sub

I don't see what's wrong here.
Thanks for help
Tartuffe

Try the following:

SqlDataSource1.UpdateParameters.Add("@.myname", SqlDbType.NVarChar, 5).value = na

|||

Set the updatecommand and parameter types in the sqldatasource.

In the detailsview.Updating event, you can play with the e.NewValues, e.OldValues collections to make any changes you want.

Or you can make your changes in the sqldatasource.updating event and play with the parameters directly via e.Command.Parameters.

Much cleaner.

|||

Thanks to reply.

I tried this:

SqlDataSource1.UpdateParameters.Add("@.myname", SqlDbType.NVarChar, 5).value = na

but in the Intellisense, 'value' doesn't appear, and when i force it in the code, i ger the strange error:

'value' is not a memeber of 'integer' ...

|||

Hi tartuffe2,

SqlDataSource.UpdateParameters.Add() will return the index value of the added item in your Sql Datasource Parameter Collection, thus you cannot assign a new value to it.

As to add a new value into a parameter collection, i think your origional way is correct ( SqlDataSource.UpdateParameters.Add("@.parametername",SqlDBType.NVChar,na) ). I cannot see any errors in the code you origionally posted. Have you tried the methodMotley suggested? That maybe is able to solve your problems. thanks