|
Hi all,
I have a table with 50 rows
I need to split the resoults of the select to pages.
page 1 will have the 1st 15 rows
page 2 will have the 2nd 15 rows
page 3 will have the 3rd 15 rows
page 4 will have the remaining 5 rows
is there a query that can make my life easy
somthing like
SPLIT_IT ( [page_num], [rows_in_page], SELECT * from myTable)
thanks
|
|
|
|
|
you can use a datagrid........or a grid like control...
yes it is possible to split your record in a stored procedure...
Tirtha
Do not go where the path may lead, go instead where there is no path and leave a trail.
Author: Ralph Waldo Emerson (1803-82), American writer, philosopher, poet, essayist
|
|
|
|
|
SqlDataAdapter.Fill(DataSet, Start, TotalRows, TableName)
"The callee (server [not server application]) is not available and disappeared; all connections are invalid.
The call did not execute," said Internet Explorer, when I tried to access a deceased [window] object using JavaScript.
::..:.:..:: KiRtAN GoR ::..:.:..::
|
|
|
|
|
Thank you for your answer
I think SqlDataAdapter is in use by C# or VB
but I'm using Visual Studio to create reports,
so I need a query to do that.
I thought about somthing like that
DECLARE @page_num int<br />
DECLARE @rows_in_page int<br />
DECLARE @temp1 TABLE ( aaa nVarchar(100), bbb nVarchar(200), ccc float, ddd int)<br />
DECLARE @SQL nvarchar(4000)<br />
<br />
SET @page_num = 3<br />
SET @rows_in_page = 15<br />
INSERT INTO Temp1 = SELECT * FROM myTable<br />
[[[ now @temp has all records ]]]<br />
<br />
[--> some code to delete the first 30 records ((page_num-1)*rows_in_page) <--]<br />
[[[ now @temp1 has pages 3,4,5,6,.... ]]]<br />
<br />
SET @SQL = 'SELECT TOP ' + @rows_in_page + ' * from @temp1'<br />
[[[ now the variable @SQL = "SELECT TOP 15 * from @temp1" ]]]<br />
<br />
<br />
EXECUTE (@SQL)<br />
[[[ the EXECUTE will give me the first 15. That means only page 3 ]]]
Can anyone post the missing code ?
|
|
|
|
|
Hi
It can be done.
Calculate startpage and endpage using the parameters @rows & @currentpage.
You need to pass two values from the front-end that is current page and number of rows per page.
See below example with paging condition and I used temporary tables with identity column for filtering pages.
Hope you got it.
<br />
<br />
use northwind<br />
go<br />
<br />
--create temporary table to store all rows with identity column or serial number for page filter <br />
CREATE TABLE #OrdersPage ([Iden] [int] IDENTITY(1,1) NOT NULL, OrderId int,CustomerID nchar(10))<br />
<br />
declare @startpage int<br />
declare @endpage int<br />
declare @rows int<br />
declare @currentpage int<br />
<br />
set @rows = 10 -- number of rows per page from the front end<br />
set @currentpage = 1 -- pass current page from the front end<br />
<br />
set @endpage = (@rows * @currentpage) + 1<br />
set @startpage = @endpage - @rows<br />
<br />
print @startpage <br />
print @endpage <br />
<br />
insert into #OrdersPage <br />
select top 50 OrderID, CustomerID from Orders<br />
<br />
select * from #OrdersPage where Iden >= @startpage and Iden < @endpage <br />
-- set paging condition here using the column 'Iden' from the temporary table<br />
<br />
drop table #OrdersPage<br />
<br />
Harini
|
|
|
|
|
Thanks...
That helped me alot 
|
|
|
|
|
I am having a stored procedure returning a result set. I am using that stored procedure to fill a datatable. That stored procedure generates the rows dynamically from some tables using a cursor. There might be some invalid data in those tables and because of that invalid data some errors are thrown. When the error is generated while adding a row, that particular row is not added to the output (which is a temp table, by the way). But the execution will continue and new rows will be added even after the error is thrown. I also get the rows alongwith the error when I execute the stored procedure in the query analyzer. But when I execute the stored procedure using ADO.NET classes and try to fill a datatable, only that error is returned.
Is there any way to bypass that error and fill the datatable. I tried the SqlDataAdapter.FillError event, but that event is not getting fired  May be it doesnot get fired when the fill error occuring is occuring database side 
Any solutions ? ? ?
"The callee (server [not server application]) is not available and disappeared; all connections are invalid.
The call did not execute," said Internet Explorer, when I tried to access a deceased [window] object using JavaScript.
::..:.:..:: KiRtAN GoR ::..:.:..::
|
|
|
|
|
Does anybody know why this SQL statement returns duplicates even though I have included the DISTINCT keyword. There aren't duplicates in the database.
SELECT DISTINCT BLT_BILLM_TAX.BILLM_TAX_UNO, BLT_BILLM_TAX.BILL_TRAN_UNO, HBM_MATTER.MATTER_CODE, BLT_BILLM_TAX.JURISDIC_CODE,BLT_BILLM_TAX.TAX_CODE, BLT_BILLM_TAX.TAXABL_FEES_AMT, BLT_BILLM_TAX.TAXABL_HARD_AMT, BLT_BILLM_TAX.TAXABL_SOFT_AMT,
BLT_BILLM_TAX.TAX_ON_FEES_AMT, BLT_BILLM_TAX.TAX_ON_HARD_AMT, BLT_BILLM_TAX.TAX_ON_SOFT_AMT, BLT_BILLM_TAX.FEE_TAX_RATE,
BLT_BILLM_TAX.DISB_TAX_RATE, CDT_DISB.PERIOD, CDT_DISB.COST_CODE, BLT_BILL.BILL_NUM
FROM HBM_MATTER
INNER JOIN CDT_DISB ON HBM_MATTER.MATTER_UNO = CDT_DISB.MATTER_UNO
INNER JOIN BLT_BILLM_TAX ON CDT_DISB.BILL_TRAN_UNO = BLT_BILLM_TAX.BILL_TRAN_UNO
INNER JOIN BLT_BILL ON CDT_DISB.BILL_TRAN_UNO = BLT_BILL.TRAN_UNO
INNER JOIN APT_INVOICE ON CDT_DISB.SOURCE_TRAN_UNO = APT_INVOICE.TRAN_UNO
WHERE (APT_INVOICE.PERIOD BETWEEN '200601' AND '200601')
AND (APT_INVOICE.TRAN_UNO = 627167)
Any help would be appreciated
Thanks
-- modified at 6:00 Thursday 31st May, 2007
-- modified at 6:01 Thursday 31st May, 2007
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
Most likely it is a inner join issue. If there are multiple rows in the table you are joining to, but you are not including any columns from that join you will get duplicates. I would look at removing any unnecessary joins.
Hope that helps.
Ben
|
|
|
|
|
I don't know what it was but I messed about with it and swapped some stuff around and it seems to work now.
Thanks
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
hi friends
what is normailzation?
type of the normalization in sqlserver2000
regards
saravanan
|
|
|
|
|
Normalization is the process by where you take a set of columns and find out which should be grouped together to form tables. That's my definition...google would probably do a better job of it though
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
My dear friends,
I’m battling with a major issue in T-SQL. I want to specify the format of a DateTime variable. In VB there is a command called ‘DateSerial’ that you can use to specify each value (Day, Month and Year) of the Date variable, but there is no such function in T-SQL.
I live in South-Africa, and the date here is in the following order: Day, Month, Year. If I set a DateTime variable in this order - (SET @Date = ‘14/2/2007’) - the following error occurs: ‘The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.’ How can I solve this problem?
Thanks, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
Hi there,
Thanks for those who had a look at my problem, but I've solved it. Just one simple sample on a previous post on this forum gaved me the answer.
DECLARE @QAZ AS DATETIME<br />
SET DATEFORMAT dmy;SET @QAZ = '14-02-2007'<br />
print @QAZ
Result: Feb 14 2007 12:00AM
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
I strongly recommend you use the ISO date format yyyyMMdd, interpretation of which is not subject to the locale or language settings of your SQL Server installation or the connection.
So I would use SET @Date = '20070214'.
|
|
|
|
|
I am some what new at this, but I need some help connecting a sql database to my asp.net app, and I keep getting security errors. Now, I've connected to databases before, but that was in a class where our instructor created all of our dbs since it wasn't a database class. I've created the table using SQL Server Express 2005 and I am trying to connect it to a website I have built in Visual Studio 2005. What am I doing wrong?
|
|
|
|
|
check your connection string
and make sure that u write the server name and database name correct
|
|
|
|
|
I'm not even getting that far. When I go to set up the settings to my sql connection I get an error saying that SQL server doesn't allow remote connections. Do I need to create a user in the security settings in the database and then use that account for authentication? I'm use to doing this in mysql and php, but not to sure with the setup with the Microsoft products.
|
|
|
|
|
You need to run the Configuration Manager and set everything there...
|
|
|
|
|
Check this link[^]
it might help
rAm
i Think, i Wait, i Fast -- Siddartha
|
|
|
|
|
What's wrong with these code: i wanna set all the values in Field19 are 'abc'
CDaoDatabase db;
CDaoTableDef tableDef(&db);
db.Open("C:\\ktt.mdb");
tableDef.Open("Table3");
CDaoRecordset recset( &db );
tableDef.CreateField("Field19",dbText,255,dbUpdatableField | dbVariableField);
if ( !recset.IsEOF() )
recset.MoveLast();
while( !recset.IsEOF() )
{
recset.SetFieldValue("Field19","abc");
recset.MovePrev();
}
|
|
|
|
|
Hi All,
I am an app in VS C++ 2005 that uses an SQL Server 2005 database.
I am having trouble understanding how to enter a date into the database.
The field data type is: smalldatetime.
The date and time has been assigned to a string and would have a value like '30/05/2007 5:30PM'
When I write the query like: INSERT INTO MyTable(Date) VALUES(MySTRING)
I end up with an error and assume it's to do with the way O have formatted the string - am I on the right track ?
Fritzables.
|
|
|
|
|
Hi Fritzables,
It is very difficult to say on which track you are, without knowing the error message. But I expect the problem is the date format. You can try two things:
SET DATEFROMAT DMY
Or you can format the string to:
'2007-05-20 5:30PM'
I hope this helps.
Wout Louwers
|
|
|
|
|
G'Day Wout,
If I was to enter the sting at: 30/05/2007 5:30PM then the error message goes something like: syntax error near 5.
Ok, so if I was to use the SET DATEFORMAT DMY, at what point would I define this - sorry for the stupid question.
Regards
Fritzables.
|
|
|
|
|
Ah, the errormessage explains something. You should try:
INSERT INTO MyTable(Date) VALUES('30/5/2007 5:30PM')
I think you forgot the quotation marks.
If that does not work you can try:
SET DATEFORMAT dmy;INSERT INTO MyTable(Date) VALUES('30/5/2007 5:30PM')
Wout Louwers
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
