Removing Individual Results From A Paged Set Of Results.

Oct 19, 2007

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

 

View 3 Replies


ADVERTISEMENT

Paged Results From SQL Query?

Jan 25, 2006

I have been searching this topic on and for quite some time and can't seem to find a decent answer. Is it feasible to do your paging strictly from a SQL query? Pass the query the pagesize, what page to return and what to sort by?

View 4 Replies View Related

Please Help To Assign Multiple Results Into Individual Variables, Stored Procedure

Apr 12, 2008

I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line
(@Tester1       integer,@Tester2        integer,@Tester3    integer,@Tester4    integer,@Tester5    integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen:
@Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.

View 2 Replies View Related

Removing Duplicate Results With One Column Different?

Aug 9, 2012

I am trying to get people from my table that have closed accounts. However, in my table many people have more than one account. They will have multiple closed accounts and some active accounts. I need to get the people with only closed accounts.

Values in the table

Code:
name surname status Closed Number
----------- --------- ----------- ------------- ----------------------------
Jeff Burns closed 2012/01/01 142
Tina Drewmor closed 2008/05/20 546
Jeff Burns active 1900/01/01 354
Kyle Higgin active 1900/01/01 851
Tina Drewmor closed 2009/04/14 154

The query I am using so far is:

Code:
select
d.name,
d.surname,
s.status,
s.closed,
s.number
from
d d inner join s s on d.number = s.number
where
s.status = 'closed'

What I need to see in the results

Code:
name surname status Closed Number
----------- --------- ----------- ------------ -----------------------------
Tina Drewmor closed 2008/05/20 546
Tina Drewmor closed 2009/04/14 154

View 4 Replies View Related

Outputting Results - Removing Dash From Zip Codes

Sep 10, 2013

I'm trying to construct a query that takes results from a table (zip codes, essentially) and strips out the results so that it's just the first 4 digits (rather than the first 4 digits + '-.....'). Which function would allow me to do this in management studio?

View 3 Replies View Related

A Basic Question: Removing Duplicate Results From Max Function

Jun 10, 2006

Hi,Say I have a table Job with columns name, date, salary . I want to getthe name ,date and salary for the date when that person earned maximumsalary. I am using something likeSELECT X.name,X.date,X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);The problem is ; if a person earns maximum salary on two dates, both ofthe dates are printed. I just want to get any one of those two rows.I triedSELECT X.name,Min(X.date),X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);but it gives error.Can anybody please suggest a solution?Regards,Aamir

View 4 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

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! 

View 1 Replies View Related

SQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?

Mar 25, 2015

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?

View 4 Replies View Related

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.
Thanks,
James.

View 1 Replies View Related

Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT

May 14, 2008




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..

Thanks
dotnetdev1

View 4 Replies View Related

Mind-boggling Gridview Results! Different Results For Different Teams..

Jun 18, 2008

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

BrianAustinHope83432230.7187500.7352941.15625014612201221

GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519

MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218

RobBennettMelville83029240.8275860.8333331.55172411904102117

AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015

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)*** 

View 2 Replies View Related

PASTE SQL RESULTS INTO EXCEL - Funny Results

Jan 30, 2008

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.

View 6 Replies View Related

Sql 0 Results

Aug 9, 2006

Hi, i wanted to know if there is a way that we can know if Sqldatasource retrieved 0 results, i wanted that cause i want to make a condition that if 0 results retrieves a page with the text "No news"...Thanks in advance.

View 2 Replies View Related

SQL Qry To Get The Following Results.....

Jan 12, 2006

SQL Server 2000
i have made a table(EmployeeRole) consists of the following fields :



Appname
EmployeeID
RoleID
ProjectID

QMS
1353
1
BI0362

QMS
1353
3
BI0362

QMS
1379
1
BI0362

QMS
1379
13
BI0362and another table called employee



EmployeeID
FirstName
LastName

1353
Wissam
Zein

1379
Wassim
Zeinand another table called Role:



RoleID
RoleName

1
User

3
PD

13
TeamLeaderi need to make a query that gives the following results:



EmployeeID
EmpName
RoleID
RoleName

1353
Wissam Zein
3
PD

1379
Wassin Zein
13
TeamLeader
thank you for the help and for the time

View 5 Replies View Related

Results From SP

Jan 16, 2001

Can someone give an example on how to insert data into a table that I get from running a any System Stored Procedure.

Thank You,
John

View 2 Replies View Related

Results

May 25, 2006

I have some SP's I run once a month and each SP takes a few mins to run and when I batch em together in one shot, I hate sitting there waiting for them to finish
whats the easiest way to report back the status of the exec?

Ex:
Set NoCount ON
EXEC StatesUpdateZipTableUpdate 'AE'
EXEC StatesUpdateZipTableUpdate 'AK'
EXEC StatesUpdateZipTableUpdate 'AL'
EXEC StatesUpdateZipTableUpdate 'AP'
EXEC StatesUpdateZipTableUpdate 'AR'
EXEC StatesUpdateZipTableUpdate 'AZ'

How would I get it to report the results after each Exec?
In otherwords Id like to create a progress bar......

View 3 Replies View Related

Getting Results Between 10 And 20

Nov 10, 2006

Here is my query

SELECT ItemName,ItemDesc FROM CATAGORY

This query returns over 80 results but I only need the results between 10 and 20 how can I modify the query to do that?

View 7 Replies View Related

Help With Results

May 16, 2008

Hi everybody

I have two tables which are info and pubssubcribe. each record on the info table has a corresponding subscription on the pubssubscribe table. I need to extract those records on the pubssubscribe with the infid appearing more than once and having the pubid BETWEEN 19 AND 22 and count the records grouped on infid
couldn't get thru with the code below

Here's my sql code
SELECT info.infid, info.infname
FROM info INNER JOIN
pubssubscribe ON info.infid = pubssubscribe.pubinfid
WHERE (info.infcond IS NULL) AND (pubssubscribe.pubid BETWEEN 19 AND 22 AND pubssubscribe.pubid > 1) AND (info.infid > 1)
ORDER BY info.infid

thanks

View 2 Replies View Related

Results To .txt

Jun 16, 2008

Hi,

How could I export results into a text file? Have been copying and pasting results into excel, but have been getting memory errors, whcihw e are working on fixing. In the meanwhile, I'd like to send these results (about 200,000 rows)into a .txt file as I execute teh query.

I use ms sql 2005

Thanks.

View 1 Replies View Related

Top Ten Results

Feb 23, 2006

Hi can anyone help me maybe im just being thick but i cant for the life of me work out how to get the top ten results based on the highest number within a column

ie.

top2 would be

name1 4
name2 8
name3 102
name4 113


i want name3 and name4 to be returned

SIMPLE well it should be aRRRRGGG HELP!!!

i have tried using max but that only returns the highest
how i get the next highest etc i dont know

View 1 Replies View Related

1 Row Results Per ID

Aug 23, 2007

Hey guys, I have a small issue that I'm not sure how to solve. I have 2 tables that I'm working on, that has the UserID, LastName, FirstName, DocumentDesc. Each DocumentDesc has its own DocumentTypeID

1st table is called Person
Fields(UserID, LastName, FirstName)

2nd table is called Documents
Fields(UserID, DocumentTypeID, DocumentDesc)

The query that I have for this is the following:




Code Snippet
Select a.UserID, a.LastName, a.FirstName, b.DocumentDesc
From person a
Join documents b on
a.UserID = b.UserID
Where b.documenttypeid = '126d2beb-f7a1-4bf1-b9c0-dded37d3a6bc' OR
b.documenttypeid = '9087956e-1fb0-4f3d-ba33-ef31d79141af'
Order by LastName





The first DocumentTypeID is for RESUME and the Second one is for TRANSCRIPT




This is a sample from the query above


UserID LastName FirstName DocumentDesc
1 Smith Paul Resume
1 Smith Paul PhdStatistics
1 Smith Paul MS Applied Statistics
1 Smith Paul MS Operation Research
2 Jackson Jane Resume
2 Jackson Jane MS Information Systems

What I'm trying to do is get this in on one line like so:

UserID LastName FirstName DocumentDesc DocumentDesc DocumentDesc DocumentDesc
1 Smith Paul Resume PhD Statistics MS Applied Statistics MS OperResearch
2 Jackson Jane Resume MS InforSystems

Note: Not all names have the same amount of documents.

View 5 Replies View Related

Why Am I Getting 0 Results???

Jul 6, 2006

Very odd... my NOT IN is not working:

---------

create table tbl1 (

region varchar(10) NOT NULL,

district varchar(10) NOT NULL

)

INSERT INTO tbl1 VALUES('000','AB1')

INSERT INTO tbl1 VALUES('001','AB2')

INSERT INTO tbl1 VALUES('001','111')

INSERT INTO tbl1 VALUES('002','111')



create table tbl2 (

region char(5) NULL,

district char(7) NULL

)

INSERT INTO tbl2 VALUES('xxx','121')

INSERT INTO tbl2 VALUES('yyy','141')

------------

select * from tbl2 where CONVERT(varchar(10),LTRIM(RTRIM(district))) not in
(select distinct district from tbl1)

From the example, the results should give me everything from 'tbl2'. I'm converting on the fly, and it still doesn't work. Can anyone help?

Thanks.

- gshaf

View 5 Replies View Related

Get Only Single Row Results Per Id?

May 26, 2007

Hi, is it possible to make an sql query that has an Outer Join but return only one row of results max per id.
For example i have an Articles table, and a PicturesForArticles table.
The Articles table has an id field(aid), a title field(aTitle) and a content field(aContent).
And the PicturesForArticles table has an id field(pid), a PicPath filed and a field linking it to the articles table(aid)
Obviously the PicturesForArticles field stores pictures for the articles, and article can have a multiple number of pictures, or no pictures at all.
So i want to make a query that will return all of the Articles fields and a picture for each article. Even if the article has many pictures i only want to get a single row for each aid(Articles Id), and if there are no pictures for that article the picture fields will be null.
Is there any way to do this, to only return on row of results for each aid?
Thanks

View 4 Replies View Related

Using SQL Results In If Statement

Jul 11, 2007

 
Hello
How can i say this I would like my if statement to say:  if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters></asp:SqlDataSource>
any help would be appreciated

View 3 Replies View Related

How Do I Append A Row To SQL Results?

Aug 31, 2007

I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValueSo the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql. So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0. 

View 4 Replies View Related

Need Help In Combining Results ..

Oct 22, 2007

Hi all,
I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table  Table 2. How can I combine these two to get the third table, Table 3 ?   
SELECT     TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1
FROM         dbo.Orders
WHERE     (ShipVia = 1)
GROUP BY EmployeeID
ORDER BY EmployeeID
  
Table 1 Results 
EmployeeID   CountShipVia1




1

                    82


2

                    71


3

                    81


4

                    116


5

                    29


6

                    48


7

                    44


8

                    75


9

                    29                                                                        
SELECT     TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2
FROM         dbo.Orders
WHERE     (ShipVia = 2)
GROUP BY EmployeeID
ORDER BY EmployeeID   
Table 2 results 
EmployeeID   CountShipVia2




1

                    44


2

                    36


3

                    45


4

                    70


5

                    15


6

                    25


7

                    24


8

                    48


9

                    19      
Table 3 the desired result:  
EmployeeID    CountShipVia1     CountShipVia2




1

                         82                      44


2

                         71                      36


3

                         81                      45


4

                         116                    70


5

                         29                      15


6

                         48                      25


7

                         44                      24


8

                         75                      48


9

                         29                      19
 
 
thanksrobby 

View 5 Replies View Related

Displaying Results Of Map

Feb 16, 2008

I am trying to create a user permission system that is stored in a database.  What is the best table structure for accomplishing this?   How could I display the permissions in a grid?  Currently I have a users table and a permissions table.  I created a map between the two.  However, I don't see how this allows me to display a grid.  All my "columns" for permissions are actually rows from the permissions table. So ideally my grid would look something like this. User | P1 | P2 | P3 |A      | T   |  F  |  T  |B      | T   |  T  |  T  |Thanks for any help.  I am relatively new to SQL so please explain gently. 

View 2 Replies View Related

Returning No Results

May 19, 2008

I have the following stored procedure that is returning nothing can anyone please help?
 SELECT     job_id, line_num, cust_id, cust_po_id, product_desc, form_num, revision, flat_size, new_art_reprint, order_qty, po_recieved_date, ord_ent_date,                       customer_due_date, scheduled_ship_date, act_ship_date, act_ship_qty, ship_from, ship_to, price_per_m, misc_charges, commentsFROM         tblOrderWHERE     (cust_id = @Cust_Id) AND (po_recieved_date BETWEEN @Start AND @End)
 When I input parameters I make sure my start date is before the first po_recieved_date and the end date is after it yet it is returning nothing. I also made sure that I am putting the correct @Cust_Id

View 6 Replies View Related

Export Sql Results In XML

May 20, 2008

Hi,
Based on the "SQL Book Server Online" from MSSQL 2000 I wrote the following codes in order to export the search results in XML format.
se pubsselect  1 as tag, null as parent, stor_id as [store!1!stor_id], stor_name as [store!1!stor_name], null as [Order!2!Ord_Num], null as [Order!2!ord_date] from storesunion allselect 2 as tag, 1 as parent, sa.stor_id, null, sa.ord_num,  sa.ord_datefrom sales sajoin stores ston sa.stor_id = sa.stor_idorder by [store!1!stor_id], [Order!2!Ord_Num]for xml explicit
The problem is that I want to results to be something like:
<stores>   <store>      <stor_id>6380</stor_id>      <stor_name>Eric the Read Books </stor_name>      <order>         <ord_num>A2976</ord_num>         <ord_date>1994-09-14 00:00:00.000</ord_date>      </order>      <order>          <ord_num>722a</order_num>          <ord_date>1994-09-13 00:00:00.000</ord_date>       </order>   </store>   <store>      <stor_id>7066</stor_id>      <stor_name>Barnum's</stor_name>      <order>         <ord_num>6871</ord_num>         <ord_date>1993-05-24 00:00:00.000</ord_date>      </order>      <order>         <ord_num>QA7442.3</ord_num>         <ord_date>1994-09-13 00:00:00.000</ord_date>       </order>   </store></stores>
How can I get the results in this format?
And, also, how can I export them directly into an xml file and save it on the disk?
Thank you in advance for your answers.

View 3 Replies View Related

Sql Query Results #?

Apr 20, 2004

I was wondering if there was a keyword that would allow you to return the number of results from a query such as (this is fake just giving an example so that someone can give me the real answer)


select TotalReturned
from table
where field=""

View 7 Replies View Related

How To Get Results From A Storedprocedure

Jun 11, 2005

To all,
I looked at the MS-SQL pubs sample database and execute the example
stored procedure reptq2 and I got 17 results set back. Where can I find
an example using Visual Studio DataGrid or any means to get all these
results from this SP.

Thanks,


Frank

View 3 Replies View Related

Help In Retrieving Results

Oct 11, 2005

i have a table in MS SQL Server,i need to show two results at a time,and on click of a button nxt two results have to b shown,how do i accomplish this (i mean retrieving the results two at a time)thanx in advance

View 1 Replies View Related

Searching In Results In Sql

Nov 8, 2005

I want to search all of the employeeid #'s in the employee table for any mis-typed information.  so if they put a . in the string i want to return that employee's info.  as well as any letter a - z.  I need to do this in sql and return it to a dataset for my crystal report.  any ideas?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved