Passing Multiple Parameters

hi, i am Create a search page"VB 6" which takes parameters as passing values from 6 different textboxes

on click ok the result should get in a Grid

User May be Enter 1 or 2 or 3 or all 6 values with different Combination

Kindly Help me how can I control Querey

Regards

Fakhruddin

Well, there are lots of variations for that.

Can you post some information about your table(s) the query you're using?

|||

Here you have a couple of outstanding articles about that theme.

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||yet another split problem.. here's another resource|||

Why dynamic SQL here, You can do it straight forward,

Have a 6 Parameters in the SP.

Pass the each value from the textbox (if user doesn't enter any value for the text box, pass NULL).

Then use the following code on your sp,

Code Snippet

Create proc SearchData

(

@.Param1 as Varchar(100) = null,

@.Param2 as Varchar(100) = null,

@.Param3 as Varchar(100) = null,

@.Param4 as Varchar(100) = null,

@.Param5 as Varchar(100) = null,

@.Param6 as Varchar(100) = null

)

As

Begin

Select

..

..

From

Tables ...

Where

(@.Param1 is NULL or Column1 = @.Param1)

And (@.Param2 is NULL or Column2 = @.Param2)

And (@.Param3 is NULL or Column3 = @.Param3)

And (@.Param4 is NULL or Column4 = @.Param4)

And (@.Param5 is NULL or Column5 = @.Param5)

And (@.Param6 is NULL or Column6 = @.Param6)

End

|||

Hi Manivannan.D.Sekaran,

I wish it is as simple as that, but not, it is not. Depend on the indexes existing for the table in question, the kind of expression used in the "where" clause and the selectivity of the indexes based on the parameters value, the approach you use will be very important regarding to performance. The expression you used, does not yield good performance when there is and index by that column with a high selectivity. See the execution plan of the following examples:

Code Snippet

use northwind

go

create procedure dbo.p1;1

@.orderid int = null

as

set nocount on

select orderid, customerid, orderdate

from dbo.orders

where orderid = @.orderid or @.orderid is null

go

create procedure dbo.p1;2

@.orderid int = null

as

set nocount on

if @.orderid is null

select orderid, customerid, orderdate

from dbo.orders

else

select orderid, customerid, orderdate

from dbo.orders

where orderid = @.orderid

go

set showplan_text off
go

exec dbo.p1;1 10250

go

exec dbo.p1;2 10250

go

set showplan_text off

go

drop procedure dbo.p1

go

You will find a deep analysis in the articles, written by Erland Sommarskog, I posted in my previous message.

AMB

|||

Mani,

I confess that I often use the method you posted (or at least a variation using coalesce( @.Param, Column). I limit my usage to known 'smallish' tables where I am satisfied there is little probability of index usage.

The warning that Erland makes in his research/articles is that when the tables are large, the query plan may be totally wrong for various combinations of supplied parameters when using this approach.

So yours is a decent suggestion, yet one that should be presented with a caveat about the potential issues.

Hunchie's correct in providing example code so that all reading this thread can examine the issues for themselves. If you add the following code to Hunchie's example, you will see that the coalesce() option has a more efficient execution plan than the

(@.Param1 is NULL or Column1 = @.Param1)

option. However, the 'else' option produces the 'best' plan of the three methods.

All that said, sometimes the simplicity of the coalesce option with small data sets makes it a choice to consider.

Code Snippet

create procedure dbo.p1;3
@.orderid int = null
as
set nocount on
select orderid, customerid, orderdate
from dbo.orders
where orderid = coalesce( @.orderid, orderid )
go

exec dbo.p1;3 10250
go

|||It is definitely important that you give more information. If the text boxes are not related, there is one solution, and if they are an array, there is another. Also, if there are millions of rows, you may have to do some other things (like using dynamic SQL or multiple stored procedures.)

Passing multi value parameter between reports

I've seen some theads on this but cant seem to get this right. Report A has 5 parameters 2 of them are multi value-- when I click on part of Report A I want to jump to report B and pass all parameter values from report A. The single value ones work but multi value only passes on value and not all values selected-- what am I missing here? Do I need to pass the values in the multi value parameters in an array?

thanks

k

Duuhhhh

someone definelty has a case of the mondays going-- changed =Parameters!code.Value(0) to =Parameters!code.Value

and all is well.

k

Passing Field values in a URL

Hello,
I need to pass field values to a URL when we click on a particular datavalue
on the Report. Basically I am trying to put up a URL on a column of the
report. And I would like to pass that corresponding column value dynamically.
Please someone let me know how to do that. At present I was trying to do
something like this that doesnt work :
http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
values of that particular row. Please someone give me a idea.
Thanks,
BabithaAre you trying to pass parameters into the Rpt Services report to be used for
rendering, or are you trying to extract them out of a rendered report?
sebring1130
"Babitha" wrote:
> Hello,
> I need to pass field values to a URL when we click on a particular datavalue
> on the Report. Basically I am trying to put up a URL on a column of the
> report. And I would like to pass that corresponding column value dynamically.
> Please someone let me know how to do that. At present I was trying to do
> something like this that doesnt work :
> http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
> values of that particular row. Please someone give me a idea.
> Thanks,
> Babitha|||I have the same problem, I am tryinf to read a row from reportviewer control
(data is alrady shown by reportviewer on the screen and I need to select one
line and process it), is there ny example?
"sebring1130" wrote:
> Are you are trying to pass parameters into the Rpt Services report to be used for
> rendering, or are you trying to extract them out of a rendered report?
> sebring1130
>
> "Babitha" wrote:
> > Hello,
> >
> > I need to pass field values to a URL when we click on a particular datavalue
> > on the Report. Basically I am trying to put up a URL on a column of the
> > report. And I would like to pass that corresponding column value dynamically.
> >
> > Please someone let me know how to do that. At present I was trying to do
> > something like this that doesnt work :
> >
> > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> >
> > where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
> > values of that particular row. Please someone give me a idea.
> >
> > Thanks,
> > Babitha|||I too am trying to achive the same outcome. I don't think this is going to
be doable.
"Babitha" <Babitha@.discussions.microsoft.com> wrote in message
news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> Hello,
> I need to pass field values to a URL when we click on a particular
datavalue
> on the Report. Basically I am trying to put up a URL on a column of the
> report. And I would like to pass that corresponding column value
dynamically.
> Please someone let me know how to do that. At present I was trying to do
> something like this that doesnt work :
> http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> where DATAKEY is one of my fields. But this doesnt help me pass the
dynamic
> values of that particular row. Please someone give me a idea.
> Thanks,
> Babitha|||Have you tried using the hyperlink feature of the cells on the report? If
you go into the texbox cell's properties and hit the "navigation" tab there
are several options to set up hyperlinks so that when you click on a cell on
the report you can automatically jump to a new URL. It looks like the URL
can be anything ... I'd be very surprized if you couldn't insert parameters
and field values in the the URL.
sebring1130
"Art Simcoe" wrote:
> I too am trying to achive the same outcome. I don't think this is going to
> be doable.
> "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > Hello,
> >
> > I need to pass field values to a URL when we click on a particular
> datavalue
> > on the Report. Basically I am trying to put up a URL on a column of the
> > report. And I would like to pass that corresponding column value
> dynamically.
> >
> > Please someone let me know how to do that. At present I was trying to do
> > something like this that doesnt work :
> >
> > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> >
> > where DATAKEY is one of my fields. But this doesnt help me pass the
> dynamic
> > values of that particular row. Please someone give me a idea.
> >
> > Thanks,
> > Babitha
>
>|||I am also trying to achieve this very ting. It appears you cannot insert
parameters and field values into the URL. You can build the expression but
the resulting URL simply contains the variable name you enter, not its value.
Anyone solved this?
"sebring1130" wrote:
> Have you tried using the hyperlink feature of the cells on the report? If
> you go into the texbox cell's properties and hit the "navigation" tab there
> are several options to set up hyperlinks so that when you click on a cell on
> the report you can automatically jump to a new URL. It looks like the URL
> can be anything ... I'd be very surprized if you couldn't insert parameters
> and field values in the the URL.
> sebring1130
>
> "Art Simcoe" wrote:
> > I too am trying to achive the same outcome. I don't think this is going to
> > be doable.
> >
> > "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> > news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > > Hello,
> > >
> > > I need to pass field values to a URL when we click on a particular
> > datavalue
> > > on the Report. Basically I am trying to put up a URL on a column of the
> > > report. And I would like to pass that corresponding column value
> > dynamically.
> > >
> > > Please someone let me know how to do that. At present I was trying to do
> > > something like this that doesnt work :
> > >
> > > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> > >
> > > where DATAKEY is one of my fields. But this doesnt help me pass the
> > dynamic
> > > values of that particular row. Please someone give me a idea.
> > >
> > > Thanks,
> > > Babitha
> >
> >
> >|||What you need to to is to create the string in the expressions:
i.e.
="http://blah.mmm.com/etc etc" & fields!fieldname.value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:EB4306DC-595A-447F-A07D-B5A7BE18C1EF@.microsoft.com...
> I am also trying to achieve this very ting. It appears you cannot insert
> parameters and field values into the URL. You can build the expression
but
> the resulting URL simply contains the variable name you enter, not its
value.
> Anyone solved this?
> "sebring1130" wrote:
> > Have you tried using the hyperlink feature of the cells on the report?
If
> > you go into the texbox cell's properties and hit the "navigation" tab
there
> > are several options to set up hyperlinks so that when you click on a
cell on
> > the report you can automatically jump to a new URL. It looks like the
URL
> > can be anything ... I'd be very surprized if you couldn't insert
parameters
> > and field values in the the URL.
> >
> > sebring1130
> >
> >
> > "Art Simcoe" wrote:
> >
> > > I too am trying to achive the same outcome. I don't think this is
going to
> > > be doable.
> > >
> > > "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> > > news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > > > Hello,
> > > >
> > > > I need to pass field values to a URL when we click on a particular
> > > datavalue
> > > > on the Report. Basically I am trying to put up a URL on a column of
the
> > > > report. And I would like to pass that corresponding column value
> > > dynamically.
> > > >
> > > > Please someone let me know how to do that. At present I was trying
to do
> > > > something like this that doesnt work :
> > > >
> > > > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> > > >
> > > > where DATAKEY is one of my fields. But this doesnt help me pass the
> > > dynamic
> > > > values of that particular row. Please someone give me a idea.
> > > >
> > > > Thanks,
> > > > Babitha
> > >
> > >
> > >

Pass value of report parameter from URL

Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.

Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"

it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?

Thanks for your help!

You mean something like this? http://msdn2.microsoft.com/en-us/library/aa256630(SQL.80).aspx

It says it is for SQL (80), so I am not sure what problems carrying that over to 2005 will present.

Hope that helps.

|||well it looks like a step towards the solution, but it didn't really get me far...

the URL to my report looks like this:
http://reports.mycompany.com/Reports2005/Pages/Report.aspx?ItemPath=MyReport

in MyReport, I only have a textfield called "project" which I have to fill. When I put in a sample value "ABC", run the report and then take a look at the html code, I have something like this:

<tr IsParameterRow="true">
<td class="ParamLabelCell">
<span>project :</span>
</td>
<td class="ParamEntryCell" style="padding-right:0px;">
<span><input name="ctl137$ctl00$ctl03$ctl00" type="text" value="ABC" size="30" id="ctl137_ctl00_ctl03_ctl00" />
</span>
</td>

how can I pass the "ABC" text to this textfield over the URL (the report doesn't need to be run, I only want the textbox filled when I load the report for the first time!)|||still no idea?|||

I know it is kind of a late response, but your aspx page with the dropdown listbox should be able to pass the information. So, on the report page where you want the info, add a textbox and set the value to: Request.Form ("myfield") where myfield is the name of the dropdown box on the previous page.

The page the data is sent from will need to post to the report page I think.

Pass value of report parameter from URL

Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.

Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"

it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?

Thanks for your help!

You mean something like this? http://msdn2.microsoft.com/en-us/library/aa256630(SQL.80).aspx

It says it is for SQL (80), so I am not sure what problems carrying that over to 2005 will present.

Hope that helps.

|||well it looks like a step towards the solution, but it didn't really get me far...

the URL to my report looks like this:
http://reports.mycompany.com/Reports2005/Pages/Report.aspx?ItemPath=MyReport

in MyReport, I only have a textfield called "project" which I have to fill. When I put in a sample value "ABC", run the report and then take a look at the html code, I have something like this:

<tr IsParameterRow="true">
<td class="ParamLabelCell">
<span>project :</span>
</td>
<td class="ParamEntryCell" style="padding-right:0px;">
<span><input name="ctl137$ctl00$ctl03$ctl00" type="text" value="ABC" size="30" id="ctl137_ctl00_ctl03_ctl00" />
</span>
</td>

how can I pass the "ABC" text to this textfield over the URL (the report doesn't need to be run, I only want the textbox filled when I load the report for the first time!)|||still no idea?|||

I know it is kind of a late response, but your aspx page with the dropdown listbox should be able to pass the information. So, on the report page where you want the info, add a textbox and set the value to: Request.Form ("myfield") where myfield is the name of the dropdown box on the previous page.

The page the data is sent from will need to post to the report page I think.

Pass sorting parameter to Stores Procedure

I used Datagrid to show "Title", "Location" and "Date", It works very well.
I want to sort DataGrid data, that is when user click the "Title", "Location" or "Date",
my asp.net code will through class and send "Sort" parameter to stores procedure to get the new data and bind to DataGrid.

Here is my stores procedure:

CREATE Procedure JobSearch
(
@.Search varchar(150),
@.Sort varchar(50)
)
AS

SELECT
JobTitle,
JobLocationCity,
JobLocationState,
PostDate

FROM
Job

WHERE
JobTitle LIKE '%' + @.Search + '%'
OR
JobKeywords LIKE '%' + @.Search + '%'

IF @.Sort = "Title"
ORDER BY JobTitle
IF @.Sort = "Location"
ORDER BY JobLocationState, JobLocationCity
IF @.Sort = "PostDate"
ORDER BY PostDate DESC

When I test stores procedure in SQL Server, I got the error about "Error 156: Incorrect syntax near the keyword 'ORDER' ".

Who has experience about stores procedure, please help me to correct this error.I am doing the same thing by working around using a string. Such as

Declare @.sql as varchar(2048)
...
exec(@.sql)|||Why you cannot use datagird to do the sorting?|||Very instersting, where's your code? In class or stores procedure? Could you show me more detail you code?

Thanks!|||I don't understand what you said. I just want to use datagrid to do the sorting.

The procedure as following:

1. Using the datagrid's AllowSorting property.
2. Writing a sortcommand event handler (retrieve e.SortExpression value, pass this value to component-class, class call SQL Server-stores procedure to retrieve new sort data and return to sortcommand event handler, and finally call bind function).

This is tree tiers program. If verything (include dataconnection and stores procedure) write in the same aspx page (two tiers program), it works.

I can write six different stores procedure in SQL Server, six classes in Component, then create a sub SortDataGrid function in aspx page to call these class and stores procedure, but this is not best way. I try to find the best way, one calss and one stores procedure in SQL Server. This stores procedure accept one parameter to do any kind of sorting.

What's your best way, could you show me?

Thanks!|||I think you'll need to use dynamic sql to achieve your desired effect. I've sketched it out below. Just make sure that the strings are closed/opened where appropriate. I don't have query analyzer available right now, so I'm sure there's a missing quote or an extra one somewhere below. But that's basically how you can do it.

CREATE Procedure JobSearch
(
@.Search varchar(150),
@.Sort varchar(50)
)
AS
DECLARE @.sql varchar(200)
SET @.sql =
'SELECT
JobTitle,
JobLocationCity,
JobLocationState,
PostDate
FROM Job
WHERE
JobTitle LIKE ''%'' + @.Search + ''%''
OR
JobKeywords LIKE ''%'' + @.Search + ''%'' '
IF @.Sort = "Title"
SET @.sql = @.sql + ' ORDER BY JobTitle'
IF @.Sort = "Location"
SET @.sql = @.sql + ' ORDER BY JobLocation, JobLocationCity'
IF @.Sort = "PostDate"
SET @.sql = @.sql + ' ORDER BY PostDate DESC'
exec(@.sql)

|||Here is how you can sort the datagird by usingDataView.|||You don't HAVE to use dynamic SQL. I'm pretty sure you can do this:

select ...
from ...
order by case when @.Sort = 1 then Column1 when @.Sort = 2 then Column2 end|||I just checked this, and yes, it works. When I tried this last night at home on a laptop with no sql server installed, I used some website's online sql server interpreter to run a query similar to this, and it gave me an error, so I figured it was wrong. I just checked at work, and it works fine.|||The only problem with doing it this way is that Column1, Column2, etc either have to be the same data type or you need to CAST them to become the same data type.

select ...
from ...
order by case when @.Sort = 1 then Column1 when @.Sort = 2 then Column2 end

You might find it better to use this method:

select ...
from ...
order by case when @.Sort = 1 then Column1 end, case when @.Sort = 2 then Column2 end

Terri|||Thanks Vito1281, Tmorton, and Pierre gave me about stores procedure suggestions. I will test these ways today and will report the result to you.

JimmyM suggest use Dataview, consider the two potential downsides:
1. there is the potential for the cached data to become stale.
2. Viewstate is maintained as a hidden HTML field, adding large objects to the viewstate can add several kilobytes, cause load the page very slowly.

So use viewstate isn't first choose in this case.

Thanks everyone !|||You may not quite understand what DataView is. DataView has nothing to do with caching data or Viewstate, it is a view (like the view you use at database) of a datatable in a dataset.
You can do something like filtering and sorting on the view and bind the view to your disply control. Here is one way of how to use it:

[Visual Basic]
Dim prodView As DataView = New DataView(prodDS.Tables("Products"), _
 "UnitsInStock <= ReorderLevel", _
 "SupplierID, ProductName", _
 DataViewRowState.CurrentRows)
[C#]
DataView prodView = new DataView(prodDS.Tables["Products"],
 "UnitsInStock <= ReorderLevel",
 "SupplierID, ProductName",
 DataViewRowState.CurrentRows);

|||JimmyM,

I am so sorry about it I made mistake to be DataView as ViewState. I never use Dataview in my program and I will learn about it. I will try to run the code follow your link.

I just test the stores procedure, which Vito1281 show to me, dynamic SQL. It work very well in SQL Server, and I will use this way first.

Amorton and Pierre have another suggestion (select ... from ...order by case when @.Sort = 1 then Column1 when @.Sort = 2 then Column2 end), I will test it later.

Thanks for everyone's help!