How do I implement paging please? - DataReader Object

Hi, I am a beginner in ASP.NET 2.0

I am creating a simple blog website.

I am using a DataReader object to collect all of the entries in the database (in one full sweep) and then display them by adding rows to a table and populating them.

I would like to enable paging so that only 5 blog entries at a time are displayed. The user should be able to page backwards and forwards 5 at a time.

Please can somebody give me some ideas of how I should do this?

Should I read all of the data into some kind of list so that all of the data is then readily available? An arraylist or something?

I realise that the DataReader object reads in one continuous stream so I couldn't expect it to read backwards and forwards from the database.

I include a portion of my code below which reads the database.

Many thanks.

Dim objCommand As New SqlCommand
objCommand.Connection = objConnection
objConnection.Open()
objCommand.CommandText = "SELECT BlogID, Date, Blog FROM BlogSite2_Blogs ORDER BY Date DESC"

Dim objReader As SqlDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

Dim dateDateTime As Date
Dim strBlogEntry As String
Dim intBlogID As Integer

Do While objReader.Read()
intBlogID = objReader.GetValue(0)
dateDateTime = objReader.GetDateTime(1)
strBlogEntry = objReader.GetString(2)

CreateBlogRow(intBlogID, dateDateTime, strBlogEntry)
Loop

objReader.Close()

check this link

http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

|||

Hi

I would do it in Database with stored procedure.

In the SP described bellow you Just need to pass in a short SQL statement, the Order By clause, and the start row and end row you'd like to return in the resultset.

CREATE PROCEDURE ReturnPage(@.Select varchar(1000), @.OrderByvarchar(1000), @.StartRowint, @.EndRowint)ASBEGIN declare @.ColListvarchar(2000);declare @.Where varchar(2000);declare @.iint;declare @.i2int;declare @.tmpvarchar(1000);declare @.decvarchar(1000);declare @.fvarchar(100);declare @.dvarchar(100);declare @.Symbolchar(2);declare @.SQLvarchar(5000);declare @.Sortvarchar(1000);set @.Sort = @.OrderBy +', 'set @.dec =''set @.Where =''set @.SQL =''set @.i = charindex(',' , @.Sort)while @.i != 0begin set @.tmp =left(@.Sort,@.i-1)set @.i2 = charindex(' ', @.tmp)set @.f =ltrim(rtrim(left(@.tmp,@.i2-1)))set @.d =ltrim(rtrim(substring(@.tmp,@.i2+1,100)))set @.Sort =rtrim(ltrim(substring(@.Sort,@.i+1,100)))set @.i = charindex(',', @.Sort)set @.symbol =casewhen @.d ='ASC'then'>'else'<'end +casewhen @.i=0then'='else''end set @.dec = @.dec +'declare @.' + @.f +' sql_variant; 'set @.ColList =isnull(replace(replace(@.colList,'>','='),'<','=') +' and ','') + @.f + @.Symbol +' @.' + @.fset @.Where = @.Where +' OR (' + @.ColList +') 'set @.SQL = @.SQL +', @.' + @.f +'= ' + @.fend set @.SQL = @.dec +' ' +'SET ROWCOUNT ' +convert(varchar(10), @.StartRow) +'; ' +'SELECT ' +substring(@.SQL,3,7000) +' from (' + @.Select +') a ORDER BY ' + @.OrderBy +'; ' +'SET ROWCOUNT ' +convert(varchar(10), 1 + @.EndRow - @.StartRow) +'; ' +'select * from (' + @.Select +') a WHERE ' +substring(@.Where,4,7000) +' ORDER BY ' + @.OrderBy +'; SET ROWCOUNT 0;'exec(@.SQL)END

Pls refer toEfficient and DYNAMIC Server-Side Paging with T-SQLfor details.

|||

Thank you very much indeed for this Young Fang. I don't understand it all but I shall have a good read and no doubt, I shall learn a lot of new things :)

How do i implement Multi value parameter

Hi..

I want to have a multivalue parameter in my report... I have selected that parameter and clicked on multivalue and then when i run the report.. it works fine for one and and not for more than one...

any help will be appreciated.

Regards

Karen

Karen, a question, how is your multivalue parameter used in your datasource? It should be :

SELECT *
FROM HELLO_DATA
WHERE COLUMN1 IN (@.MULTIVALUEPARAMETER1)

Hope this helps,

|||

thanks i figured it out and now it works fine.

Regards

Karen

sql

How do i implement If Then Else Conditions

Hi All,

I'm very new to Integration Services. Self Learned this ETL tool based on my prior ETL tool knowledge.

Can you tell me how do write IF Then Else conditions for every column in my source and redirect to a single output?

Hey correction here ... i'm using flat file as my source

Thanks in Advance,

Suresh N

It would help if you could provide a more explicit example of the type of logic you're hoping to implement, but here is a response based on my best guess at what you're trying to accomplish.

FIrst, start with the Derived Column transformation in your data flow: http://msdn2.microsoft.com/en-us/library/ms141069.aspx. This transform is the tool of choice for adding new columns - including new columns based on conditional logic and the values in existing columns - to your data flow.

Next, build an expression using the SSIS conditional operator to implement the If Then Else logic you need: http://msdn2.microsoft.com/en-us/library/ms141680.aspx.

You can also use the Script Transformation to perform more complex If Then Else logic in the data flow, but I personally try to avoid this if I can, as it makes your packages more difficult to maintain.

Please let us know if this gives you what you need.

|||

Thanks for reply Matthew.

I'm not able to do. Still needed help.

I have a flat file as a source, for each column i need to detect for NULL values. If it is NULL then i need to populate "99" Else Value.

I'm trying the same in Derived column transformation, not succeeded.

Do SSIS have something where i could write If Then Else ?

thanks..

Suresh N

|||

Suresh,

You can use a derived column transformation in the data flow with an conditional expression like

ISNULL(column1) ? 99 : column1

http://msdn2.microsoft.com/en-us/library/ms137538.aspx

|||

Suresh N wrote:

Thanks for reply Matthew.

I'm not able to do. Still needed help.

I have a flat file as a source, for each column i need to detect for NULL values. If it is NULL then i need to populate "99" Else Value.

I'm trying the same in Derived column transformation, not succeeded.

Do SSIS have something where i could write If Then Else ?

thanks..

Suresh N

This is exactly what the Derived Column transform is designed to do. Can you please post some specific information about what you are doing and what results you're seeing. There's not a lot of helpful information in your post to assist in troubleshooting.

|||

Hi Rafael,

Thanks much for your answer.

This has made my job done.

Regards,

Suresh N

|||

If you have to do this for several files accross a couple of packages then this when I think you should consider a custom transform. Faster development and maintenance as well as the ability to control custom business rules in a single (external to packages) assembly. The use of 99 in a local business rule I believe.