How to create sql for retrieving 10 records per time in .NET

I want to create sql for retrieving 10 records per time but need to know the total result matches.
And I have previous and next button for retrieving previous or next 10 records.
ThanksAs a great man once said: Huh?

I'm sure that your posting made sense to you, but I don't even have a clue what you meant. Could you try to repost and fill in a few of the details that I'm missing? I'd like to help you if I could, but at the moment I don't understand enough of what you want to have a chance!

-PatP|||Hi,

as Pat said, this isn't very clear and you really should repost. I just want to comment on what you may need to consider mentioning.

Almost all databases are used by multiple users at the same time. Selecting the 10 first records in a particular order makes perfect sense, as does selecting record 11 to 20 of that same recordset. But selecting 10 and then (at a later time) selecting "the ten next" doesn't have any clear meaning since the data may well have changed in the meantime. So perhaps it's a good idea to clarify how you want your system to deal with the different change scenarios: records may have been added, removed or updated, or any mix of the three between the two queries.

One way to "page in the database":
Initialize:
- establish a single orderby column for your query (if you have several columns, combine them into one column yielding equivalent sort order)
- select the top 10 records
- persist the value of the orderby column for the last record. In ASP.NET, use ViewState for this.

Each round trip:
- use the value of the last record in the previous result set to select the next ten:
SELECT TOP 10 ... WHERE ... AND ordCol > @.lastValue ORDER BY ordCol
- persist the last value again

Of course, if you require to page the other way (previous page) you must also persist the value of the first record. Furthermore, since there's no BOTTOM keyword to mirror TOP, you must use reverse sort order (otherwise, if you did SELECT TOP 10 ... WHERE ordCol < @.firstVal, the first page would be returned even if you tried to get the previous one from, say, page 5). Since you still want to display in-order, fix this by selecting into a temp table and then select in-order from that one:

SELECT TOP 10 INTO #page ... WHERE ... AND ordCol < @.firstVal ORDER BY ordCol DESC
SELECT * FROM #page ORDER BY ordCol

I hope this helps!

Dag|||Or you could just set-up paging in a datagrid...|||Huh?

Just a guess...

You want to do paging

http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx

Make sure to say hi to Jeff for me
__________________|||I meant that you could use a .NET DataGrid control that allows you to view a recordset one page at a time. You can define how many records you want displayed on each page. Of course this deals with paging at the Application level - don't know if that's what he wants but just thought I'd mention it.

I don't have a clue about paging at the SQL Server level...:-)|||Sorry for unclear question. Yes, what i want to do is paging.
Brett: The URL http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx is unavailable
mmcdonald: Thanks for your suggestion but the user don't want the result be shown in datagrid :(

how to create report by month from vb

hi all, i m using this code to display report of month selected from combo1
it shows records but only single of current month and moreover if there is no record of a month it shows previous result.
plz help me soon
bye

///////////
mon = Combo1.Text
cmd.CommandText = "Select * from travel"
rs2.Open cmd, , adOpenStatic, adLockReadOnly
Do While Not rs2.EOF
mo = Format(rs2.Fields("report_date"), "mmm")

If mo = mon Then

crxreport.RecordSelectionFormula = "{travel.travel_id} = " & rs2.Fields("travel_id")
CRViewer1.ReportSource = crxreport

End If

rs2.MoveNext

Loop
rs2.Close

CRViewer1.Zoom (100)
CRViewer1.ViewReportu cn do it easily by inserting the required field of that month into a temp table.

& then link the temp table to ur report

i think it will do|||thanks for response,
sorry i not understand what u mean by temp table
and how i can pass month name from combo box in vb to crystal report for displaying record of that month

waiting for reply
bye|||insert a combobox containg all the month in the form where u u load ur report .
Then make a query(for ur defined month from the combobox) 2 select the data from the original .insert these data 2 a temp table which is linked 2 the crystal report.

Then load the report.

bye.|||thanks
please tell me what is problem in my code given above
or give some sample code
to pass month name from vb form
to report
thankssql

How to create and schedule a job using linked servers?

There are 2 sql servers : A and C.
I have to grab data on server A and update records with this data on server C. It has to run every night, so it has to be scheduled as a job.
I created linked server on server A and ran query select... It works.
I can create Update query.
What are my steps next? I read how to create a job, but not understand how to incorporate the query into it. I am totaly unexpirenced user, please explain step by step or give me a link to a good explanation. I also do not understand how and whether DTS should be used in all this.
Appreciate any help.

Ann2wrap ur sql query in SP,create and call that SP from job steps.
From BOL,
How to create a job (Enterprise Manager)

To create a job
Expand a server group, and then expand a server.
Expand Management, and then expand SQL Server Agent.
Right-click Jobs, and then click New Job.
In the Name box, enter a name for the job.
To create a Transact-SQL job step
Create a new job or right-click an existing job, and then click Properties. For more information on creating a job, see Creating Jobs (javascript:hhobj_301.Click()).
In the Job Properties dialog box, click the Steps tab, and then click New.
In the Step name box, enter a job step name.
In the Type list, click Transact-SQL Script (TSQL).
In the Database list, click a database for this job step to use.
In the Command box, enter the Transact-SQL command batch(es), or click Open to select a Transact-SQL file to use as the command.
Click Parse to check your syntax. The message "Parse succeeded" is displayed when your syntax is correct. If an error is found, correct the syntax before continuing.|||I created a stored procedure for the job and not sure what Type and Command do I use in this case? EXEC MyProcedure in command window eith type TSQL does not work. The procedur is working fine when I run this command in Query Analyser:EXEC MyProcedure .

Thank you.
Ann

How to create a view to search records between 1pm yesterday and 7

How to create view to search records between 1:00pm yesterday an 7:00am toda
y?Why do you need a view to do that? I assume this is the WHERE clause you are
looking for?
SELECT * FROM YourTableOrView
WHERE YourDT >=
CONVERT(DATETIME,CONVERT(CHAR(8),DATEADD
(dd,-1,GETDATE()),112) + ' ' +
'13:00:00')
AND YourDT < CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE
(),112) + ' ' +
'07:00:00')
Andrew J. Kelly SQL MVP
"View" <View@.discussions.microsoft.com> wrote in message
news:AD50EE4D-9335-4831-8738-C700712C28FF@.microsoft.com...
> How to create view to search records between 1:00pm yesterday an 7:00am
> today?

How to create a No Records Found message

We have a report that we use to verify that two our systems are in
sync. The only time anyone needs to act on the report would be if
any
results were returned. I'm looking for a best practice that would
allow me to insert a "No Records Found" message if the results were
negative rather than simply just displaying a blank report.
Thanks,On Dec 5, 4:38 pm, mbailey256 <mbaileyst...@.yahoo.com> wrote:
> We have a report that we use to verify that two our systems are in
> sync. The only time anyone needs to act on the report would be if
> any
> results were returned. I'm looking for a best practice that would
> allow me to insert a "No Records Found" message if the results were
> negative rather than simply just displaying a blank report.
> Thanks,
As far as I can tell, only Charts and Subreport objects have a
"NoRows" Property that is used to display text when their associated
DataSets have no rows.
But, since a No Records Found is equivalent to having no rows of data,
you can explicitly try counting the rows. Try doing a TextBox at the
very top of your report with a Visible Expression of:
= ( Count( Fields!OneThatWeKnowIsNotNull.Value, "DataSet" ) = 0
When the Count is zero, the textbox is Visible, and anything in the
Text Expression will be seen.
-- Scott|||On Dec 5, 4:10 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 5, 4:38 pm, mbailey256 <mbaileyst...@.yahoo.com> wrote:
> > We have a report that we use to verify that two our systems are in
> > sync. The only time anyone needs to act on the report would be if
> > any
> > results were returned. I'm looking for a best practice that would
> > allow me to insert a "No Records Found" message if the results were
> > negative rather than simply just displaying a blank report.
> > Thanks,
> As far as I can tell, only Charts and Subreport objects have a
> "NoRows" Property that is used to display text when their associated
> DataSets have no rows.
> But, since a No Records Found is equivalent to having no rows of data,
> you can explicitly try counting the rows. Try doing a TextBox at the
> very top of your report with a Visible Expression of:
> = ( Count( Fields!OneThatWeKnowIsNotNull.Value, "DataSet" ) = 0
> When the Count is zero, the textbox is Visible, and anything in the
> Text Expression will be seen.
> -- Scott
Thanks Scott. I did find the NoRow property for the regular table,
but for my report with multiple tables, your solution fit perfectly!!!

how to create a dynamic view?

Is there a way to create a dynamic view, that will read the current month and select records in table for the last 12 months?

thanks

Something like this should work for you: This will alway provide the data for the previous twelve months up to the moment of execution.

Code Snippet

CREATE TABLE MyTable
( RowID int IDENTITY,
DateCol datetime,
)
GO

CREATE VIEW MyView
AS
SELECT
RowID,
DateCol
FROM MyTable
WHERE DateCol >= ( dateadd( month, -12, getdate() ) )
GO

SET NOCOUNT ON

INSERT INTO MyTable VALUES ( '02/15/2006' )
INSERT INTO MyTable VALUES ( '03/01/2006' )
INSERT INTO MyTable VALUES ( '04/01/2006' )
INSERT INTO MyTable VALUES ( '03/15/2006' )
INSERT INTO MyTable VALUES ( '02/28/2006' )
INSERT INTO MyTable VALUES ( '02/15/2007' )
INSERT INTO MyTable VALUES ( '03/01/2007' )
INSERT INTO MyTable VALUES ( '04/01/2007' )
INSERT INTO MyTable VALUES ( '03/15/2007' )
INSERT INTO MyTable VALUES ( '02/28/2007' )

SELECT *
FROM MyView
ORDER BY DateCol DESC

DROP TABLE MyTable
DROP VIEW MyView

|||You could do something along the lines of the following:

CREATE VIEW YourLastTwelve
AS
SELECT *
FROM YourTable
WHERE YourDateField < CONVERT(varchar(25),
DATEADD(m, 1, GetDate() + 1 - DAY(GetDate())), 101)
AND YourDateField >= CONVERT(varchar(25), DATEADD(yy, -1 ,
DATEADD(m, 1, GetDate() + 1 - DAY(GetDate()))), 101)

-Sue

how to count with restrictions

Hello Gents,
need your help to write a formula for counting records with defined calue in the fileld, e.g. how many records with value "123456" in field {table.field}?
thank you very much for your help!U can.

Create a running total field. Add the field which you want to count. Give "Count" as your type of summary.

In Evaluate option,
select "Use a Formula" option. click X-2 box. Write your formula. Never reset this .

u can get the count what u want.

Hope this work.|||thanks a lot, but which will be formula in my case?|||Just simply enter

{table.fieldname} = "123456"|||thanks for prompt reply, but unfortunatelly it always returns zero...|||If it is string field, use trim function, records may be stored with spaces.

Otherwise it should work if the table contains records for that value|||Doesnt help... When i use simply count({fieldname}) - that returns total fine, but with this restriction it doesnt work...|||Sorry for asking this,

Do you have any records which meet ur condition?

I used lots of running total sums with various conditons. working fine.|||Sure, i have :-)
Actually, this field (string type) is a set of 5 kind of different words (total is ~700). I would like to know how many i have words "123456" for example.|||In addition: when i use running total with evaluation "For each record" - it returns 1. When i set type of summary to maximum - then returns only value, not the sum.|||Make sure

i) Included the exact field name in "Fields to Summarize" box
ii) Type of Summary is count

iii) Evaluate:

Check "Use a formula option" and
write the formula in formula workshop (by clicking X-2)
trim({table.fieldname}) = "123456"

iv) Reset:

check "Never" option|||Yes, all of these conditions is true.|||Ok, good

Now where have u placed your running total field?
(in which section)|||In ReportHeader.|||that's the problem.

It's running total. So will be calculated while printing records.

So place in Report Footer. So u'll get the count.|||Thats works! Thank you very much!!!

How to count the records in a datatable

I have the following:

Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapter
Dim dF As Data.DataTable = ResourceAdapter.GetDataByDistinct
Dim numRows = dF.Rows.Count
Dim dS As Data.DataTable = ResourceAdapter.CountQuery
Dim sumRows = dS.Rows.Count
DataList1.DataSource = dF
DataList1.DataBind()
LblCount.Text = numRows
LblSum.Text = sumRows

numRows is the number of records in a particular category. CountQuery is a method that returns the total number of records in all the categories. I don't know how to get the result of that query, the code line in bold isn't right. How do I get this number?

Diane

ds.Rows[0][0].ToString()

If you are getting a single count value, you can use the above statement. Make sure you validate nulls, else an exception will be raised

Thanks

|||

Thank you.

Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapter
Dim dF As Data.DataTable = ResourceAdapter.GetDataByDistinct
Dim numRows = dF.Rows.Count
Dim dS As Data.DataTable = ResourceAdapter.CountQuery
Dim sumRows = ds.Rows[0][0].ToString()

But before I can assign the count to sumRows, don't I have to fix the line above it (in bold)? That line gives me the compiler error

Value of type 'System.Nullable(Of Integer)' cannot be converted to 'System.Data.DataTable'.

Diane

|||

Are you sure ResourceAdapter.CountQuery returns a datatable? If so check if you have any rows available or not first

If (ds.Rows.Count > 0)
Dim sumRows = ds.Rows(0)(0).ToString() /// i am c# code, not sure of how you refer a two dimensional arrary
End If

Thanks

|||

I'm not sure what it returns. The query is one that's designed to return only one row, the number of records in the table. I'm not sure how to get that.

Diane

|||

Looking at the error you are getting (ie: Value of type 'System.Nullable(Of Integer)' cannot be converted to 'System.Data.DataTable'. )

I would say you need to have

Dim sumRows as Int

sumRows = ResourceAdapter.CountQuery

How To Count how many ROWS are returned in SQL Data Reader

My code keeps reading for another row when none are present....

There's are only unique records in the dB so far, but dupes are possible

While reader.has rows

reader.read

if value not db.null

get values

end if

end while

The program gets the row and reads it. After processing the values, it goes to thru the While and reads the next row (none), it then crashes on if value not db.null... because the value is null or no record. The dB has Values Required option on - no empty fields allowed.

Is there a way to count the ROWS returned?

Thanks.

That depends on your coding language, which one are you using ? For example the .NET Framework has the read() method which returns false if no record can be read anymore, leading to the following code:

while dr.Read()
{}

and to the fact that you won′t get an error if no rows is read anymore.

In my good old ASP ages there was something similar like rs.EOF or something, I guess there has to be be something in your coding language too.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I'm using VB.

But I'm using the WHILE there are ROWS, READ, so it shouldn't read if no more rows are present. Only ONE ROW is returned, so the WHILE statement should return FALSE on the second run of the loop and exit the LOOP because no more ROWS are present.

Moverover, it continued to READ, so perhaps the framework (NET) detected another row (empty) and reads it (while rows are present, read). Howbeit, this ROW is empty so an error is generated at the if not null.db line - but even so, it should NOT generate an error because NOT db.Null should detect that there is a Null Value and return FALSE and exit the loop.

How to copy record from a table in a server to a diffrent server.

Hi all,

I have server1 and server2 having the same tables. I wanna copy tbl1 records in server1 to tbl1 table in server2. How can I accomplish this task?

Thank you in advance,

Use a query. This assumes you have permissions to both servers.

Code Snippet

INSERT INTO tbl1
SELECT
Col1,
Col2,
Col3,
etc
FROM Server2.MyDatabase.dbo.tbl1
WHERE {criteria}

how to copy detail records to another header

Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11).

So far I have two stored procedures:
1) sp_copyDetailsOne
/*Create a recordset of the desired items to be copied*/
CREATE PROCEDURE sp_copyDetailsOne @.invoiceIdFrom INT, @.outCrsr CURSOR VARYING OUTPUT AS
SELECT itemId, itemPrice, itemDescription, itemQuantity
FROM tblSalesItems
WHERE (invoiceId = @.invoiceIdFrom)
OPEN @.outCrsr

2) sp_copyDetailsTwo
CREATE PROCEDURE sp_copyDetailsTwo @.invoiceIdFrom INT, @.invoiceIdTo INT
/*Allocate a cursor variable*/
DECLARE @.crsrVar CURSOR

/*Execute sp_copyDetailsOne to instantiate @.crsrVar*/
EXEC sp_copyDetailsOne @.invoiceIdFrom, @.outCrsr = @.crsrVar OUTPUT

/*Loop through the recordset and insert a new record using the new invoiceId*/
FETCH NEXT FROM @.crsrVar
WHILE @.@.FETCH_STATUS = 0
BEGIN

/*Run the insert here*/
INSERT INTO tblSalesItems
(invoiceId, itemId, itemPrice, itemDescription, itemQuantity)
VALUES (@.invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30)

/*Fetch next record from cursor*/
FETCH NEXT FROM @.crsrVar
END

CLOSE @.crsrVar
DEALLOCATE @.crsrVar

My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome.

Thanksis this a one time operation? If so, won't insert (...) (select ... +1) do?|||Your FETCH just displays the data accessed by the cursor. You need to FETCH...INTO a list of variables that would hold the values that you'll use in VALUES clause of your INSERT (@.itemPrice, @.itemDescription, @.itemQuantity)