How to fetch records of specific rows from data table?

Question

I am using C# and SQL server. I used top query but it is giving me only top result from data table. I want results of rows like 20-40 or 30-100 by entering this value in textbox.

CREATE TABLE [dbo].[newpatient] (
[id]          INT           IDENTITY (1, 1) NOT NULL,
[serialno]    VARCHAR (MAX) NULL,
[patientname] VARCHAR (100) CONSTRAINT [DF__newpatien__patie__1273C1CD] DEFAULT ('') NULL,
[age]         INT           CONSTRAINT [DF__newpatient__age__1367E606] DEFAULT ((0)) NULL,
[address]     VARCHAR (100) CONSTRAINT [DF__newpatien__addre__145C0A3F] DEFAULT ('') NULL,
[symptoms]    VARCHAR (MAX) CONSTRAINT [DF__newpatien__sympt__15502E78] DEFAULT ('') NULL,
[medicine]    VARCHAR (MAX) CONSTRAINT [DF__newpatien__medic__164452B1] DEFAULT ('') NULL,
[bookingdate] DATETIME      NULL,
[alloteddate] DATETIME      NULL,
[village]     VARCHAR (MAX) CONSTRAINT [DF__newpatien__villa__173876EA] DEFAULT ('') NULL,
[thana]       VARCHAR (MAX) CONSTRAINT [DF__newpatien__thana__182C9B23] DEFAULT ('') NULL,
[district]    VARCHAR (MAX) CONSTRAINT [DF__newpatien__distr__1920BF5C] DEFAULT ('') NULL,
[state]       VARCHAR (MAX) CONSTRAINT [DF__newpatien__state__1A14E395] DEFAULT ('') NULL,
[isvalid]     BIT           CONSTRAINT [DF__newpatien__isval__1B0907CE] DEFAULT ('') NULL,
CONSTRAINT [pk_id_newpatient] PRIMARY KEY CLUSTERED ([id] ASC)

);

try
   {
     SuperClass sc = new SuperClass();
     Cursor = Cursors.WaitCursor;
     timer1.Enabled = true;
     rptPatients rpt = new rptPatients();// created report
     SqlCommand MyCommand = new SqlCommand();
     SqlDataAdapter myDA = new SqlDataAdapter();
     DB_DOCTORDataSet myDS = new DB_DOCTORDataSet();//created dataset
     SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=DB_DOCTOR;Integrated Security=True;Asynchronous Processing=True");
     MyCommand.Connection = con;
     MyCommand.CommandText = "select top '" + textBox1.Text + "' * from NewPatient";
     MyCommand.CommandType = CommandType.Text;
     myDA.SelectCommand = MyCommand;
     myDA.Fill(myDS, "NewPatient");
     rpt.SetDataSource(myDS);
     crystalReportViewer1.ReportSource = rpt;
   }
catch (Exception ex)
   {
     MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
   }

@RahulSharma , Can you please show the structure of your table ??? — Sohail
– Sohail, Commented Dec 14, 2013 at 10:20
Try using CTE this is already similar to this stackoverflow.com/questions/3747186/… — Jade
– Jade, Commented Dec 14, 2013 at 10:39
you may find your answer from this URL: stackoverflow.com/questions/17259716/… — fasadat
– fasadat, Commented Dec 14, 2013 at 10:56

Daniel B · Accepted Answer · 2013-12-14 10:47:09Z

1

You cannot use the TOP clause select an interval such as 20 to 40. The simplest approach is to select the TOP 40 and then discard the first 19 rows on the client.

Slightly less simply is to change the query to something like

WITH T AS
(
    SELECT TOP 40 NP.*, row_number() OVER (ORDER BY id) AS RN from NewPatient NP Order by xx
)
SELECT * from T where RN>=20

edited Dec 14, 2013 at 10:47

answered Dec 14, 2013 at 10:41

Daniel B

8075 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rahul Sharma Over a year ago

how can i select interval ?

Rahul Sharma Over a year ago

can u help me to use above query using C# ?

Daniel B Over a year ago

Just replace the current query text ´"select top '" + textBox1.Text + "' * from NewPatient"´ with the above query text. You need to substitute "20" and "40" with parameters.

Daniel B Over a year ago

Take away the single quotes around 40, add a comma after 40.

Al3x · Accepted Answer · 2013-12-14 23:32:48Z

0

If the data in the DataBase is ordered by the ID, why not use:

MyCommand.CommandText = "select * from NewPatient where id between  " + textBox1.Text + " and " + textBox2.Text + " from NewPatient";

Where textBox1 and textBox2 holds the range of records you want to get.

answered Dec 14, 2013 at 23:32

Al3x

1252 silver badges9 bronze badges

6 Comments

Tony Over a year ago

What about when there are gaps in the ID range? Using BETWEEN will not always return a full "page" of rows in that case.

Al3x Over a year ago

If they are ordered by the ID and the range goes from 10 to 30, the gaps you talk about would be like "11,12,15,16,22,27"? In that case you'll still get all those records because they are inside the between range.

Tony Over a year ago

When selecting pages of rows you always want 10 rows, irrespective of the ID. Using BETWEEN to get the rows 10-20 from the example list "11,12,15,16,22,27" would result in only 4 rows being returned, not 10.

Al3x Over a year ago

I see what you mean but if you specify a range and in that range the aren't 10 items, you can't magically get 10. You should specify your criteria to select items. For example, you could want the next 10 items starting from a fixed id: MyCommand.CommandText = "select top(10) from NewPatient where id >= " + textBox1.Text + " from NewPatient"; Here textBox1.Text holds the lowerest ID you want to get, and the top(10) only returns you 10 items. You could add "order by desc" or "asc" if the entries are not in order.

Tony Over a year ago

"if you specify a range and in that range the aren't 10 items, you can't magically get 10" True, but your solution will never return a full page of rows if there are gaps in the ID range, even if there are hundreds of rows. To get a full page of rows you can use ROW_NUMBER to sequentially number them and then select 10; see the accepted answer to this question.

|

Collectives™ on Stack Overflow

How to fetch records of specific rows from data table?

2 Answers 2

4 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related