Passing parameter from a webpage to a report

I am developing a website which need to allow user to pass through several webpages for criteria selection and generate a report base on the selected criteria finally.

Anyone know how to pass the selected value from a webpage to the SQL statement or stored procedure which used to generate the report? Or there are other methods to do so?look at the rendering methods of RS - there's one by URL where you can pass the parameters via URL and request the report.|||I believe you simply build a querystring and append the parameters and values to the end of the querystring.|||Thanks for your kindly reply.

I have another related question see whether anyone can help. Base on my current knowledge, I know that we can pass one value for each parameter. E.g. countryID=20. If I want to pass multiple values for this parameter, e.g. countryID=20, 21, 25, ... How can I do so? Also, how should I set it in report designer for this purpose?|||Can you explain little more in detail (perhaps with some sampoe) ? Is your data in the format 20,21,25 for each record ?|||You could pass in a string like you describe. You would need to have logic in your report or stored procedure to handle such a request, though...|||In my report, there is a chart to compare sales performance between several products which are selected by user. The number of selected products is not fixed. I need to pass multiple productID into the report.

Also, I face a problem that ...seem I can't pass parameters into SQL functions to build dataset. Is it a rule? The error msg is "Syntax error or access violation.". My statement is as follows:

select * from getTable(@.productID)

Notes: getTable is a user-defined function.|||so what u can do

Select * from table where productid in (@.productid)|||In the following statement, getTable is a function with many calculation.

select * from getTable(@.productID)

If I use the one below, I can't get what I want. Also, seem can't be a string e.g. "12, 13, 14"
Select * from table where productid in (@.productid)|||so just pass the value in paenthesis

like '12,13,14'. it will definitely work.

If it is a function. then it should return the value same as u mentioned like "12,13,14"
if u can make it " '12,13,14' " . Then you can pass on values.

Passing a parameter onto a Oracle database

I am connected to an oracle database and wish to allow the users enter a
ProdID, normally in SQL the criteria in the dataset would be =@.ProdID and
this would be passed onto the report. In oracle it sees this as a text and
encloses parameter in quotes '=@.ProdID'
Is it possible to use this expression in reporting services when attached to
an oracle database or is this something that cannot be fixedThe managed Oracle data provider uses a ':' to mark named parameters
(instead of '@.'); the OleDB provider for Oracle only allows unnamed
parameters (using '?'). The following KB article explains more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
Examples:
Managed Oracle provider (named parameters):
select * from table where ename = :parameter
OleDB for Oracle (unnamed parameters):
select * from table where ename = ?
Note: the Visual Data Tools (VDT) query designer (4 panes) actually uses OLE
DB in the preview pane. The text-based generic query designer (GQD; 2 panes)
uses the .NET provider for Oracle. Generally, you will achieve better
results when using GQD with Oracle.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:717C796D-3694-4B5C-92C2-8E701CF8C428@.microsoft.com...
>I am connected to an oracle database and wish to allow the users enter a
> ProdID, normally in SQL the criteria in the dataset would be =@.ProdID and
> this would be passed onto the report. In oracle it sees this as a text
> and
> encloses parameter in quotes '=@.ProdID'
> Is it possible to use this expression in reporting services when attached
> to
> an oracle database or is this something that cannot be fixed|||Thanks Robert, now I know what I must do.
"Robert Bruckner [MSFT]" wrote:
> The managed Oracle data provider uses a ':' to mark named parameters
> (instead of '@.'); the OleDB provider for Oracle only allows unnamed
> parameters (using '?'). The following KB article explains more details:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> Examples:
> Managed Oracle provider (named parameters):
> select * from table where ename = :parameter
> OleDB for Oracle (unnamed parameters):
> select * from table where ename = ?
> Note: the Visual Data Tools (VDT) query designer (4 panes) actually uses OLE
> DB in the preview pane. The text-based generic query designer (GQD; 2 panes)
> uses the .NET provider for Oracle. Generally, you will achieve better
> results when using GQD with Oracle.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:717C796D-3694-4B5C-92C2-8E701CF8C428@.microsoft.com...
> >I am connected to an oracle database and wish to allow the users enter a
> > ProdID, normally in SQL the criteria in the dataset would be =@.ProdID and
> > this would be passed onto the report. In oracle it sees this as a text
> > and
> > encloses parameter in quotes '=@.ProdID'
> >
> > Is it possible to use this expression in reporting services when attached
> > to
> > an oracle database or is this something that cannot be fixed
>
>

Passing "Like" criteria in parameter to SQL Server Stored Procedure

Hello All,

I was hoping that someone had some wise words of wisdom/experience on this. Any assistance appreciated... feel free to direct me to a more efficient way... but I'd prefer to keep using a stored proc.

I'm trying to pass the selected value of a dropdownlist as a stored procedure parameter but keep running into format conversion errors but I am able to test the query successfully in the SQLDatasource. What I would like to do is this: select * from tblPerson where lastnames Like "A%" . Where I pass the criteria after Like. I have the values of the drop down list as "%", "A%", "B%", ...

I've been successfully configuring all of the other params, which includes another dropdown list (values bound to a lookup table also)... but am stuck on the above...

Thank you for any assistance,

Hi

WildCard should work in parameterized queries(stored procedure):

cmd.Parameters.Add(New SQLParameter("@.Name", "%" & text1.text & "%"))

You could take a look atParameterized Queries for more.

|||

thank you yyy8347,

I had seen that article but hadn't really found it helped until today.. along with another error I had the incorrect control ID referenced for my parameter.

I was stuck in the vicious circle believeing I wasn't using the correct escape characters in the dropdownlist for the value (" or ' s) but as it happened I was.

Thanks again and Happy Holidays,

|||

Hi

It seems you passed " or ' to the stored procedure. You just need to pass A% as parameter to procedure, Here is a simple Sample Code :

I use pub database,here is sproc:

alter procedure Test_Like (@.keywordvarchar(32))as beginselect *from titleswhere titlelike @.keywordend

And here is aspx:

<%@. Page Language="C#" AutoEventWireup="true" CodeFile="DDL_Filt_LikePara.aspx.cs" Inherits="DDL_Filt_LikePara" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"> <asp:ListItem>%</asp:ListItem> <asp:ListItem>T%</asp:ListItem> <asp:ListItem>S%</asp:ListItem> <asp:ListItem>M%</asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pubsConnectionString%>"SelectCommand="Test_Like" SelectCommandType="StoredProcedure"> <SelectParameters><asp:ControlParameter ControlID="DropDownList1" DefaultValue="%" Name="keyword" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> </div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="title_id" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="title_id" HeaderText="title_id" ReadOnly="True" SortExpression="title_id" /> <asp:BoundField DataField="title" HeaderText="title" SortExpression="title" /> <asp:BoundField DataField="type" HeaderText="type" SortExpression="type" /> <asp:BoundField DataField="pub_id" HeaderText="pub_id" SortExpression="pub_id" /> <asp:BoundField DataField="price" HeaderText="price" SortExpression="price" /> <asp:BoundField DataField="advance" HeaderText="advance" SortExpression="advance" /> <asp:BoundField DataField="royalty" HeaderText="royalty" SortExpression="royalty" /> <asp:BoundField DataField="ytd_sales" HeaderText="ytd_sales" SortExpression="ytd_sales" /> <asp:BoundField DataField="notes" HeaderText="notes" SortExpression="notes" /> <asp:BoundField DataField="pubdate" HeaderText="pubdate" SortExpression="pubdate" /> </Columns> </asp:GridView> </form></body></html>

GridView will display title begin with T or S or M according your selection in DDL.

Hope this helps.