Search Based On User Input
Jul 25, 2007
I have a search page that allows users to type/select values contined within the entry they're looking for. My SELECT statement returns columns in a table that get compared to the user input. So if someone selected Status (Open) then all of the 'Open' Request entries should populate the search page. How do I phrase the SELECT statement to compare values if the user gives them, but ignore the fields where no data was input when it's searching? So a search where no values were entered would return every Request Entry instead of an error; no entry.
Thanks!
View 2 Replies
ADVERTISEMENT
Jan 1, 2014
I am new for SQL SP, I have three table,
Table 1 - Merchant
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
Table 2 - Region
RegionID RegionName
1 Region1
2 Region2
3 Region3
Table 3 - Offer
OfferID RegionID MerchantID
1 1 3
2 1 2
3 1 1
4 2 2
5 2 4
I have two input parameter for this SP, @MerchantName = NULL and @RegionName = NULL
Expected Result Table
If input parameter @MerchantName = NULL and @RegionName = NULL
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
If we Search by RegionName="Region1" Ex: @MerchantName = NULL and @RegionName = 'Region1'
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
If we Search by RegionName="Region2" Ex: @MerchantName = NULL and @RegionName = 'Region2'
MerchantID MerchantName Zip
2 Merchant2 1002
4 Merchant4 1004
If we search by MerchantName='Merchant1' Ex: @MerchantName = 'Merchant1' and @RegionName = ''
MerchantID MerchantName Zip
1 Merchant1 1001
How to get the expected result for the above scenario.
View 3 Replies
View Related
Oct 17, 2007
Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements:
DropDownList1: This drop down list contains 3 choices- "=", ">", and "<".
Age: This text box is where someone would enter a number.
Button1: This is the form's submit button.
I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.
The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error:
"The variable name '@Age' has already been declared. Variable names must be unique within a query batch or stored procedure."
Any help would be appreciated.
Here is what I'm using in my code behind:
protected void Button1_Click(object sender, EventArgs e) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("SELECT * FROM People WHERE Age "); switch (DropDownList1.SelectedValue) { case "=": sb.Append("= "); break; case ">": sb.Append("> "); break; case "<": sb.Append("< "); break; } sb.Append("@Age"); SqlDataSource1.SelectCommand = sb.ToString(); SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text); }
View 7 Replies
View Related
Jan 5, 2015
I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
For this :
I calculated datediff + 1 and got sub jobs days BUT
say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec
so on paper I calulated as :
1-31 Dec 2014
PhaniMarketing NULL (Do not fall in Req Dt)
PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec)
PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days)
Total days 31
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
View 4 Replies
View Related
Jan 5, 2015
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
View 0 Replies
View Related
Mar 1, 2007
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
View 5 Replies
View Related
Jun 5, 2008
Hi
I am building a pretty simple intranet application where we need to be able to track changes to tables. The tracking feature do not need to be very advanced, we just need to see who changed something and what it was. Therefore I decided just to use a trigger based solution, but need some input/advice since my SQL skills is somewhat lacking.
Consider the following (mock-up) schema:
-- My content table
CREATE TABLE [Content](
[ContentGuid] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
[Data] [nvarchar](4000) NOT NULL,
[ChangedBy] [nchar](10) NOT NULL,
[MaybeNull] [int] NULL
)
-- My history table
CREATE TABLE [History](
[ChangedTable] [nvarchar](50) NOT NULL,
[ReferenceGuid] [uniqueidentifier] NOT NULL,
[ChangedBy] [nchar](10) NOT NULL,
[ChangedOn] [datetime] NOT NULL DEFAULT (getutcdate()),
[IsDelete] [bit] NOT NULL DEFAULT ((0)),
[Changes] [xml] NOT NULL
) ON [PRIMARY]
-- My insert/update trigger
CREATE TRIGGER [RecordChangeOnInsertUpdate]
ON [Content]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Changes xml
SET @Changes = (select * from [inserted] for xml raw ('Content'), elements xsinil)
INSERT INTO [History]
([ChangedTable]
,[ReferenceGuid]
,[ChangedBy]
,[ChangedOn]
,[IsDelete]
,[Changes])
SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 0, @Changes
FROM [inserted]
END
-- My delete trigger
CREATE TRIGGER [RecordChangeOnDelete]
ON [Content]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Changes xml
SET @Changes = (select * from [deleted] for xml raw ('Content'), elements xsinil)
INSERT INTO [History]
([ChangedTable]
,[ReferenceGuid]
,[ChangedBy]
,[ChangedOn]
,[IsDelete]
,[Changes])
SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 1, @Changes
FROM [deleted]
END
I have decided to use the "one history table for all table changes" method. The changes for a row is stored in a xml column which obviously limits the total size of columns in a table being tracked, but that is not a problem in my application. In general I like this set up, since I will be able to change the schema continuously without having to change the triggers, and since the application will probably evolve a lot over the coming months this is pretty important to me.
EDIT: I should add that all the tables I will be tracking have a uniqueidentifier column. This makes it possible to related table rows in the different tables being tracked with their history.
A few concerns with the above SQL:
- Can the inserted/deleted table change between "SET @Changes = (select * ..." and the "INSERT INTO ..." statement, such that the data is not valid? If so, how to work around that?
- If I were to (hypothetically) perform a "UPDATE [Content] SET [Data] = 'something'", not only is my update trigger called once for each row updated, but the XML added to the inserted row in the history table ([History].[Changes]) represent all the rows updated in the batch update. How do I get around this?
Are there any other issues I should be aware of?
Regards, Egil.
View 13 Replies
View Related
Feb 25, 2014
I need to sort based on the values sent from the input xml column resultdata
This is an value which i have in my database, sent from front end.
Currently the display is based on database storage level of master data. i will not be able to change the master data ordering. based on selection, i have to sort since i have an logic which stored the data into resultdata column
CREATE TABLE [dbo].[Table_Prod](
[ProdCode] [nchar](5) NULL,
[ProductName] [nchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table_Prod] ([ProdCode], [ProductName]) VALUES (N'RD ', N'Bus ')
[Code] ....
Current Result
IdSalesMonthSeasonDeptDataProductInfoProdDataProductInfoDescription
1Jan Winter RO,HOHeadOffice,RegionalOfficeRD,AI,SA Bus,Ship,PlaneSales for the month of Jan
2May Summer SO,HOHeadOffice,SalesOfficeAI,RD,TR,SA Bus,Ship,Train,PlaneMay sales information
3Sep Rain HO,RO,SOHeadOffice,SalesOffice,RegionalOfficeSA,TR,RD Bus,Ship,TrainSales for the month of Sep in the rain season
Expected Result
IdSalesMonthSeasonDeptDataProductInfoProdDataProductInfoDescription
1Jan Winter RO,HORegionalOffice, HeadOfficeRD,AI,SA Bus,Plane,ShipSales for the month of Jan
2May Summer SO,HOSalesOffice, HeadOfficeAI,RD,TR,SA Plane,Bus,Train,ShipMay sales information
3Sep Rain HO,RO,SOHeadOffice,RegionalOffice,SalesOfficeSA,TR,RD Ship,Train,BusSales for the month of Sep in the rain season
View 1 Replies
View Related
Apr 17, 2008
Hi,
i have a table like below
table:-
id col2 col3 col4 col5 col6
1 2 3 4 5 6
2 5 8 4 7 6
3 4 8 2 6 9
4 2 5 8 6 3
5 6 9 5 5 9
i want to write a stored procedure where i pass column names a parameters and i want to get result based on that
For ex:-
if i pass the parameters as
col3 and col5 where id =1 then i should the result as
id col3 col4 col5
1 3 4 5
and if i pass input as col2and col6 where id =3, the result should be
id col2 col3 col4 col5 col6
3 4 8 2 6 9
can anyone help on this??
View 3 Replies
View Related
Nov 3, 1999
I do appreciat your help, I want to run a store procedure which will show 6 months . I do not know how to write the procedure, here in the notion in my mind, I want to be able to pass an input parameter (month) to the procedure which will then run a query to show 6 months prior the input parameter month, how can I do that, thanks for your help
Ali
View 2 Replies
View Related
Aug 19, 2014
I need a Query for calculating the fiscal_week based on the input dates (start_date and end_date), though I got a query from this forum, it is not giving me exact result.
the sample is in the excel file with the attachment.
In the excel:
First tab tells you the raw_data what I am using to find the Fiscal_week
Second tab tell you the data where i found the mistake, and how I am expecting the output.
I also have attached the query I have got from this forum, query I have modified for fiscal week.
View 4 Replies
View Related
Aug 28, 2007
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?
View 5 Replies
View Related
Oct 19, 2015
I have 2 tables in my report, a multi value parameter (tp_title) is passed to the report. I am trying to hide the first tablix with this expression in the visiblity option of the tablix properties.
=IIF(Parameters!tp_Title.value = "Financial Years" or Parameters!tp_Title.value="ALL", False,True )
I get the error: The Hidden expression for the tablix ‘Tablix1’ contains an error: Overload resolution failed because no Public '=' can be called with these arguments:
'Public Shared Operator =(a As String, b As String) As Boolean':
Argument matching parameter 'a' cannot convert from 'Object()' to 'String'.
When I render the report. How the tablix can be made invisible based on the parameter?
View 6 Replies
View Related
Aug 14, 2002
Hi all,
Can anyone tell me about how to prompt a user to type when running a sql statement through query analyzer.
thanks
Jay
View 1 Replies
View Related
Apr 23, 2008
I have created a query that has a "When" and an "And" function.
I know need to make the query run on user input, i want it to ask for the date, then name to run the query
View 2 Replies
View Related
Nov 1, 2006
Afternoon...
I have a database for a pretend dvd hire company and need to create a query that uses user input as part of the query.
Ie, select * from dvd where actor = 'Tom Cruise';
The 'Tom Cruise' part of the query needs to be user input every time the query is ran... Can you help?
Many thanks in advance!?
View 14 Replies
View Related
May 14, 2007
Is it possible to allow user input via a Reporting Services Report? What I mean is could a report be created that would allow someone using the report to enter a number that would be written to the SQL database?
View 1 Replies
View Related
Sep 15, 2015
I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
BEGIN
IF @rptType = 1
BEGIN
SELECT LastName, FirstName, MiddleInitial
[Code] ....
As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.
So what I'm looking for is basically 2-fold.
View 5 Replies
View Related
Dec 18, 2007
Hi, Just wondering if I could make sure that "hackers" don't tamper with my querystring - which is a parameter for a SQL query. i.e... Dim ListOfValues as string = request.querystring("listOfValues")
'Output would be this: 324234,5445,554654,45632,SQL command : Delete From table where product_id IN (@ListOfValues)
cmd.paramaters.addwithvalue("@ListOfValues", ListOfValues )
How can I validate it so that hackers can't add any characters other than 'numbers' and ' , ' to the sql parameter? I have tried to tryparse the 'ListOfValues ' as an integer - by replacing "," with "" but an integer overflow occured. Any questions or ideas? Thanks
View 6 Replies
View Related
Nov 24, 2004
Hi,
I am new to ASP.NET so pordon me if my questions seems to be stupid.
I was given an assign to develop a ASP that would return results from a SQL server.
The problem is that part of my query to the SQL server comes from the webform
select a.invnum, a.invdate, a.duedate, a.invamt,a.payamt from vpshead a inner join vendors
b on (a.vendnum = b.vendnum) where (a.vendnum = " & user.text & " ) and (b.vendpass = "
& Pass.text & " ) and (a.payflag <> 'V') and ( (a.invamt <> 0) or (a.payamt <> 0) )and (a.chkno = '') order by a.invdate"
As your can tell I am using the user.text and Pass.text in my query which will come from the web form.
I know this is wrong but how else can I do it?
Thanks
View 3 Replies
View Related
Feb 19, 2005
To: All,
well here's a problem that I encountered, i got a textbox that is used to store the Date of Birth of a user. So when user keys in something, it is store as a string. However i wish to convert it into a datetime so that i can store it into my database. Anybody know of a way to help? i've tried countless methods but doesn't seem to work. Please give me a hand guys.... Thanks
From: iaciz
View 2 Replies
View Related
Aug 20, 2005
Hi there
I want to give dynamic input to the t-sql code in sql analyzer.how do i do that....
Like adding 2 numbers
declare @a integer,@b integer,@c integer
--I dont want to give like this
--set @a = 5
--set @b = 10
set @c = @a + @b
can anyone help me with this
regards
Vic
Vicky
View 3 Replies
View Related
May 24, 2007
Hi,
I am trying to make a simple stored procedure which I want to take input on every run from end user:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @sku varchar(20)
insert into skua (sku,SumOfQtyNum)
(select sku,sumofqtynum from sku where @sku = sku.sku)
it is working but not asking parameter values
(I want to use passthrough query after that in Access)
thanks
AA
View 2 Replies
View Related
Jul 23, 2005
Hello. I'm using SQL query analyzer to run some queries against anSQL2000 DB. Unfortunately, my previous SQL experience is some OralceSQL I took in school.To put it simply, I'm trying to write a query to pull records thatmatch a variable input. In Oracle, I'd use a substitution variablelike this...SELECT * FROM jobs WHERE jobid = &job_id_to_query;Is this possible under SQL2000? Is this only a feature of SQL*Plus?TIARich
View 8 Replies
View Related
Nov 30, 2006
I m new to SQL Server. Before this I was using access. In access one can have user inputs easily .. for example
where receivedate=[enter date]
how I can get userinputs in SQL server Query....
Forgive me if it is a dumb question
View 6 Replies
View Related
Feb 14, 2007
I want to pull dates from my database that are between to set dates i have written a query that does this which looks like this:
"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('12/02/2007', 2) + '/' + SUBSTRING('12/02/2007', 4, 2) + '/' + RIGHT ('12/02/2007', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('14/02/2007', 2) + '/' + SUBSTRING('14/02/2007', 4, 2) + '/' + RIGHT ('14/02/2007', 4)))"
This works when the dates are included in the statement but when i try and use parameters to pull them in like this:
"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('@Choice1', 2) + '/' + SUBSTRING('@Choice1', 4, 2) + '/' + RIGHT ('@Choice1', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('@Choice2', 2) + '/' + SUBSTRING('@Choice2', 4, 2) + '/' + RIGHT ('@Choice2', 4)))"
I then recieve the following error message why is this?
ERROR -------------------> Syntax error converting datetime from character string.
Any Help would be greatly appreciated thanks
View 2 Replies
View Related
Jul 15, 2004
Hi folks, i am lookin for an option in the QA to take user input while executing a query. just like we do in SQLPLUS:
SELECT * FROM dept WHERE deptno=$abc
here it would ask me for the deptno. I guess it's a very handy feature and we could test our variables and calculations withing the procedures etc.
Is there a way, or any substitute?
Howdy!
View 14 Replies
View Related
May 3, 2008
I need to convert user's input to SQL statement.
How to code to chage user's input:123, 234 to '123','234'
View 4 Replies
View Related
Jun 2, 2008
Hi I have a table(tblA) as follows
Col1--------------col2---------col3-----col4
London------------1131---------299------Barking
Didicot-----------3451---------429------Dansdon
Barking/ASton-----1131---------345------Singleton
Vander/ADon/cam---3907---------299------derby
Null or Blank ---1131---------423------Addington
Expecting the Data should display look like below
London------------1131---------299------Barking
Didicot-----------3451---------429------Dansdon
Barking/ASton-----1131---------345------Singleton
Vander/ADon/cam---3907---------299------derby
But when user change the col2 value (1131) to 113999, this should be changed to all values where col2 is 1131. Please Help
View 2 Replies
View Related
Nov 20, 2014
I dont know how to get the newest input for each user from one single table.
Should be a very simple task but i cant work it out.
The table looks like this:
ID (A_I), userID, ip, date(timestamp)
Here is a SQL Fiddle Link with some data also: [URL] ....
I have tried a lot querys like this one:
SELECT userID, ip FROM userips GROUP BY userID ORDER BY ID DESC
But this one does not give me the latest ip which was entered by a user.
View 2 Replies
View Related
Jan 11, 2006
I would like to setup a query where the user is firstly prompted to input the information e.g. if they are trying to locate a particular person from a telephone database they would be prompted to enter the surname for that person.
View 4 Replies
View Related
Nov 30, 2007
Hello,
I'm trying to do something simple that has turned out to be a frustrating problem. I have a field in my report that needs to be populated by user input. Based on this user input, I will use the field in a calculation. The simple solution to me, was to create a custom assembly that has a function that uses Console.Write("Input Data") and Console.readline() to assign the input to a string, and then return that string to the report. This works fine in a test console application, but when I copy and paste the code to my custom assembly, it returns #Error to the report. I've debugged, and found that when I hit F10 on Console.Write("test"); it skips right over it, and nothing happens. It also skips over Console.readline(); with nothing happening. This makes me unable to take the user input and set the textbox equal to that value.
Am I doing this completely wrong? Is there something I'm missing, or just not understanding correcly? It seems like reporting services has to have this option. I'd really appreciate some advice. Thanks!
-Paul
View 3 Replies
View Related
May 20, 2008
Hello Everybody,
I am sure this has been asked a thousand times before in a thousand different ways, but here goes...
I am in need of the ability to prompt users for a From Date and To Date to insert into a WHERE clause in SQL queries. What is the easiest least painful way of accomplishing this? A great many of our customers utilized SQL Server Express or MSDE, so they do not have the full gambit of SQL tools handy. Believe me when I say that I have searched high and low across the internet for this solution. I was hoping I might be able to accomplish this through VB Script or J Script. Or, anything reasonable.
Thanks in advance for your time and patience,
Rocky Mountain Tech
View 1 Replies
View Related
