Showing posts with label parameters. Show all posts
Showing posts with label parameters. Show all posts

Friday, March 30, 2012

How to value parameters for sqldatasource

I set up a sqldatasource based on a stored procedure which takes one parameter. The sqldatasrouce wizard generates the following code for the parameter below. The question is how do I value the DeptID parameter on the load of the form. I tried the following code in the load of the page, but get a null reference error:

Me.SqlDataSource1.InsertParameters("DeptID").DefaultValue = Session("DeptID")

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:FDConn %>"
SelectCommand="GetTruckStatus" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="DeptID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<radG:RadGrid ID="RadGrid1" runat="server">
</radG:RadGrid>

You've set the value of an InsertParameter, but you are performing a Select, not an Insert. Anyway, the SqlDataSource offers a number of different parameter types. One of them is an <asp:SessionParameter>. Use that instead of the generic<asp:Parameter> that you are currently using. You can reconfigure the SqlDataSource to generate it automatically. Click the WHERE button when applying the Select Statement, and in the Source dropdown, choose Session.

|||

Mike,
Thanks for the information. I went back into the configure the sqldatasrouce, but if you chose to use a stored procedure, the button for selecting "Where" is grayed out. I assumed that since the code was generated from vb and from the stored procedure I selected, vb would know the type of parameter it had to generate.


Regards,
Tom

|||

Mike,

I went back into the code and used the "SelectParameter" type and all worked well. Thanks for pointing me in the right direction.

Tom

Wednesday, March 28, 2012

How to validate Report Parameters

Hi,

I am designing a report using SRS 2000. I want to validate parameters before executing the query. Suppose if user gives a random number for parameter (say Emp ID), i want to display a error message in a textbox "There are no records for the given employee ID" and stop executing query to display employee details in the table.

I tried using Visible expression for table IIF(Fields!EmpID = "", False, True). But when I run the report with a random number, an error populated saying "You cannot query a record for no data..."

Please suggest me a work around.

Regards,

One workaround may be to use a stored procedure and perform validation within it. Return a result set that provides either a data result or error values within your dataset. Use visibility expressions on the reporting services to show-hide either the table that returns the data or the table that returns the error message.

eg.

if @.myparam = 'bad'

select 'bad' as column1

else

select column1 from mytable

then you do a check for the first value of column1. If it's bad show the error table, otherwise show the regular table.

cheers,

Andrew

|||

Hi Chiro,

Here's how it works.

In your table properties, locate the NoRows properties of the table. Place your message.

Note: If you use this you cannot change the font color because reporting services does have a color property for this.

or

Try this

Add a Rectangle into the body of your report

In the Visibility property of the Rectangle, locate the Hidden and click expression.

Place this in your Rectangle

=iif( Sum(Fields!EmpID.Value, "Your Dataset")>0, False, True)

Then try running the report.

|||

Thanks for the reply. I managed it by using 2 datasets, 1 for validation parameters and 1 for getting data. I dont know why the above method which u have suggested is throwing error "You cannot fetch records for no rows or some thing..." when i try entering some junk characters like "23423daedfsrewAFSD".

Any way. Thanks for the reply.

Regards

How to Validate parameters in SQL Reporting Services

Can I apply validations to the parms in SQL Reporting Services?
Example:
I create a report that has two parameters listed:
FromDate
ToDate
When I run this report, user can key in any date he wishes. I need to apply
validations to the report:
1) FromDate must be less than ToDate
2) Dates must be in valid format
3) Date range cannot be more than 6 months
Can anyone tell me how to do this?
--
Thanks for your response.
JrYou could make the dates both dropdowns and make the "To Date" dependent on
the "From Date"
ex:
// data source for "From date"
select DISTINCT datePosted
from mytable
// date source for "To Date"
select distinct datePosted
from mytable
where datePosted > @.FromDate
and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
"JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
> Can I apply validations to the parms in SQL Reporting Services?
> Example:
> I create a report that has two parameters listed:
> FromDate
> ToDate
> When I run this report, user can key in any date he wishes. I need to
> apply
> validations to the report:
> 1) FromDate must be less than ToDate
> 2) Dates must be in valid format
> 3) Date range cannot be more than 6 months
> Can anyone tell me how to do this?
> --
> Thanks for your response.
> Jr|||dates are one example but what if there are business rules that need to be
written? cant that be done from reporting services or does it have to be done
via C#?
"Tim Dot NoSpam" wrote:
> You could make the dates both dropdowns and make the "To Date" dependent on
> the "From Date"
> ex:
> // data source for "From date"
> select DISTINCT datePosted
> from mytable
> // date source for "To Date"
> select distinct datePosted
> from mytable
> where datePosted > @.FromDate
> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
> > Can I apply validations to the parms in SQL Reporting Services?
> >
> > Example:
> > I create a report that has two parameters listed:
> > FromDate
> > ToDate
> >
> > When I run this report, user can key in any date he wishes. I need to
> > apply
> > validations to the report:
> > 1) FromDate must be less than ToDate
> > 2) Dates must be in valid format
> > 3) Date range cannot be more than 6 months
> >
> > Can anyone tell me how to do this?
> >
> > --
> > Thanks for your response.
> > Jr
>
>|||Class consultant response; "It Depends".
Really, it depends on what the rules are.
One such rule might be (this is from a real spec): "Display the current
season first followed by the next 2 future seasons in ascending order.
Finally, append all other seasons in reverse chronological order". This
rule can be met using TSQL (although it took me 10 minutes just to code the
sql for a parameter query).
Really complex business rules may best be written in C# and handled outside
of the report, then passed to it sans parameter toolbar. I honestly haven't
come across any that I couldn't handle in TSQL though. Can you give me an
example of some you've dealt with?
"don" <don@.discussions.microsoft.com> wrote in message
news:9CF3705B-AA2C-4070-9EA9-A4D4E1F5EC4E@.microsoft.com...
> dates are one example but what if there are business rules that need to be
> written? cant that be done from reporting services or does it have to be
> done
> via C#?
>
> "Tim Dot NoSpam" wrote:
>> You could make the dates both dropdowns and make the "To Date" dependent
>> on
>> the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> > Can I apply validations to the parms in SQL Reporting Services?
>> >
>> > Example:
>> > I create a report that has two parameters listed:
>> > FromDate
>> > ToDate
>> >
>> > When I run this report, user can key in any date he wishes. I need to
>> > apply
>> > validations to the report:
>> > 1) FromDate must be less than ToDate
>> > 2) Dates must be in valid format
>> > 3) Date range cannot be more than 6 months
>> >
>> > Can anyone tell me how to do this?
>> >
>> > --
>> > Thanks for your response.
>> > Jr
>>|||I'll chime back in. I don't fully understand your example below for the
dates. Are you saying you have a table with all dates in it? If I have a
FROM and TO date and I need the TO to be greater than the from, then how are
you loading the drop down for the TO date? Does this table contain all the
dates in time? I guess if I can understand how to do this date logic, then
most rules could be handled with TSQL. However, I don't know enuff yet to
know how to conditionally load a drop. Example: Dropdown1 has customers in
it. I want to load DropDown2 based on the customer selected in DropDown1.
How do you make the dropdown load conditioned on the first one?
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:uOWszXemGHA.2316@.TK2MSFTNGP04.phx.gbl...
> Class consultant response; "It Depends".
> Really, it depends on what the rules are.
> One such rule might be (this is from a real spec): "Display the current
> season first followed by the next 2 future seasons in ascending order.
> Finally, append all other seasons in reverse chronological order". This
> rule can be met using TSQL (although it took me 10 minutes just to code
> the sql for a parameter query).
> Really complex business rules may best be written in C# and handled
> outside of the report, then passed to it sans parameter toolbar. I
> honestly haven't come across any that I couldn't handle in TSQL though.
> Can you give me an example of some you've dealt with?
>
> "don" <don@.discussions.microsoft.com> wrote in message
> news:9CF3705B-AA2C-4070-9EA9-A4D4E1F5EC4E@.microsoft.com...
>> dates are one example but what if there are business rules that need to
>> be
>> written? cant that be done from reporting services or does it have to be
>> done
>> via C#?
>>
>> "Tim Dot NoSpam" wrote:
>> You could make the dates both dropdowns and make the "To Date" dependent
>> on
>> the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> > Can I apply validations to the parms in SQL Reporting Services?
>> >
>> > Example:
>> > I create a report that has two parameters listed:
>> > FromDate
>> > ToDate
>> >
>> > When I run this report, user can key in any date he wishes. I need to
>> > apply
>> > validations to the report:
>> > 1) FromDate must be less than ToDate
>> > 2) Dates must be in valid format
>> > 3) Date range cannot be more than 6 months
>> >
>> > Can anyone tell me how to do this?
>> >
>> > --
>> > Thanks for your response.
>> > Jr
>>
>|||Here you go.
> Example: Dropdown1 has customers in it. I want to load DropDown2 based
> on the customer selected in DropDown1. How do you make the dropdown load
> conditioned on the first one?
Let's suppose that @.CustomerId is the parameter to be used in the report.
Also, @.BillingPeriod is used in the report and is related to customer via a
table called "CustomerBillingPeriod".
the sql for the Customer parameter comes from the dataset dsCustomer:
select CustomerID,
CustomerFullName
from CustomerTable
the sql for the BillingPeriod parameter comes from the dataset
dsBillingPeriod:
select b.BillingPeriodID,
b.BillingPeriodDesc
from BillingPeriod b
INNER JOIN CustomerBillingPeriod cb
ON b.BillingPeriodID = cb.BillingPeriodID
WHERE cb.CustomerID = @.CustomerID
The billingPeriod parameter has now become a dependent parameter, dependent
on the selection of Customer.
There's a trade-off here though. The more dependent parameters you have in
a report, the more complicated the report becomes. You now have to
replicate portions of the TSQL for the report to help filter the report
based on parameters that will actually yield a report. If, on the other
hand, you made every parameter independent of the others, you now invite the
user to select parameters that yield no results whatsoever. This is
sometimes very frustrating, especially when a report takes longer than a
couple of minutes to run. On the positive side, the user is guided to their
report by the absense of parameter values that would otherwise give them the
"No data is available for the parameters you specified".
That's a paragraph from a book I'm going to write one day. <g>
-Tim
Say for example, that
"Jr" <JrM@.noemail.noemail> wrote in message
news:ezUBA9fmGHA.856@.TK2MSFTNGP03.phx.gbl...
> I'll chime back in. I don't fully understand your example below for the
> dates. Are you saying you have a table with all dates in it? If I have a
> FROM and TO date and I need the TO to be greater than the from, then how
> are you loading the drop down for the TO date? Does this table contain
> all the dates in time? I guess if I can understand how to do this date
> logic, then most rules could be handled with TSQL. However, I don't know
> enuff yet to know how to conditionally load a drop. Example: Dropdown1
> has customers in it. I want to load DropDown2 based on the customer
> selected in DropDown1. How do you make the dropdown load conditioned on
> the first one?
>
> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
> news:uOWszXemGHA.2316@.TK2MSFTNGP04.phx.gbl...
>> Class consultant response; "It Depends".
>> Really, it depends on what the rules are.
>> One such rule might be (this is from a real spec): "Display the current
>> season first followed by the next 2 future seasons in ascending order.
>> Finally, append all other seasons in reverse chronological order". This
>> rule can be met using TSQL (although it took me 10 minutes just to code
>> the sql for a parameter query).
>> Really complex business rules may best be written in C# and handled
>> outside of the report, then passed to it sans parameter toolbar. I
>> honestly haven't come across any that I couldn't handle in TSQL though.
>> Can you give me an example of some you've dealt with?
>>
>> "don" <don@.discussions.microsoft.com> wrote in message
>> news:9CF3705B-AA2C-4070-9EA9-A4D4E1F5EC4E@.microsoft.com...
>> dates are one example but what if there are business rules that need to
>> be
>> written? cant that be done from reporting services or does it have to be
>> done
>> via C#?
>>
>> "Tim Dot NoSpam" wrote:
>> You could make the dates both dropdowns and make the "To Date"
>> dependent on
>> the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> > Can I apply validations to the parms in SQL Reporting Services?
>> >
>> > Example:
>> > I create a report that has two parameters listed:
>> > FromDate
>> > ToDate
>> >
>> > When I run this report, user can key in any date he wishes. I need
>> > to
>> > apply
>> > validations to the report:
>> > 1) FromDate must be less than ToDate
>> > 2) Dates must be in valid format
>> > 3) Date range cannot be more than 6 months
>> >
>> > Can anyone tell me how to do this?
>> >
>> > --
>> > Thanks for your response.
>> > Jr
>>
>>
>|||I think i've got now. Thanks so much for your help.
PS - let me know when the book gets published!
Jr
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:ueg2jYhmGHA.4992@.TK2MSFTNGP03.phx.gbl...
> Here you go.
>> Example: Dropdown1 has customers in it. I want to load DropDown2 based
>> on the customer selected in DropDown1. How do you make the dropdown load
>> conditioned on the first one?
> Let's suppose that @.CustomerId is the parameter to be used in the report.
> Also, @.BillingPeriod is used in the report and is related to customer via
> a table called "CustomerBillingPeriod".
> the sql for the Customer parameter comes from the dataset dsCustomer:
> select CustomerID,
> CustomerFullName
> from CustomerTable
> the sql for the BillingPeriod parameter comes from the dataset
> dsBillingPeriod:
> select b.BillingPeriodID,
> b.BillingPeriodDesc
> from BillingPeriod b
> INNER JOIN CustomerBillingPeriod cb
> ON b.BillingPeriodID = cb.BillingPeriodID
> WHERE cb.CustomerID = @.CustomerID
>
> The billingPeriod parameter has now become a dependent parameter,
> dependent on the selection of Customer.
>
> There's a trade-off here though. The more dependent parameters you have
> in a report, the more complicated the report becomes. You now have to
> replicate portions of the TSQL for the report to help filter the report
> based on parameters that will actually yield a report. If, on the other
> hand, you made every parameter independent of the others, you now invite
> the user to select parameters that yield no results whatsoever. This is
> sometimes very frustrating, especially when a report takes longer than a
> couple of minutes to run. On the positive side, the user is guided to
> their report by the absense of parameter values that would otherwise give
> them the "No data is available for the parameters you specified".
> That's a paragraph from a book I'm going to write one day. <g>
> -Tim
> Say for example, that
> "Jr" <JrM@.noemail.noemail> wrote in message
> news:ezUBA9fmGHA.856@.TK2MSFTNGP03.phx.gbl...
>> I'll chime back in. I don't fully understand your example below for the
>> dates. Are you saying you have a table with all dates in it? If I have
>> a FROM and TO date and I need the TO to be greater than the from, then
>> how are you loading the drop down for the TO date? Does this table
>> contain all the dates in time? I guess if I can understand how to do
>> this date logic, then most rules could be handled with TSQL. However, I
>> don't know enuff yet to know how to conditionally load a drop. Example:
>> Dropdown1 has customers in it. I want to load DropDown2 based on the
>> customer selected in DropDown1. How do you make the dropdown load
>> conditioned on the first one?
>>
>> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
>> news:uOWszXemGHA.2316@.TK2MSFTNGP04.phx.gbl...
>> Class consultant response; "It Depends".
>> Really, it depends on what the rules are.
>> One such rule might be (this is from a real spec): "Display the current
>> season first followed by the next 2 future seasons in ascending order.
>> Finally, append all other seasons in reverse chronological order". This
>> rule can be met using TSQL (although it took me 10 minutes just to code
>> the sql for a parameter query).
>> Really complex business rules may best be written in C# and handled
>> outside of the report, then passed to it sans parameter toolbar. I
>> honestly haven't come across any that I couldn't handle in TSQL though.
>> Can you give me an example of some you've dealt with?
>>
>> "don" <don@.discussions.microsoft.com> wrote in message
>> news:9CF3705B-AA2C-4070-9EA9-A4D4E1F5EC4E@.microsoft.com...
>> dates are one example but what if there are business rules that need to
>> be
>> written? cant that be done from reporting services or does it have to
>> be done
>> via C#?
>>
>> "Tim Dot NoSpam" wrote:
>> You could make the dates both dropdowns and make the "To Date"
>> dependent on
>> the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> > Can I apply validations to the parms in SQL Reporting Services?
>> >
>> > Example:
>> > I create a report that has two parameters listed:
>> > FromDate
>> > ToDate
>> >
>> > When I run this report, user can key in any date he wishes. I need
>> > to
>> > apply
>> > validations to the report:
>> > 1) FromDate must be less than ToDate
>> > 2) Dates must be in valid format
>> > 3) Date range cannot be more than 6 months
>> >
>> > Can anyone tell me how to do this?
>> >
>> > --
>> > Thanks for your response.
>> > Jr
>>
>>
>>
>|||Tim-
One more question on this part. I understand the customer and billingperiod
example, but what about dates:
1) how do i prevent user from keying 2/32/2006?
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:erXqGFTmGHA.1568@.TK2MSFTNGP05.phx.gbl...
> You could make the dates both dropdowns and make the "To Date" dependent
> on the "From Date"
> ex:
> // data source for "From date"
> select DISTINCT datePosted
> from mytable
> // date source for "To Date"
> select distinct datePosted
> from mytable
> where datePosted > @.FromDate
> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> Can I apply validations to the parms in SQL Reporting Services?
>> Example:
>> I create a report that has two parameters listed:
>> FromDate
>> ToDate
>> When I run this report, user can key in any date he wishes. I need to
>> apply
>> validations to the report:
>> 1) FromDate must be less than ToDate
>> 2) Dates must be in valid format
>> 3) Date range cannot be more than 6 months
>> Can anyone tell me how to do this?
>> --
>> Thanks for your response.
>> Jr
>|||If they're dropdowns, the user can't key in an invalid date.
As an aside, you could also use custom code to perform parameter validation
although this is a real pain in the posterior and you may spend more time
working around that than actually building a good report, but,
You ~could~ have a textbox above your report visualization (i.e., Table,
Matrix, List) and add 2 methods to the report in the code section:
' assume you have date1 and date2 as parameters
Public shared function IsValidParameterList(date1 as string, date2 as
string) as boolean
dim startDate as dateTme = datetime.parse(date1)
dim endDate as dateTime = dateime.Parse(date2)
if ( endDate < startDate) then
return false
end if
return true
End function
Public shared function GetInvalidParameterMessage(date1 as string, date2 as
string) as string
Dim rtnvalue as string
dim startDate as dateTme = datetime.parse(date1)
dim endDate as dateTime = dateime.Parse(date2)
if ( endDate < startDate) then
rtnvalue = "End date must be greater than start date"
end if
return rtnvalue
End function
Then in your report, the textbox would have visibility expression ="=(Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
Parameters!EndDate.Value.ToString()))"
And your report visualization would have visibility expression = "=(NOT
Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
Parameters!EndDate.Value.ToString()))"
This is purely an academic exercise. Like I said, you could spend almost as
much time doing validation like this as you could giving the user a good
report and an hour of training on how to use it. At the end of the day, you
really can't protect a user from themselves but so much. It's cost
prohibitive.
-Tim
"JrMcG" <JrM@.noemail.noemail> wrote in message
news:%23056RurmGHA.3732@.TK2MSFTNGP05.phx.gbl...
> Tim-
> One more question on this part. I understand the customer and
> billingperiod example, but what about dates:
> 1) how do i prevent user from keying 2/32/2006?
> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
> news:erXqGFTmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>> You could make the dates both dropdowns and make the "To Date" dependent
>> on the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> Can I apply validations to the parms in SQL Reporting Services?
>> Example:
>> I create a report that has two parameters listed:
>> FromDate
>> ToDate
>> When I run this report, user can key in any date he wishes. I need to
>> apply
>> validations to the report:
>> 1) FromDate must be less than ToDate
>> 2) Dates must be in valid format
>> 3) Date range cannot be more than 6 months
>> Can anyone tell me how to do this?
>> --
>> Thanks for your response.
>> Jr
>>
>|||Tim -
Thanks. This has been most informative. I think i'll go with the drop down
for teh Ending date, with a date picker on the from date.
Thanks again.
Jr.
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:u5G2uTymGHA.200@.TK2MSFTNGP03.phx.gbl...
> If they're dropdowns, the user can't key in an invalid date.
> As an aside, you could also use custom code to perform parameter
> validation although this is a real pain in the posterior and you may spend
> more time working around that than actually building a good report, but,
> You ~could~ have a textbox above your report visualization (i.e., Table,
> Matrix, List) and add 2 methods to the report in the code section:
> ' assume you have date1 and date2 as parameters
> Public shared function IsValidParameterList(date1 as string, date2 as
> string) as boolean
> dim startDate as dateTme = datetime.parse(date1)
> dim endDate as dateTime = dateime.Parse(date2)
> if ( endDate < startDate) then
> return false
> end if
> return true
> End function
> Public shared function GetInvalidParameterMessage(date1 as string, date2
> as string) as string
> Dim rtnvalue as string
> dim startDate as dateTme = datetime.parse(date1)
> dim endDate as dateTime = dateime.Parse(date2)
> if ( endDate < startDate) then
> rtnvalue = "End date must be greater than start date"
> end if
> return rtnvalue
> End function
> Then in your report, the textbox would have visibility expression => "=(Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
> Parameters!EndDate.Value.ToString()))"
> And your report visualization would have visibility expression = "=(NOT
> Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
> Parameters!EndDate.Value.ToString()))"
> This is purely an academic exercise. Like I said, you could spend almost
> as much time doing validation like this as you could giving the user a
> good report and an hour of training on how to use it. At the end of the
> day, you really can't protect a user from themselves but so much. It's
> cost prohibitive.
> -Tim
>
> "JrMcG" <JrM@.noemail.noemail> wrote in message
> news:%23056RurmGHA.3732@.TK2MSFTNGP05.phx.gbl...
>> Tim-
>> One more question on this part. I understand the customer and
>> billingperiod example, but what about dates:
>> 1) how do i prevent user from keying 2/32/2006?
>> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
>> news:erXqGFTmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>> You could make the dates both dropdowns and make the "To Date" dependent
>> on the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> Can I apply validations to the parms in SQL Reporting Services?
>> Example:
>> I create a report that has two parameters listed:
>> FromDate
>> ToDate
>> When I run this report, user can key in any date he wishes. I need to
>> apply
>> validations to the report:
>> 1) FromDate must be less than ToDate
>> 2) Dates must be in valid format
>> 3) Date range cannot be more than 6 months
>> Can anyone tell me how to do this?
>> --
>> Thanks for your response.
>> Jr
>>
>>
>|||Glad I could help. I've been down both paths and have even been down the
path of doing my own UI for the parameters. That was a big waste of time
for little payback. IMHO, there's more business value in a solid report
with meaningful information rather than a flashy UI that captures
parameters.
_T
"JrMcG" <JrM@.noemail.noemail> wrote in message
news:Oxdk2$2mGHA.3376@.TK2MSFTNGP03.phx.gbl...
> Tim -
> Thanks. This has been most informative. I think i'll go with the drop
> down for teh Ending date, with a date picker on the from date.
> Thanks again.
> Jr.
> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
> news:u5G2uTymGHA.200@.TK2MSFTNGP03.phx.gbl...
>> If they're dropdowns, the user can't key in an invalid date.
>> As an aside, you could also use custom code to perform parameter
>> validation although this is a real pain in the posterior and you may
>> spend more time working around that than actually building a good report,
>> but,
>> You ~could~ have a textbox above your report visualization (i.e., Table,
>> Matrix, List) and add 2 methods to the report in the code section:
>> ' assume you have date1 and date2 as parameters
>> Public shared function IsValidParameterList(date1 as string, date2 as
>> string) as boolean
>> dim startDate as dateTme = datetime.parse(date1)
>> dim endDate as dateTime = dateime.Parse(date2)
>> if ( endDate < startDate) then
>> return false
>> end if
>> return true
>> End function
>> Public shared function GetInvalidParameterMessage(date1 as string, date2
>> as string) as string
>> Dim rtnvalue as string
>> dim startDate as dateTme = datetime.parse(date1)
>> dim endDate as dateTime = dateime.Parse(date2)
>> if ( endDate < startDate) then
>> rtnvalue = "End date must be greater than start date"
>> end if
>> return rtnvalue
>> End function
>> Then in your report, the textbox would have visibility expression =>> "=(Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
>> Parameters!EndDate.Value.ToString()))"
>> And your report visualization would have visibility expression = "=(NOT
>> Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
>> Parameters!EndDate.Value.ToString()))"
>> This is purely an academic exercise. Like I said, you could spend almost
>> as much time doing validation like this as you could giving the user a
>> good report and an hour of training on how to use it. At the end of the
>> day, you really can't protect a user from themselves but so much. It's
>> cost prohibitive.
>> -Tim
>>
>> "JrMcG" <JrM@.noemail.noemail> wrote in message
>> news:%23056RurmGHA.3732@.TK2MSFTNGP05.phx.gbl...
>> Tim-
>> One more question on this part. I understand the customer and
>> billingperiod example, but what about dates:
>> 1) how do i prevent user from keying 2/32/2006?
>> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
>> news:erXqGFTmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>> You could make the dates both dropdowns and make the "To Date"
>> dependent on the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> Can I apply validations to the parms in SQL Reporting Services?
>> Example:
>> I create a report that has two parameters listed:
>> FromDate
>> ToDate
>> When I run this report, user can key in any date he wishes. I need to
>> apply
>> validations to the report:
>> 1) FromDate must be less than ToDate
>> 2) Dates must be in valid format
>> 3) Date range cannot be more than 6 months
>> Can anyone tell me how to do this?
>> --
>> Thanks for your response.
>> Jr
>>
>>
>>
>

How to Validate Parameters

Hi,

I am designing a report using SRS 2000. I want to validate parameters before executing the query. Suppose if user gives a random number for parameter (say Emp ID), i want to display a error message in a textbox "There are no records for the given employee ID" and stop executing query to display employee details in the table.

I tried using Visible expression for table IIF(Fields!EmpID = "", False, True). But when I run the report with a random number, an error populated saying "You cannot query a record for no data..."

Please suggest me a work around.

Regards,

In any data control you can set the NoRows property which will display a defined message (in the property) instead of the data control.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Thank you. It is working for results with no rows but not for invalid rows. ie., if we search for employee details, the employees with no details "No Rows" concept is working, but for employees who doesnt exists, it is not working.

I even tried IF @.@.COUNT = 0 THEN RETURN -3.

But I am not sure how to catch that return value ?

|||

Hi,

from your description you retrieve all data within one dataset, right ? So there are two approaches achieve your goal as the NoRows cannot be tweaked to use the return value as you described.

1. Use two datasets to retrieve first the parent data and afterwards the child data (what you described as details)
2. Place a textbox behind the table for your details with label like "No rows/details found". Check the dataset for the expressions which makes you sure that the datagrid (or whatever data control you are using) and use this expression for toggeling the visibility or the data control, making the control visible which has been in the background.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

how to validate inputput parameters in reporting server

could u please tell me how to validate inputput parameters in
reporting serverSimple validations like values not entered etc... RS validates
automatically.. otherwise no way for complicated validation.
Amarnath
"sbmmohapatra@.gmail.com" wrote:
> could u please tell me how to validate inputput parameters in
> reporting server
>|||The easiest way of course is to use dropdownlists.
Alternatives are to use the user-parameters as input for internal parameters.
F.e. you let the user enter the parameters you then define an (or several)
internal parameter that is/are the result of a validation function (you can
even use custum code to do the validation). The function does the parameter
validation and returns the result to the internal parameter that you use in
your report.
"Amarnath" wrote:
> Simple validations like values not entered etc... RS validates
> automatically.. otherwise no way for complicated validation.
> Amarnath
> "sbmmohapatra@.gmail.com" wrote:
> > could u please tell me how to validate inputput parameters in
> > reporting server
> >
> >

How to use wildcard characters with control parameters

Hi guys,

I have a gridview and textbox. I can filter the grid just fine with the textbox, but I would like to allow the user to type in part of a name.

I thought something like this would work but it doesn't:

Emp_Name LIKE'%'+@.Emp_Name+'%'

Thank you.

That should work|||

That's what I thought too, but it doesn't. I guess I should give more info.

I am using an objectdatasource to populate the gridview. The object is a table adapter. It seems like the wildcards are completely ignored. If I am looking for smith, I can type smith and get the correct results. But if I type smit, nothing gets returned, though I want Smith to be returned. If I actually literally type the wildcard characters into the textbox the results are very unpredictable. Typing %smit% will return the correct result, as will %smi% But if I type %sm% then nothing gets returned. It seems as though the number of characters matter when doing this. And like I said, if I have'%'+@.Emp_Name+'%' in the actual query within the table adapter, the wildcards are ignored. Hopefully there is some kind of solution to this.

Thank you very much.

|||Anyone? Let me know if you need more information.

Friday, March 23, 2012

how to use stored proc to drive data-driven subscription

I am wondering how to use a stored proc that, in the procedure, uses TEMP
TABLES to drive the parameters for a data driven subscriptions.
In other words, I want this stored procedure to return a list of e-mail
addresses to distribute the report to. This procedure gathers this list of
addresses, but in order to do so it needs to execute *another* stored
procedure, putting the data from that stored proc into a TEMP table.
When I run the proc in query analyzer it works fine (no surprise) but when I
tell RS to use it as the query for the subscription it gives me the error:
The dataset cannot be generated. An error occurred while connecting to a
data source, or the query is not valid for the data source.
(rsCannotPrepareQuery) Get Online Help Invalid object name '#step1'.
I am setting up the data-drive subscription as follows:
exec rpt_misc_GetLostBillPayments_emailproc_sp
Here is a copy of the above proc:
<snip>
declare @.emails varchar(255)
set @.emails = 'mymail@.myaddress.com;'
create table #step1
(claim_id int,
bill_id int,
received_date_2 datetime,
modified_date datetime,
bill_amount float,
provider_claim_payment_id int,
provider_payment_created_date datetime)
insert into #step1
(claim_id,
bill_id,
received_date_2,
modified_date,
bill_amount,
provider_claim_payment_id,
provider_payment_created_date)
exec rpt_misc_getlostbillpayments_sp
select top 1 claim_id, @.emails as email from #step1
drop table #step1
Any suggestions?You should be able to do this through the SOAP API (read use rs.exe). The
CreateDataDrivenSubscription does not validate the fields you specify, so as
long as the field names returned at runtime match those used in the data
driven subscriptions' field references it should work.
The UI calls PrepareQuery before creating the subscription to get the fields
list. It sounds like you know what the fields are already, so you might be
able to skip this test. Note that this makes the subscription uneditable in
the UI.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"david boardman" <davidboardman@.discussions.microsoft.com> wrote in message
news:D5877C1D-596C-4E56-A9DF-98D4CB6000F4@.microsoft.com...
>I am wondering how to use a stored proc that, in the procedure, uses TEMP
> TABLES to drive the parameters for a data driven subscriptions.
> In other words, I want this stored procedure to return a list of e-mail
> addresses to distribute the report to. This procedure gathers this list
> of
> addresses, but in order to do so it needs to execute *another* stored
> procedure, putting the data from that stored proc into a TEMP table.
> When I run the proc in query analyzer it works fine (no surprise) but when
> I
> tell RS to use it as the query for the subscription it gives me the error:
> The dataset cannot be generated. An error occurred while connecting to a
> data source, or the query is not valid for the data source.
> (rsCannotPrepareQuery) Get Online Help Invalid object name '#step1'.
> I am setting up the data-drive subscription as follows:
> exec rpt_misc_GetLostBillPayments_emailproc_sp
>
> Here is a copy of the above proc:
> <snip>
> declare @.emails varchar(255)
> set @.emails = 'mymail@.myaddress.com;'
>
> create table #step1
> (claim_id int,
> bill_id int,
> received_date_2 datetime,
> modified_date datetime,
> bill_amount float,
> provider_claim_payment_id int,
> provider_payment_created_date datetime)
> insert into #step1
> (claim_id,
> bill_id,
> received_date_2,
> modified_date,
> bill_amount,
> provider_claim_payment_id,
> provider_payment_created_date)
> exec rpt_misc_getlostbillpayments_sp
>
> select top 1 claim_id, @.emails as email from #step1
> drop table #step1
>
> Any suggestions?

How to use SqlFacet in VB .Net (for parameters)

I have written a CLR stored proc in VB .NET and I have to define the length of the string to be passed as parameters. I cannot get it to compile in VB .Net (VS 2005). pl help.

Code Snippet

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub ProcessAdjustment(ByVal ServerName As String, ByVal Cob As SqlString <SqlFacetAttribute(MaxSize=30)> , Optional ByVal DateFrom As String = Nothing, Optional ByVal DateTo As String = Nothing)

Thanks in Advance,

Arun

I assume you want to apply the SqlFacetAttribute to your Cop param?

If so, shouldn't the attribute be before the param (that's the way it is in C# anyway). Also, in VB I believe you're using the := syntax for attribute properties:

<SqlFacet(MaxSize:=30)> ByVal Cob as SqlString

You have to excuse me but my VB.NET syntax is a bit rusty.

Niels
|||

Thanks Niels.

Works perfectly.

Monday, March 19, 2012

How to use Parameters without letting it to Prompt to enter

Hi All,
We are using 10 parameters in SRS report. When the report is
executing, it prompts us to enter all 10 parameters.
But we don't want to enter all parameters, we should be able to enter
blank parameters without mandatory parameters.
SRS prompts to enter all parameters.
Kindly help.
Regards,
Manoj
Mobile: +91-9989010205Allow blank option should be enabled. Still it will wait for the input for
any other field you wanted to enter and have to press "View Reports"
Amarnath
"plmanoj@.gmail.com" wrote:
> Hi All,
> We are using 10 parameters in SRS report. When the report is
> executing, it prompts us to enter all 10 parameters.
> But we don't want to enter all parameters, we should be able to enter
> blank parameters without mandatory parameters.
> SRS prompts to enter all parameters.
> Kindly help.
> Regards,
> Manoj
> Mobile: +91-9989010205
>|||Hi Amar,
Thanks for your reply!
Even blank option does not solve the purpose. It still pops the message saying
"Please enter a value for the parameter. The parameter cannot be blank".
Our objective is, It should not be mandatory to specify the parameter value.
Please let us know if you have any solution.
Regards,
Manoj
"Amarnath" wrote:
> Allow blank option should be enabled. Still it will wait for the input for
> any other field you wanted to enter and have to press "View Reports"
> Amarnath
> "plmanoj@.gmail.com" wrote:
> > Hi All,
> >
> > We are using 10 parameters in SRS report. When the report is
> > executing, it prompts us to enter all 10 parameters.
> >
> > But we don't want to enter all parameters, we should be able to enter
> > blank parameters without mandatory parameters.
> >
> > SRS prompts to enter all parameters.
> >
> > Kindly help.
> >
> > Regards,
> > Manoj
> > Mobile: +91-9989010205
> >
> >|||Hope you have checked for Null as well, so if both are checked then it goes
through. Just check each parameter, to see any where you had not checked and
more over there is an option called default, where you can specify so that it
goes through
Amarnath
"AxMan" wrote:
> Hi Amar,
> Thanks for your reply!
> Even blank option does not solve the purpose. It still pops the message saying
> "Please enter a value for the parameter. The parameter cannot be blank".
> Our objective is, It should not be mandatory to specify the parameter value.
> Please let us know if you have any solution.
> Regards,
> Manoj
> "Amarnath" wrote:
> > Allow blank option should be enabled. Still it will wait for the input for
> > any other field you wanted to enter and have to press "View Reports"
> >
> > Amarnath
> >
> > "plmanoj@.gmail.com" wrote:
> >
> > > Hi All,
> > >
> > > We are using 10 parameters in SRS report. When the report is
> > > executing, it prompts us to enter all 10 parameters.
> > >
> > > But we don't want to enter all parameters, we should be able to enter
> > > blank parameters without mandatory parameters.
> > >
> > > SRS prompts to enter all parameters.
> > >
> > > Kindly help.
> > >
> > > Regards,
> > > Manoj
> > > Mobile: +91-9989010205
> > >
> > >|||Forgot to mention, the other option is "default value", if you give the
default value then it goes through directly, without popping a msg.
Amarnath
"AxMan" wrote:
> Hi Amar,
> Thanks for your reply!
> Even blank option does not solve the purpose. It still pops the message saying
> "Please enter a value for the parameter. The parameter cannot be blank".
> Our objective is, It should not be mandatory to specify the parameter value.
> Please let us know if you have any solution.
> Regards,
> Manoj
> "Amarnath" wrote:
> > Allow blank option should be enabled. Still it will wait for the input for
> > any other field you wanted to enter and have to press "View Reports"
> >
> > Amarnath
> >
> > "plmanoj@.gmail.com" wrote:
> >
> > > Hi All,
> > >
> > > We are using 10 parameters in SRS report. When the report is
> > > executing, it prompts us to enter all 10 parameters.
> > >
> > > But we don't want to enter all parameters, we should be able to enter
> > > blank parameters without mandatory parameters.
> > >
> > > SRS prompts to enter all parameters.
> > >
> > > Kindly help.
> > >
> > > Regards,
> > > Manoj
> > > Mobile: +91-9989010205
> > >
> > >

How to use parameters in the MDX?

Hi all,

I'm working on a project that builds a report in RS 2005 agains the data cube created in AS 2005.

I created a report in RS 2005 against database table, and the parameters were easy to be added in the T-SQl query.

There are about 15 parameters needs to add in the query, and I can use "IN, =, Betweent.. and ," in the "where" clause, e.g.,

select *
FROM Mytable1
WHERE EmployeeID IN (@.EmployeeID)
and EmployeeName] IN (@.EmployeeName)
...
and Salary between @.SalaryFrom and @.SalaryTo

... and Date between @.DateStart and @.DateEnd

most of the restriction fields are column fileds in the report.

Right now, I need to switch the datasource to the data cube, and then I have to use MDX to do the query. But I really cannot get enough information about MDX in the MSDN library --> SQL Server Language Reference -->MDX Reference to write a MDX query, especially with parameters.

Could somebody tell me how to do it?

please give me a complete example for the following T-SQL query:

select t1.EmployeeName, t2.Salary, t3.Date, t4.SaleAmount


FROM Mytable1 t1, mytable2 t2, mytable3 t3, mytable4 t4


WHERE t1.EmployeeName IN (@.EmployeeName)
...
and t2.Salary between @.SalaryFrom and @.SalaryTo

... and t3.Date between @.DateStart and @.DateEnd

and t4.SaleAmount betweent @.SaleAmountFrom and @.SaleAmountTo

in the cube, those data are stored in DimEmployee (field EmployeeName), DimSalary(filed Salary), DimDate, FactSales, and the SaleAmount will be the messure field.

Or if sombody can provide me the link to MDX tutorial other than what I can get from the "MDX reference" I mented above, I'll be very appreciated !!

Thanks!!

Jone

Once you set up your data source to use the Analysis Services provider, you will be thrown in the MDX Query Designer. From there, you should be able to create your reports by just dragging and droppping attributes and measures. You may find this link useful.|||

Thank you, Teo, that's really a help.

But I still have some problems when I set up the parameters.

1. After I set up couple of parameters, say 5, p1, p2, p3, p4,p5; then if I change the order of the parameters, like p1,p2,p5,p3,p4, in the parameter pane (I still change the order in the report-->report parameters), the query fails since the "forward dependency " isn't allowed.

My question here is : how to change the parameter's order?

2. I have some parameters in style " from .. to", such as "date". when defining the parameter, I choose "Range(Inclusive)" as the "Operator", and choose "all : all" . There are tones of date values in the table, so I think it's not a good idea to let the user choose one date from the drop-down list.

My question is: how to let the user input a date by themselves? how to show a calendar when user click on this parameter field?

Thank you very much

Jone

|||

1. Are these parameters dependent on each other? Please provide repro steps or sample report definition targeting the Adventure Works cube.

2. You can use a DateTime parameter as long as your main query can resolve it (StrToMember() function) to a valid member in in your Date dimension. The 4_ProductSalesQueryMode.rdl report in this download shows how. If I recall I had to set the Value property of the dimension key of the Date dimension to a DateTime column in the underlying database table to get it to work.

|||

Thanks Teo.

In my report,

1. some of those parameters are dependent on each other, while some are not(but all connected to Facttable). i tried to "move up" or "move down" the undependent parameters in the filter pane, I got errors on "forward dependency".

Upon each setting up of a parameter(say p2, and p1 has been set up before p2), a 'select (strtoset() ...) .. from)' was created automatically in such style ( in the report dataset and the parameter dataset):

FROM ( SELECT ( STRTOSET(@.DimP2, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimP1, CONSTRAINED) ) ON COLUMNS FROM [My Report]))

after changing the order, I got errors.

2. I can get the calendar against the dataset against the database table, but failed against the datacube. I modified the parameter @.Dimdate from string to DateTime, then I got "constrain voilated".

If you have any idea on what I missed, please save me.

Thank you very much!

Jone

|||

Thanks Teo.

In my report,

1. some of those parameters are dependent on each other, while some are not(but all connected to Facttable). i tried to "move up" or "move down" the undependent parameters in the filter pane, I got errors on "forward dependency".

Upon each setting up of a parameter(say p2, and p1 has been set up before p2), a 'select (strtoset() ...) .. from)' was created automatically in such style ( in the report dataset and the parameter dataset):

FROM ( SELECT ( STRTOSET(@.DimP2, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimP1, CONSTRAINED) ) ON COLUMNS FROM [My Report]))

after changing the order, I got errors.

2. I can get the calendar against the dataset against the database table, but failed against the datacube. I modified the parameter @.Dimdate from string to DateTime, then I got "constrain voilated".

If you have any idea on what I missed, please save me.

Thank you very much!

Jone

how to use parameters in method (filter by parameter)

Hello!

I've begin to do a tutorial of this site: Working with Data in ASP.NET 2.0 c#, but in the 3. step I can't make something:

In this step I can't add parameterized methods to the TableAdapter. The problem come up when I want to add Sql query.

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @.CategoryID

The error message:

Error in WHERE clause near'@.'.
Unable to parse query text.

So, how can i use parameter in this method?

tnx for the help

Simpson

Are you sure you are passing in the parameter values to the TableAdapter when calling in your code?

Thanks

|||Hi

I have the same problem have you found a solution.

Regards
Demetrios|||Hi

I have the same problem have you found a solution.

Regards
Demetrios|||Hi

I have the same problem have you found a solution.

Regards
Demetrios|||

Hi MaoBranca,

This might because the @.CategoryID was not declared properly.

If this is a SQL Server database, please make sure that you have added the parameter to the SqlCommand parameter collection. If it has been added, please also confirm that the data type, size and value are correct.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||i have found the solution

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @.CategoryID

this works for sqlserver if you are using an access database then it is

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = ?

if you are using mysql then it is

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = #CategoryID

but i an not sure with mysql

how to use parameters in mdx query

i have a problem with mdx query. here is a simple example of what i=B4m
trying to do
select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name])} ON ROWS
from [TestCube]
this query works fine... my problem is that the [Customer] dimension
has 3 members and i want to select just one of them, i tried to use
filter function, but it wasn=B4t working, i probably used it the wrong
way. can anyone help me with this? i=B4m working with sql server 2005...You should write it like this:
select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name].[BillyTheKid])} ON ROWS
from [TestCube]
The query with parameters should look like this:
="select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name].[" & Parameters!ClientName.Value & ")} ON ROWS
from [TestCube]"
Everything needs to be on a single line (it will do a line break, but if you
copy it to Notepad, it should all be on one line.)
Even if you're working with RS 2005, you might pick up some tips from an
article called
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
by William Pearson
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_1
He's using RS 2000, but the way to do it is still similar. The good stuff
starts on page 8
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_8
The previous pages are on how to copy and edit one of the sample reports.
Pearson has written lots of good articles on RS and Analysis Services. You
find the full list at http://www.databasejournal.com/article.php/1459531
Kaisa M. Lindahl Lervik
<cellardoor0716@.gmail.com> wrote in message
news:1155818962.580093.222920@.m79g2000cwm.googlegroups.com...
i have a problem with mdx query. here is a simple example of what i´m
trying to do
select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name])} ON ROWS
from [TestCube]
this query works fine... my problem is that the [Customer] dimension
has 3 members and i want to select just one of them, i tried to use
filter function, but it wasn´t working, i probably used it the wrong
way. can anyone help me with this? i´m working with sql server 2005...

How to use parameters

iam developing the crystal report there was small problem iam facing
please help me
How to use parameter of main report into the subreport
example
@.startdatetime is a parameter in main report
ia want to use the same parmeter in subreportcreate startdatetime parameter in subreport as well, then like it with main reports parameter.

i think i already suggested this to you.|||Thanks for reply

But we are getting six parameter while running the report
six parameters are
3 from sub reports(@.agencyid,@.startdatetime,@.Enddatetime)
3 from main reports(@.agencyid,@.startdatetime,@.Enddatetime)

while running the report it asking for
@.agencyid for subreport name
@.startdatetime for subreport name
@.Enddatetime for subreport name
@.agencyid
@.startdatetime
@.Enddatetime

but i requried only three parameter to be displayed to enter the values
please suggest me to solve the problem|||yes after linking it will ask only for main report parameters.|||You can make use of shared variables. Read it in help file

how to use parameter in rs?

the database hass a store proc with 3 parameters,
eg.create proc stp_test @.Start varchar(1024),@.End varchar(1024),@.Type
varchar(1024) as......
in the reporting project, it has 3 parameters StartA,EndA,TypeA exactly
like those above.
how to call the store proc in the reporting project?
exec stp_test @.Start=@.StartA,@.End=@.EndA,@.Type=@.TypeA ?
but it does not work,a "must declare @.StartA first" message appears
when previewing.hi,you shuld write in the query string just "stp_test ",then click on Run or
Refresh and the VS will update automaticly the parameters.
"Spirit" wrote:
> the database hass a store proc with 3 parameters,
> eg.create proc stp_test @.Start varchar(1024),@.End varchar(1024),@.Type
> varchar(1024) as......
> in the reporting project, it has 3 parameters StartA,EndA,TypeA exactly
> like those above.
> how to call the store proc in the reporting project?
> exec stp_test @.Start=@.StartA,@.End=@.EndA,@.Type=@.TypeA ?
> but it does not work,a "must declare @.StartA first" message appears
> when previewing.
>|||The above syntax works when the command type is set to
'StoredProcedure', if the command type is set to 'Text' the syntax
would be:
Exec stp_test @.Start, @.End, @.Type
This is also the syntax that must be used when you are using the Report
Wizard and it is asking for the Query String.|||thanks ,it works well.

Friday, March 9, 2012

how to use GetReportParameters for a parameter with dependencies?

try to develop a customized report with personal UI.

can use GetReportParameters to get all parameters. but can not find a way to get the paramter with dependencies.

there is a perference from http://www.odetocode.com/Articles/123.aspx. I made my own code, but it never works.

any helps? thanks.

--

Dim vs() As ParameterValue = Nothing

vs(0) = New ParameterValue

vs(0).Name = pName ' dependency' name

vs(0).Value = pValue ' dependency' value

Dim ps() As ReportParameter = rs.GetReportParameters(report, Nothing, True, vs, Nothing)

When calling GetParameters you want to pass in the parameter values you have. If a dependency is satisfied by that set then the return value should have the values for the dependent parameter populated.

how to use GetReportParameters for a parameter with dependencies?

try to develop a customized report with personal UI.

can use GetReportParameters to get all parameters. but can not find a way to get the paramter with dependencies.

there is a perference from http://www.odetocode.com/Articles/123.aspx. I made my own code, but it never works.

any helps? thanks.

--

Dim vs() As ParameterValue = Nothing

vs(0) = New ParameterValue

vs(0).Name = pName ' dependency' name

vs(0).Value = pValue ' dependency' value

Dim ps() As ReportParameter = rs.GetReportParameters(report, Nothing, True, vs, Nothing)

When calling GetParameters you want to pass in the parameter values you have. If a dependency is satisfied by that set then the return value should have the values for the dependent parameter populated.

Wednesday, March 7, 2012

How to Use Delete Parameters

Hey gang I am trying to use the delete feature of a sqldatasource and having issues with the delete feature. My code is below the issue is if I just call the delete and don't have a paramter in my delete statement then it deletes the records in my table. However when I add a parameter to my delete query (@.maID) and try to set it then it doesn't delete the record. What am I doing wrong? The e.CommandArgument is passing the correct record ID (which is an Integer)....can someone please help?

PS I also tried without the @. symbol before my parameter and no luck either.

DELETE FROM tempMbrAccounts WHERE (maID = @.maID) is my delete query

Public Sub OnDeleteButtonClick(ByVal senderAs Object,ByVal eAs GridViewCommandEventArgs)Handles gvEnterAcct.RowCommandDim RecIDAs Integer If e.CommandName ="Delete"Then RecID = e.CommandArgument dsInsert.DeleteParameters.Add("@.maID", RecID) dsInsert.Delete() tempAcctTable()End If End Sub
If maID is your table's primary key, SetDataKeyNames="maID" for your gridview. You should be able to use the default DelectCommand of SqlDataSource without adding extra code.|||

I should add that I an not using the standard delete button that is implemented in a GridView. I am using an imagebutton.

I do have the datakey set to maID but that isn't working either.

|||

By using an ImageButton in a TemplateField, you can still use the standard way to do delete. Here is a sample which is working . ID is the Primary Key of the test table.

<div>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataKeyNames="ID"DataSourceID="SqlDataSource1"><Columns><asp:CommandFieldShowEditButton="True"/><asp:BoundFieldDataField="ID"HeaderText="ID" ReadOnly="True"SortExpression="ID"/><asp:BoundFieldDataField="myCol"HeaderText="myCol"SortExpression="myCol"/><asp:TemplateField><ItemTemplate><asp:ImageButtonid="DeleteImgBtn"CommandName="Delete"ImageUrl="="~/images/delete.gif"runat="server"/></ItemTemplate>

</

asp:TemplateField>

</Columns></asp:GridView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"DeleteCommand="DELETE FROM [test] WHERE [ID] = @.ID"SelectCommand="SELECT [ID], [myCol] FROM [test]"><DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters></asp:SqlDataSource></div>

How to use default values of parameters

My report has 6 parameters. 3 Of them are defined with default values. How can I via the webservice method use these default values? I am building the parameters dynamically. You can see that the first 3 parameters are filled in depending on the value selected in a dropdown box. With the last 3 I want to use the default values. Is this possible and how do I do this. When with the first 3 parameters, no value is selected in the dropdown box, the default values shouls also be used but this is not yet programmed. Can anyone help me with this?
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(reportPath, historyID, forRendering, reportHistoryParameters, credentials);
ParameterValue[] rptParameters = new ParameterValue[parameters.GetLength(0)];
int intParam = 0;
foreach (ReportParameter rp in parameters)
{
rptParameters[intParam] = new ParameterValue();
rptParameters[intParam].Name = rp.Name;
switch(rp.Name.ToUpper())
{
case "OPCO":
rptParameters[intParam].Value = this.Opco;
break;
case "CONFIGTYPEGROUP":
rptParameters[intParam].Value = this.ConfigtypeGroup;
break;
case "CONFIGTYPE":
rptParameters[intParam].Value = this.ConfigType;
break;
case "ERRORCODE":
rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
case "VISITDATETIME_FROM":
rptParameters[intParam].Value = rp.DefaultValues.ToString())
break;
case "VISITDATETIME_TO":
rptParameters[intParam].Value = rp.DefaultValues.ToString();
break;
}
intParam++;
}
firstPage = rs.Render( reportPath,
format,
null,
deviceInfo,
rptParameters,
null,
null,
out encoding,
out mimeType,
out reportHistoryParameters,
out warnings,
out streamIDs);I already found the solution.
I have to write:
rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
in stead of:
rptParameters[intParam].Value = rp.DefaultValues.ToString();
This will give back the default value defined in the report
"Gert" wrote:
> My report has 6 parameters. 3 Of them are defined with default values. How can I via the webservice method use these default values? I am building the parameters dynamically. You can see that the first 3 parameters are filled in depending on the value selected in a dropdown box. With the last 3 I want to use the default values. Is this possible and how do I do this. When with the first 3 parameters, no value is selected in the dropdown box, the default values shouls also be used but this is not yet programmed. Can anyone help me with this?
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(reportPath, historyID, forRendering, reportHistoryParameters, credentials);
> ParameterValue[] rptParameters = new ParameterValue[parameters.GetLength(0)];
> int intParam = 0;
> foreach (ReportParameter rp in parameters)
> {
> rptParameters[intParam] = new ParameterValue();
> rptParameters[intParam].Name = rp.Name;
> switch(rp.Name.ToUpper())
> {
> case "OPCO":
> rptParameters[intParam].Value = this.Opco;
> break;
> case "CONFIGTYPEGROUP":
> rptParameters[intParam].Value = this.ConfigtypeGroup;
> break;
> case "CONFIGTYPE":
> rptParameters[intParam].Value = this.ConfigType;
> break;
> case "ERRORCODE":
> rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> case "VISITDATETIME_FROM":
> rptParameters[intParam].Value = rp.DefaultValues.ToString())
> break;
> case "VISITDATETIME_TO":
> rptParameters[intParam].Value = rp.DefaultValues.ToString();
> break;
> }
> intParam++;
> }
> firstPage = rs.Render( reportPath,
> format,
> null,
> deviceInfo,
> rptParameters,
> null,
> null,
> out encoding,
> out mimeType,
> out reportHistoryParameters,
> out warnings,
> out streamIDs);|||If you want the server to use the current default values, then don't pass in
anything for this values. Only pass in the three parameters that don't have
defaults and the server will then use the current default value during
rendering.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert" <Gert@.discussions.microsoft.com> wrote in message
news:66030BB4-9273-4AF1-B608-9B01F77ED7FD@.microsoft.com...
> I already found the solution.
> I have to write:
> rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> in stead of:
> rptParameters[intParam].Value = rp.DefaultValues.ToString();
> This will give back the default value defined in the report
>
> "Gert" wrote:
> > My report has 6 parameters. 3 Of them are defined with default values.
How can I via the webservice method use these default values? I am building
the parameters dynamically. You can see that the first 3 parameters are
filled in depending on the value selected in a dropdown box. With the last 3
I want to use the default values. Is this possible and how do I do this.
When with the first 3 parameters, no value is selected in the dropdown box,
the default values shouls also be used but this is not yet programmed. Can
anyone help me with this?
> >
> > ReportParameter[] parameters = null;
> >
> > parameters = rs.GetReportParameters(reportPath, historyID, forRendering,
reportHistoryParameters, credentials);
> >
> > ParameterValue[] rptParameters = new
ParameterValue[parameters.GetLength(0)];
> >
> > int intParam = 0;
> > foreach (ReportParameter rp in parameters)
> > {
> > rptParameters[intParam] = new ParameterValue();
> > rptParameters[intParam].Name = rp.Name;
> > switch(rp.Name.ToUpper())
> > {
> > case "OPCO":
> > rptParameters[intParam].Value = this.Opco;
> > break;
> > case "CONFIGTYPEGROUP":
> > rptParameters[intParam].Value = this.ConfigtypeGroup;
> > break;
> > case "CONFIGTYPE":
> > rptParameters[intParam].Value = this.ConfigType;
> > break;
> > case "ERRORCODE":
> > rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> > case "VISITDATETIME_FROM":
> > rptParameters[intParam].Value = rp.DefaultValues.ToString())
> > break;
> > case "VISITDATETIME_TO":
> > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > break;
> > }
> >
> > intParam++;
> > }
> >
> > firstPage = rs.Render( reportPath,
> > format,
> > null,
> > deviceInfo,
> > rptParameters,
> > null,
> > null,
> > out encoding,
> > out mimeType,
> > out reportHistoryParameters,
> > out warnings,
> > out streamIDs);|||I can't use your option because I have to loop through all the report parameterss because I have different reports with different paramaters and I don't know in the beginning of the program for which parameter the user will select a value or not. If no value selected, the default value of the report is retrieved.
I use the following code and this works perfect. I use a Hash table(see below) to store the paramaters and its values if they are selected.
********************************************************
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(reportPath, historyID, forRendering, reportHistoryParameters, credentials);
ParameterValue[] rptParameters = new ParameterValue[parameters.GetLength(0)];
int intParam = 0;
foreach (ReportParameter rp in parameters)
{
rptParameters[intParam] = new ParameterValue();
rptParameters[intParam].Name = rp.Name.ToUpper();
if (hashParameters.ContainsKey(rp.Name.ToUpper()))
{
if (hashParameters[rp.Name].ToString() != "")
rptParameters[intParam].Value = hashParameters[rp.Name].ToString();
else
rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
}
intParam++;
}
********************************************************
The Hash table is filled as follows:
********************************************************
hashParameters = new Hashtable();
hashParameters.Add("OPCO", this.Opco);
hashParameters.Add("CONFIGTYPEGROUP", this.ConfigtypeGroup);
hashParameters.Add("CONFIGTYPE", this.ConfigType);
hashParameters.Add("ERRORCODE", "");
hashParameters.Add("VISITDATETIME_FROM", "");
hashParameters.Add("VISITDATETIME_TO", "");
hashParameters.Add("COUNTERNUMBER", "");
hashParameters.Add("ERRORDATETIME_FROM", "");
hashParameters.Add("ERRORDATETIME_TO", "");
hashParameters.Add("PARAMETERNUMBER", "");
hashParameters.Add("MODIFICATIONNR", "");
********************************************************
"Daniel Reib [MSFT]" wrote:
> If you want the server to use the current default values, then don't pass in
> anything for this values. Only pass in the three parameters that don't have
> defaults and the server will then use the current default value during
> rendering.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Gert" <Gert@.discussions.microsoft.com> wrote in message
> news:66030BB4-9273-4AF1-B608-9B01F77ED7FD@.microsoft.com...
> > I already found the solution.
> > I have to write:
> > rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> > in stead of:
> > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> >
> > This will give back the default value defined in the report
> >
> >
> > "Gert" wrote:
> >
> > > My report has 6 parameters. 3 Of them are defined with default values.
> How can I via the webservice method use these default values? I am building
> the parameters dynamically. You can see that the first 3 parameters are
> filled in depending on the value selected in a dropdown box. With the last 3
> I want to use the default values. Is this possible and how do I do this.
> When with the first 3 parameters, no value is selected in the dropdown box,
> the default values shouls also be used but this is not yet programmed. Can
> anyone help me with this?
> > >
> > > ReportParameter[] parameters = null;
> > >
> > > parameters = rs.GetReportParameters(reportPath, historyID, forRendering,
> reportHistoryParameters, credentials);
> > >
> > > ParameterValue[] rptParameters = new
> ParameterValue[parameters.GetLength(0)];
> > >
> > > int intParam = 0;
> > > foreach (ReportParameter rp in parameters)
> > > {
> > > rptParameters[intParam] = new ParameterValue();
> > > rptParameters[intParam].Name = rp.Name;
> > > switch(rp.Name.ToUpper())
> > > {
> > > case "OPCO":
> > > rptParameters[intParam].Value = this.Opco;
> > > break;
> > > case "CONFIGTYPEGROUP":
> > > rptParameters[intParam].Value = this.ConfigtypeGroup;
> > > break;
> > > case "CONFIGTYPE":
> > > rptParameters[intParam].Value = this.ConfigType;
> > > break;
> > > case "ERRORCODE":
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> > > case "VISITDATETIME_FROM":
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString())
> > > break;
> > > case "VISITDATETIME_TO":
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > > break;
> > > }
> > >
> > > intParam++;
> > > }
> > >
> > > firstPage = rs.Render( reportPath,
> > > format,
> > > null,
> > > deviceInfo,
> > > rptParameters,
> > > null,
> > > null,
> > > out encoding,
> > > out mimeType,
> > > out reportHistoryParameters,
> > > out warnings,
> > > out streamIDs);
>
>|||Sure you could, just dynamically allocate an array and add to that array as
you find parameters the user has set. Then use the dynamic array to pass to
render. The only problem with your code would be if the default values
changed between your call to GetReportParameters and Render. In your code
you will use the old value. If you are displaying this value to the user,
then this is probably what you want. If you are displaying something like
'Use default' then the user may not get the actual default at render time.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert" <Gert@.discussions.microsoft.com> wrote in message
news:C8CD66F1-CFB0-4A33-A90E-00919BB04B1B@.microsoft.com...
> I can't use your option because I have to loop through all the report
parameterss because I have different reports with different paramaters and I
don't know in the beginning of the program for which parameter the user will
select a value or not. If no value selected, the default value of the report
is retrieved.
> I use the following code and this works perfect. I use a Hash table(see
below) to store the paramaters and its values if they are selected.
> ********************************************************
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(reportPath, historyID, forRendering,
reportHistoryParameters, credentials);
> ParameterValue[] rptParameters = new
ParameterValue[parameters.GetLength(0)];
> int intParam = 0;
> foreach (ReportParameter rp in parameters)
> {
> rptParameters[intParam] = new ParameterValue();
> rptParameters[intParam].Name = rp.Name.ToUpper();
> if (hashParameters.ContainsKey(rp.Name.ToUpper()))
> {
> if (hashParameters[rp.Name].ToString() != "")
> rptParameters[intParam].Value =hashParameters[rp.Name].ToString();
> else
> rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> }
> intParam++;
> }
> ********************************************************
> The Hash table is filled as follows:
> ********************************************************
> hashParameters = new Hashtable();
> hashParameters.Add("OPCO", this.Opco);
> hashParameters.Add("CONFIGTYPEGROUP", this.ConfigtypeGroup);
> hashParameters.Add("CONFIGTYPE", this.ConfigType);
> hashParameters.Add("ERRORCODE", "");
> hashParameters.Add("VISITDATETIME_FROM", "");
> hashParameters.Add("VISITDATETIME_TO", "");
> hashParameters.Add("COUNTERNUMBER", "");
> hashParameters.Add("ERRORDATETIME_FROM", "");
> hashParameters.Add("ERRORDATETIME_TO", "");
> hashParameters.Add("PARAMETERNUMBER", "");
> hashParameters.Add("MODIFICATIONNR", "");
> ********************************************************
>
> "Daniel Reib [MSFT]" wrote:
> > If you want the server to use the current default values, then don't
pass in
> > anything for this values. Only pass in the three parameters that don't
have
> > defaults and the server will then use the current default value during
> > rendering.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Gert" <Gert@.discussions.microsoft.com> wrote in message
> > news:66030BB4-9273-4AF1-B608-9B01F77ED7FD@.microsoft.com...
> > > I already found the solution.
> > > I have to write:
> > > rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> > > in stead of:
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > >
> > > This will give back the default value defined in the report
> > >
> > >
> > > "Gert" wrote:
> > >
> > > > My report has 6 parameters. 3 Of them are defined with default
values.
> > How can I via the webservice method use these default values? I am
building
> > the parameters dynamically. You can see that the first 3 parameters are
> > filled in depending on the value selected in a dropdown box. With the
last 3
> > I want to use the default values. Is this possible and how do I do this.
> > When with the first 3 parameters, no value is selected in the dropdown
box,
> > the default values shouls also be used but this is not yet programmed.
Can
> > anyone help me with this?
> > > >
> > > > ReportParameter[] parameters = null;
> > > >
> > > > parameters = rs.GetReportParameters(reportPath, historyID,
forRendering,
> > reportHistoryParameters, credentials);
> > > >
> > > > ParameterValue[] rptParameters = new
> > ParameterValue[parameters.GetLength(0)];
> > > >
> > > > int intParam = 0;
> > > > foreach (ReportParameter rp in parameters)
> > > > {
> > > > rptParameters[intParam] = new ParameterValue();
> > > > rptParameters[intParam].Name = rp.Name;
> > > > switch(rp.Name.ToUpper())
> > > > {
> > > > case "OPCO":
> > > > rptParameters[intParam].Value = this.Opco;
> > > > break;
> > > > case "CONFIGTYPEGROUP":
> > > > rptParameters[intParam].Value = this.ConfigtypeGroup;
> > > > break;
> > > > case "CONFIGTYPE":
> > > > rptParameters[intParam].Value = this.ConfigType;
> > > > break;
> > > > case "ERRORCODE":
> > > > rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> > > > case "VISITDATETIME_FROM":
> > > > rptParameters[intParam].Value = rp.DefaultValues.ToString())
> > > > break;
> > > > case "VISITDATETIME_TO":
> > > > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > > > break;
> > > > }
> > > >
> > > > intParam++;
> > > > }
> > > >
> > > > firstPage = rs.Render( reportPath,
> > > > format,
> > > > null,
> > > > deviceInfo,
> > > > rptParameters,
> > > > null,
> > > > null,
> > > > out encoding,
> > > > out mimeType,
> > > > out reportHistoryParameters,
> > > > out warnings,
> > > > out streamIDs);
> >
> >
> >

How to use Convert date statement in cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).V

Hi

I am using SQL 2005, VB 2005

I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works fine

string insertSQL;

insertSQL ="INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@.ProjectID, @.TypeofEntryID, @.PriorityID ,@.Title, @.Area)";

cmdInsert SqlCommand;

cmdInsert=new SqlCommand(insertSQL,conn);

cmdInsert.Parameters.Add("@.ProjectID",SqlDbType.Varchar).Value=ProjectID.Text;

My query is how to detail with dates my previous code was

insertSQL +="convert(datetime,'" +DateTime.Now.ToString("dd/MM/yy") +"',3), '";

I tried the code below but the record doesn't save?

string date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

Could someone point to me in the right direction?

Thanks in advance

yazzy:


string insertSQLstring date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

Why don't you just directly pass DateTime.Now to the @.Date parameter (SqlDbType.DateTime)?

|||

Sorry,

I am having a hard time trying to figure out what it is that you are trying to do. Convert(datetime,@.Date,3) does NOTHING. @.Date is already a datetime, and the third parameter isn't valid for datetime to datetime ?conversions?.

I think you are trying to store a date into a datetime column? Or is the date field a varchar?

If it's a datetime, then change:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, @.Date,@.IDStaff)";

andchange:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

to:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = Now.Date

*OR* change your insert to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, DATEADD(d,0,DATEDIFF(d,0,getdate())),@.IDStaff)";

and remove the Date parameter completely (Which is the method I prefer, since I use the SQL Server's clock exclusively).

|||

Hi Motley

I am trying to store a date into a datetime column. The thrid parameter is to store the date in 'dd/MM/yy' formate (i.e UK date)

Because I have to store date in the above formate, it is causing when I try to insert/update a record.

Sorry I had not included this information.

Speak soon

How to use comma separated lists with parameters in sqldatasource?

I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report. The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables. The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results. They are then displayed using the gridview control. It actually works!

Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this? I'm not very experienced with asp nor sql server and have been struggling with this new challenge. I really could use pointers as to whether there's an easy way to handle this. In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically. I get the impression that with all this ASP.NET fanciness, there has to be a better way?

If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express.

Thanks in advance for any replies, they would be GREATLY appreciated :)

Hi,

you either generate the SQL query programmatically based on the CSV, e.g split the CSV string with the delimiter, loop the array returned by String.Split and then buold the SQl string dynamically. Other way is to create a stored procedure to the Db and use functions to split CSVs for example:http://www.sqlteam.com/item.asp?ItemID=11499