Multiple Parameters - How To Configure The Logic?
Apr 15, 2008
Hi,
Im trying to set up a report in visual studio 2005 which uses multiple parameters (6) on which the user can filter to get the information they want.
here is what i want to happen - I've tried to explain as best i can but i dont think i've done a very good job...please ask questions if things need clarifying:
the 6 parameters are - userid, printers, default printer, area, applications, supervisor.
these need to be able to be filtered on any/all/combination of those parameters. parameters are set up and data is accessible, however when i run the report and try and filter on these it only works if all parameters are set to "All" (this is made available through a UNION select statement), or if i individually select a value for each category. if i choose a combination of say userid =myname, printers = myprinter, default=all, area=all etc it will match if ANY of the criteria is matched (including the ALL criteria, therefore it will always display all the values). i need to it work so if i pick the userid and the printer it will only match records containing BOTH the values i select, not either, or if i choose just the area, it will only return those records that contain that department.
Below is the WHERE statement i am currently entering in
WHERE (AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (((AllUserData.nvarchar3 LIKE @Area) OR (@Area = 'All')) OR ((AllUserData.nvarchar4 LIKE @Default) OR (@Default = 'All')) OR ((AllUserData.ntext1 LIKE '%' + @Printer + '%') OR (@Printer = 'All')) OR ((AllUserData.ntext2 LIKE '%' + @Application + '%') OR (@Application = 'All')) OR ((@Application = 'All') AND (@Printer = 'All') AND (@Default = 'All') AND (@Area = 'All')))
and here is what Visual Studio automatically translates that into as soon as i execute the script:
WHERE (AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (AllUserData.nvarchar3 LIKE @Area) OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Area = 'All') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (AllUserData.nvarchar4 LIKE @Default) OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Default = 'All') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (AllUserData.ntext1 LIKE '%' + @Printer + '%') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Printer = 'All') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (AllUserData.ntext2 LIKE '%' + @Application + '%') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Application = 'All') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Area = 'All') AND (@Default = 'All') AND (@Printer = 'All') AND
(@Application = 'All')
Note: AllUserData.tp_ListId references a specific row in the database which must be queried to get the correct information. so it must match on that before anything else.
any help structuring the logic i am using or better understanding the way visual studio/sql handles this kind of thing would be fantastic! thanks!
Shannon
View 2 Replies
ADVERTISEMENT
Jan 4, 2008
Guys, here is my scenario:
I have 3 tables
1) Vendor
2) Service
3) Service Product
Each vendor has the ability to have any number of services, all with any number of service products.
How should I setup my page so I can edit/insert vendors, and tie a list of service products and services to them?
My plan was to have a Formview with either a checkboxlist, or multilist (to select multiple items - but they don't work that way) and then store selected items in another table (each item selected with its own record) to hold onto the vendor id and tie it to a service id and a product id, but I was unsure how to do this.
Any help would be greatly appreciated.
FYI I am writing in VB.
View 2 Replies
View Related
Oct 25, 2007
Hi,
I have 2 related tables in SQL 2005. I am using Business Logic Handler on one of those tables during Merge Replication to reject data for few rows during web synchronization. Once a row is rejected, I would like to run the Business Logic Handler for the related table to reject changes for the row with same foreign key as the rejected row in first table.
Not sure if I need to create separate Business Logic Handlers for each table or can it be achieved in a single Handler.
Also is it possible to get a list of rejected rows back from the handler?
Thanks for help.
View 1 Replies
View Related
May 15, 2008
Hi folks,
I am exploring various solutions to setting up a scalable database configuration. To do a proof of concept, I need to setup the database in such a way that the data is partitioned on 2 physical machines.
I looked at distributed partitioned views, which allowed me to create a table on databases residing on two different servers, and then I created distributed view (with appropriate range constraints) that supported distributed selects, joins, updates, and inserts. This seemed to be the solution we were looking for.
However, I ran into a major problem because of the fact that distributed partitioned views don't allow a table to maintain an Identity column with an identity seed.
I did more research and found the Partitioned Table concept that was introduced in SQL 2005. This partitioned table allows us to have a single table which is horizontally partitioned across multiple file groups. This table now is just like a regular table in that it supports the identity column and identity seeds and guarantees index integrity. We were successful in implementing the partitioned table on a single machine, with three filegroups.
Now I need to move these file groups to different physical machines. I have found some articles on adding file group on additional hard disks on the same machine - but nothing to setup a database that is comprised of multiple servers that each handle a part of the data partition.
Is it possible to create a database comprise of filegroups residing on different physical machines?
Thanks.
View 12 Replies
View Related
Dec 21, 2007
Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
Thanks in advanced
View 4 Replies
View Related
Jun 28, 2006
hello. I have a database that a client developed that I need to pull data from. it consists of articles that fall into a range of 3 main categories. each article will have up to 7 different subcategories they fall into. I need to be able to sort by main category as well as by subcategory. But when I create the SQL query it gets really messy. I tried using WHERE Cat1= comm OR leg OR and so on, but there are seven categories so this gets very cumbersome and doesn't quite work. Is there a way to create an array or a subquery for this? I am a total newbie, so any help is much appreciated!
View 2 Replies
View Related
Mar 4, 2007
Hi all. I have the problem on query.
This is my query.
select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
where P.religion in ('Born Again','Baptist', 'Catholic')
How could I make a query that returns a result for either "Born Again, Baptist, or Catholic". The parameter would depend on the input of the user depending on how many religion the user inputed.
If the user inputed Born Again and Baptist, the result is the employees that have a religion of Born Again and Baptist.
Thanks
-Ron-
View 6 Replies
View Related
Aug 15, 2007
I need to pass a parameter to a stored procedure that allows the user to select all store numbers or a combination thereof.
The portion that selects all works fine. I am have trouble with the later. My code looks like this:
CREATE PROCEDURE vch_GetSurgeryPatientsWithoutOrdersFromEyeSite_clb
(
@StartDate datetime,
@EndDate datetime,
@center varchar (1000)
)
AS
IF @StartDate IS NULL
BEGIN
Set @StartDate = GetDate()
Set @EndDate = (GetDate ()+90)
END
Declare @StoreList TABLE ( CenterID int )
-- Get our Centers
IF ( @Center = 'ALL' ) or ( @Center IS NULL )
BEGIN
INSERT @StoreList
SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock)
END
ELSE
BEGIN
INSERT @StoreList
SELECT DISTINCT POS_Site_ID
FROM LVIGP.dbo.POS40108 (nolock) WHERE POS_Site_ID = @Center
END
the first if statement returns all of the store number like this
CenterID
001
002
010
024
057
the second if returns nothing
I tried using coalesce but it returns the store number like this:
CenterID
001, 002, 010, 024, 057
This does not work with the rest of my program.
View 1 Replies
View Related
Sep 11, 2007
Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.
Thanks.
View 6 Replies
View Related
Sep 6, 2006
Hi All,
I have a database with very heavy volume of data.
I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries.
Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc.
Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ?
Thanks in advance,
HHA
View 4 Replies
View Related
Sep 18, 2007
Is it possible to have a parameter with one label but multiple values. For example:
Label Value
---------------------------------------------
Machinist (100,200,300)
Is it possible to set up an expression that when the user selects this label it will look for the job codes 100, 200 and 300
and return all employees in those codes?
Thanks,
A
View 2 Replies
View Related
Apr 25, 2008
Hi,
I am working on SSRS. I need to open a new report from one report when user clicks on some particular summerized count link.
Its a sort of drilled down report. I am not getting how to pass the respected Ids (more than one) to the next report when user clicks on the link in the 1st report. These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from the database.
Please help.
Regards,
Sachin
View 4 Replies
View Related
Oct 30, 2007
Hello,
I have a report in which one parameter has with multiple selections (List of CheckBoxes). SSRS automatically adds a "Select All" value as the first option. I'm showing a User Type list, but for example, usually the user running the report will be selecting two or three sets of "User Types" (*), then I named those selections and put them in the list also. The list looks like:
"Select All"
IT Users (*)
HR Users (*)
Programmers
DB Analyst... etc...
What I'm trying to do is that when the user selects "IT Users" (per example), then the options "Programmers" and "DB Analyst" will be checked automatically, because they belong to "IT Users" group.
Any help will be appreciated... Thanks in advance
View 2 Replies
View Related
Apr 4, 2008
Hello,
Using a Report Designer of the SQL Server 2008 connecting to an ORACLE database:
- I want to use Parameters filters with multiple Values but i get the error :
"FilterExpression for the data set 'DATAset1' cannot be performed. Cannot compare data of types System.String and System.Object[]. Please Check the Data Type Returned by the FilterExpression"
without the multiple Values it works but don't resolve my problem.
The filter configuration is =Fields!DT_OPERACAO.Value = =Parameters!FLT_Ano_Lectivo.Value
how can i solve this situation ?
View 3 Replies
View Related
Feb 23, 2007
In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.
Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.
The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.
Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?
Thanks,
Mike
View 15 Replies
View Related
Dec 28, 2007
I have a SQL query that goes like this
"select * from Product where ProductID in (1,2,3)"
How can i create a stored procedure where a single input parameter can take multiple values?
Can anyone help me with this?
View 4 Replies
View Related
Mar 26, 2008
Hi,
I want to create a stored procedure which I can pass multi parameters. This is what I need, I have a gridview which is used for displaying customer info of each agent. However, the number of customers for each agent is different. I will pass customer names as parameters for my stored procedure. Here is a sample,
CREATE PROCEDURE [dbo].[display_customer]
@agentID varchar(20),
@customer1 varchar(20),
@customer2 varchar(20),
..... -- Here I do know how many customers for each agent
AS
SELECT name, city, state, zip
FROM rep_customer
WHERE agent = @agentID and (name = @customer1 or name = @customer2)
Since I can not decide the number of customers for each agent, my question is, can I dynamically pass number of parameters to my above stored procedure?
Thanks a lot!
View 6 Replies
View Related
Feb 12, 2006
Hi,Hope if someone can help me here. Keep in mind I an fairly new to .NET and SQL and am learning to break my MS Access habit :)
I have a web form that is using a SqlDataSource and a FormView control. In addition to this I have 2 text boxes. What I am trying to do is display results in the FormView based on what a user types into one of the Text Boxes (one or the other…Not both)
The SELECT statement in the SqlDataSource looks like this in concept.
SELECT Field1, Field2, Field3, Field4FROM dbo.MYTABLEWHERE (Field1 = @Field1) AND (Field2 IS NULL)OR (Field2 = @Field2) AND (Field1 IS NULL)
I have the two text boxes pointing at the parameters (@Field1 and @Field2) so in theory I would expect that when a user populates one of the text boxes and clicks a button to databind the FormView it would display a record matching that criteria…. But it’s not all I get is a blank/missing FormView.
I tried different variations on the SQL statement and tried using = '' instead of IS NULL but still the same results. However, if I populate one text box with a value that I know is not in my table and populate the other with a value of which I know exists in my table is…It works.What am I missing?
View 13 Replies
View Related
Apr 12, 2006
OK, 1st, I have looked at every article that has come back on a "Stored Procedures" Search on this site, and am more confused than when I started looking for my answer.
This is what I need to do:
I need to pass a search sentence to a stored procedure, have the stored procedure break up the space delimited string and then do a "like" and "contains" in the WHERE statement, on what was sent to the stored procedure, and then return the results to a gridview for the person to select which item best answers their search.
I am just totally lost with using a stored procedure. I have done this in webmatrix when I coded it all into the aspx page, or into the codepage, but I have never done it with a stored procedure on the sql server, never sent a varible to a stored procedure... and am totlaly lost, or just do not understand how to do it.
Any help would be great.
Thanks in advance.
D4D
View 2 Replies
View Related
Aug 3, 2012
Here is my stored procedure:
ALTER PROCEDURE dbo.SP_UpdateFixedRev
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@Number int,
@FixedRev money
)
AS
BEGIN
/* SET NOCOUNT ON */
Update Ticket set FixedRev = @FixedRev where Number = @Number;
End
Here is my code:
Dim dbConn As New OleDbConnection
Dim dbComm As OleDbCommand
dbConn.ConnectionString = connStr 'connStr is class-level vrbl
dbConn.Open()
dbComm = dbConn.CreateCommand
dbComm.Parameters.Add("@Number", OleDbType.Integer).Value = txtDatabaseTicketNo.Text
dbComm.Parameters.Add("@FixedRev", OleDbType.Currency).Value = txtFixedRev.Text
dbComm.CommandText = "SP_UpdateFixedRev"
dbComm.CommandType = CommandType.StoredProcedure
dbComm.ExecuteNonQuery()
dbConn.Close()
However its not updating my database when I run the app from a button click event.
View 7 Replies
View Related
Nov 26, 2014
Using a full search index with the following query works with just one parameter.
declare @P0 varchar(50) = '"First*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0)
How do I make it work if I have two parameters, while also protecting the parameters from injection attacks?
declare @P0 varchar(50) = '"First*"'
declare @P1 varchar(50) = '"Second*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0 AND @P1)
If they weren't parameters, you put single quotes around the ' @P0 AND @P1' to get this query to work.
View 2 Replies
View Related
Apr 25, 2008
Hi,
I am working on SSRS. I need to open a new report from one report when user clicks on some
particular summarized count link.
It is a sort of drilled down report. I am not getting how to pass the respective Ids (more
than one) to the next report when user clicks on the summarized link in the 1st report.
These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from
the database.
View 3 Replies
View Related
Apr 25, 2008
Hi,
I am working on SSRS. I need to open a new report from one report when user clicks on some
particular summarized count link.
It is a sort of drilled down report. I am not getting how to pass the respective Ids (more
than one) to the next report when user clicks on the summarized link in the 1st report.
These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from
the database.
View 1 Replies
View Related
Nov 1, 2007
Hi,
Am having difficulty with report/query parameters, where the report now regularly tells me that I must declare the scalar variable @Site.
I want to use 3 data sources :
1. to select a site from a list of sites - works fine and I can select at runtime
2. Once this is selected - I need to present a pair of dates to the user - min and max for data for the site - needs selection 1 to have been performed. Now I HAVE seen this work - once, the date selectors were greyed out until the site had been chosen, then they became available. Now I get the scalar variable error.
3. Finally I will pull the data with 3 parameters (site, startdate, enddate)
This was almost working, and the detail was produced for item 3, until I introduced the date selection option. Now neither item 2 or 3 will accept the users selected site - its from a drop-down.
All 3 queries are being performed by SQL SP's :
1. exec getsitelist - used to populate the dropdown to select @Site
2. exec getdates @Site - used to preset the start/end dates (NB I would really like calendar control here to select the date, with the value pulled from 1 to set the start point - but hey lets walk first ;-).
3. exec GetData @Site, @Start, @End
SOooo - can SSRS2005 even support dependent parameters of this type ??.
If so - whats the best way to create the parameters etc. ??. NB I can see all 3 parameter defs in 'Report Parameters'.
Many thanks - hopefully - for my sanity :-O.
Regards
Graham
View 1 Replies
View Related
Oct 9, 2007
Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan
View 2 Replies
View Related
Nov 21, 2007
If I were to create a stored procedure that searches a table using (optional) multiple parameters, what would be the best way to do the search. I want to try and avoid using several "IF" statements (like IF @FirstName IS NOT NULL, etc). How would I do it, or would I just be better off using several "IF" statements? Thanks...
CREATE PROCEDURE intranet_search_GetEmployeesBySearch
(
@FirstName NVarChar(100),
@LastName NVarChar(100),
@Phone NVarChar(50),
@Cell NVarChar(100),
@Pager NVarChar(100),
@Ext NVarChar(50),
@Email NVarChar(100),
@Department NVarChar(200),
@Position NVarChar(100),
@IsManager Bit
)
AS
BEGIN
SET NOCOUNT ON;
END
GO
View 8 Replies
View Related
Mar 9, 2004
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?
Do I need just a select statement to do this? What is the difference between the output and select statements?
Thanks in advance.
View 1 Replies
View Related
Apr 11, 2006
I was curious to know if it the amount of data sent to the sql server mattered.
I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).
So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.
Thanks
View 1 Replies
View Related
Dec 1, 2014
Using a full search index with the following query works with just one parameter.
declare @P0 varchar(50) = '"First*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0)
How do I make it work if I have two parameters, while also protecting the parameters from injection attacks?
declare @P0 varchar(50) = '"First*"'
declare @P1 varchar(50) = '"Second*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0 AND @P1)
If there weren't parameters, you put single quotes around the '@P0 AND @P1' to get this query to work.
In addition, while "where contains((Col1,Col2),@P0) and contains((Col1,Col2),@P1)" works, it appears to increase the execution time.
View 0 Replies
View Related
Aug 31, 2007
I have a couple of questions that I hope someone might be able to provide some direction on.
1. Why can't you call a proc when the proc uses temp tables? I wrote a simple proc (select * from table) and the data subscription works perfect. I then take that same query and put it into a temp table and the data set can't be validated by the subscription, Why?
2. Why can't you pass a simple execute statement in a data subscription? (execute dbo.RptTakeTheLeadFLSDinnerCompetition @FiscalQuarter ='2007-Q3', @StoreRegionID = '9231', @StoreType = 'Full-Line Store') works perfect in SQL but once you try and validate in the data subscription is fails.
3. Another question, why if I dumb down the proc and the number of parameters does it work? Are there limitations on the number of parameters you can pass into a proc from a data driven subscription?
Basically, I want to declare a few variables dynamically and pass them into a execute statement that calls the proc. Which in turn runs the report.
Any ideas on where I seem to be going wrong?
View 3 Replies
View Related
May 17, 2007
Hi!
I'm using Reporting Services Integration Mode and I have a page that has a number of report viewer webparts on it to display reports. Is there a way that I can add another webpart to pass parameters to all the reports at once instead of individually? I am trying to build a dashboard and this is one of the requirements. I see on the Report Viewer web part, that there is an option in Connections to 'Get report parameters from' , but it is greyed out. Any ideas on how to do this?
Thanks!
Brian
View 6 Replies
View Related
Jul 5, 2007
Hi all,
I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.
Thank you,
View 2 Replies
View Related
