Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK] AS DECLARE @sql NVARCHAR(max) DECLARE @DB VARCHAR(max) DECLARE databasecursor CURSOR FOR
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
I have an interface that allows the user to filter the data to be returned.
e.g. if the user selects @SeniorManager then the query should return all data related to this.
So if the user selects @SeniorManager and @Auditor then the query should return all data matching both @SeniorManager AND @Auditor.
FundID and ClientID are the ID's that link each table.
Am I correct in using UNION ALL below?? thanks.
Code Block SELECT SeniorMgr, null AS Auditor, null AS Legal FROM FundClient f INNER JOIN FundPortfolio p ON p.ClientID = f.ClientID INNER JOIN Staff s ON (s.ClientID = p.ClientID) AND (s.FundID = p.PortfolioID) AND (s.ClientID = f.ClientID) WHERE s.ClientID = @ClientID AND s.FundID = @FundID AND (@SeniorManager = null Or SeniorMgr = @SeniorManager) UNION ALL
SELECT null AS SeniorMgr, fa.Auditor, null AS Legal FROM FundClient f INNER JOIN FundPortfolio p ON p.ClientID = f.ClientID INNER JOIN (Auditor a INNER JOIN FundAuditor fa ON fa.AuditorID = a.Auditor) ON (a.ClientID = p.ClientID) AND (a.FundID = p.PortfolioID) AND (a.ClientID = f.ClientID) WHERE a.Auditor = @Auditor AND a.ClientID = @ClientID AND a.FundID = @FundID
UNION ALL
SELECT null AS SeniorMgr, null AS Auditor, fl.Legal FROM FundClient f INNER JOIN FundPortfolio p ON p.ClientID = f.ClientID INNER JOIN (Legal l INNER JOIN LegalCounsel fl ON fl.LegalID = l.Legal) ON (l.ClientID = p.ClientID) AND (l.FundID = p.PortfolioID) AND (l.ClientID = f.ClientID) WHERE l.LegalC = @Legal AND l.ClientID = @ClientID AND l.FundID = @FundID
We are working on an application which aggregates news articles from various sources. The problem is, when querying the database, is that there are a lot of double results because some of the sources get their news from the same press agency. To clarify things a bit here's a link to an example: http://84.244.146.27/Zoekresultaten.aspx?SearchQuery=Amsterdam As you can see, there are a lot of the same results. What we want is to filter out the double results irrespective of the source. In other words; when the query encounters the first result, display that and leave out the other same results. I've tried filtering them out by using DISTINCT on the 'news header' / results, but that does not seem to work. Any ideas on how to get this done? Any help is greatly appreciated!
I don't know if anyone has encountered this before but here goes:
I've a select clause below: result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"
queryLocalName is an input field that allows the user to search for non-English characters in the database.
What I'm wondering is what kind of effect is the N in the where clause is having?
I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.
I have a query that calculates sales by sales person, but it displays horizontally across my query window. Is their a way in SQL Server to have the data display vertically down the window instead?
This is my current query
Code:
Select count(case when salesman Like 'Geo%' then id else null end) As [George] ,count(case when salesman Like 'Li%' then id else null end) As [Lisa] ,count(case when salesman Like 'Jor%' then id else null end) As [Jorge] ,count(case when salesman Like 'Ri%' then id else null end) As [Richard] ,count(case when salesman Like 'Geo%' then id else null end)+count(case when salesman Like 'Li%' then id else null end) As [Team 1 Sales] ,count(case when salesman Like 'Jor%' then id else null end)+count(case when salesman Like 'Ri%' then id else null end) As [Team 2 Sales] from sales.southeastregion
Which of course shows the results as such
George --- Lisa --- Jorge --- Richard --- Team 1 --- Team 2 100 50 10 90 150 100
And I want the data to be displayed like
George - 100 Lista - 50 Jorge - 10 Richard - 90 Team 1 - 150 Team 2 - 100
I have an issue with some data that has a leading ASCII char 160 (the "a" with the accent mark) but it shows in query results as a space.
... where customername like char(160) + '%'
returns 2 rows but shows the customer name with a leading space. How would I change the collation or do otherwise to get this character to display correctly in the results?
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says. Command(s) completed successfully. What I really need it to do is to display the results of the query. Does anyone know how to do this? declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
I want to create a stored procedure that returns a list of records froma table. But depending on a userID value given only certain recordswill be returned that they have access to.I think this might be hard to do in a single SELECT statement becausethe user might also belong to a group that might have permission, etc.Can you do something like this pseudo code in a T-SQL procedure?DECLARE cur CURSOR FOR SELECT * FROM myTableOPEN curFETCH NEXT FROM curWHILE @@FETCH_STATUS = 0BEGINif( accessGranted(curRecord.id) ){ addRecordToResultSet() }else { ommitRecordFromResultSet() }END
Hey all, I've been searching for a while and haven't really found an answer for what I'm trying to do. Here it all is and someone can tell me if I'm going in the wrong direction to solve this. I have a textbox set to a parameter of "SearchString" and a DropDownList with the SelectedValue paramter of "FilterValue". The parameters work, I've tested that. So here is the problem. I am trying to use the "FilterValue" to pick the field that the "SearchString" searches with a LIKE statement (e.g. SELECT * FROM dbo.Units WHERE (@FilterValue LIKE '%'+@SearchString+'%')) This, I've found out, doesn't work. I have this setup with a DataLayer.vb in my App_Code directory handling the SQL calls, while the normal CodeBehind is in the typical "Default.vb" page. I will post code later as I don't have access to it right now, but if anyone can explain how a filter like this would work I would appreciate it! Thanks,
Lets say you have to retrieve some SQL.Then you have to go through those records and delete some items, change some values, and even add columns and rows...based on logic that depends on the data you got.Thanks.
Hi, I made a table to display information from northwind database. It displays fine on IE5.0 but will only show the first line in netscape. Do you know what it may be???
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.
Here goes..
SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE FROM SAMPLE SAMPLE, SDIDATAITEM SDI WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND (SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') ) ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC
I am working on a project that displays images. Before you can get to the image, you have to select a category that the image may be in. After that, you select the sub categories. I am trying to display a count of the number of records that the subcategories contain. Here is an example:
The user can make a selection from the categories listed below:
Geographic Area Time Period Topic Record Type
If the user selects time period, he/she is taken to a list of subcategories. I would like to display the subcategories with a count of the number of records that will be displayed if it is selected. Listed below is an example of what this would look like:
Colonial (10) -----the number in parenthesis is the number of records that will be displayed if selected------ Gilded Age (12) Revolutionary (9) Progressive Era (22)
Is there a way to display the number in parenthesis using ASP.Net and SQL Server 2000? Any clues will be greatly appreciated.
I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test ( id int identity(1,1) primary key, ename varchar(10) )
insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg'
I need the output something similar as below
REG Reg Regsxysn psReg
I have tried out with full text indexing but i could'nt get the required output.
I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test ( id int identity(1,1) primary key, ename varchar(10) )
insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg'
I need the output something similar as below
REG Reg Regsxysn psReg
I have tried out with full text indexing but i could'nt get the required output.
I have a single column returned from a select statement. How can I have this returned as a vertical string? I looked into using PIVOT but my scenario seems too simple to use Pivot. I'm not requiring any aggregate functions or anything.
I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest; USE SpecTest GO
CREATE TABLE [dbo].[Spec1]( [Spec1ID] [int] IDENTITY(1,1) NOT NULL, [Channel] [int] NOT NULL,
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hi All, I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:
SELECT l.loanid, p.investorid, l.duedate, case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate, pc.interestrate FROM loan l inner join participation p on p.loanid = l.loanid inner join paymentchange pc on pc.loanid = l.loanid where p.investorid = '12345' and RateDueDate is not null order by l.loanid, pc.duedate
I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.
Please help. I'm completely new to SSIS, and I was hoping someone could point me in the right direction(s).
I am developing a winforms client app, and I need to be able to provide the user with the ability to import data in CSV format into our application's database.
I'd like to use SSIS if possible, as long as what I am trying to do isn't near impossible.
I'm thinking of a UI where the user can specify a flat file (CSV) to upload and be processed by an SSIS package on a remote database server.
This package will be responsible for validating the CSV file and inserting data into the database as appropriate.
Is there a way I can:
launch an SSIS package remotely, from a winforms app on the client machine that is not running SQL server (preferably asynchronously) -- and pass it some parameters, including the import file itself (optionally) provide progress feedback to the client pc to let them know it is being processed display a nice SSRS report on the client upon completion that tells them exactly what the success/failure of the import was (how many rows processed, which ones failed and why, etc)
Any helpful examples, links, etc would be most appreciated.
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners num1, num2, num3, num4, num5, num6 33 52 47 23 17 28 ... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
Hi, I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number(). I would like to give my users to option of removing individual people from this list but cannot find a way to do this. I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message. Is there any way to do this? I am still new to stored procedures so any advice would be helpful. Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
