Showing posts with label approach. Show all posts
Showing posts with label approach. Show all posts

Friday, March 30, 2012

Problems with the SELECT ROW_NUMBER() OVER approach

Hi guys, I am using this approach in order to be able to do server side paging on the SQL server (why Microsoft, why can't you just put the limit vlause to work?).

Anyway, here is what I am using...

http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

My code is somehow structured like this:

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Day, Month, Year DESC)AS Row, *
FROM reptable
where $LOTS OF CLAUSES$
)

select Year, Month, Day, $ALL MY DATA$ from LogEntries
where Row between (@.PageIndex - 1) * @.PageSize + 1 and @.PageIndex*@.PageSize

This works like a CHARM.

Problem is I've placed this under a stored procedure and I need the procedure to return the total records affected by the first query (the total rows returned on that query).

But, if I use set @.parameter=@.@.ROWCOUNT after the select, it gives me only 10 (which is the pagesize)

If I use set @.parameter=@.@.ROWCOUNT before the select, SQL says it is not valid (I guess it waits for a SELECT after a WITH Clause).

I tried select count(*) from LogEntries after the select clause. Still no success. It says the table doesn't exist. I guess the object looses his value after the select is executed.

What can I do in order to make this work?

Any help is appreciated

Luis

What about:

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Day, Month, Year DESC)AS Row, *
FROM reptable
where $LOTS OF CLAUSES$
)

select Year, Month, Day, (SELECT MAX(Row) FROM LogEntries) ,$ALL MY DATA$ from LogEntries
where Row between (@.PageIndex - 1) * @.PageSize + 1 and @.PageIndex*@.PageSize

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Or use:

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Day, Month, Year DESC)AS Row, COUNT(*) OVER() as cnt, *
FROM reptable
where $LOTS OF CLAUSES$
)
select Year, Month, Day, cnt ,$ALL MY DATA$ from LogEntries
where Row between (@.PageIndex - 1) * @.PageSize + 1 and @.PageIndex*@.PageSize

--Jens' idea will work too, but I think putting it in the CTE will do it in less passes of the data.

Rob|||I am sorry guys (stupid mistake). I forgot to mention that the count(*) cannot come on the SQL column list because this list is used on a auto-generated report and there is no way of removing it.
So, the count should be returned in a output parameter of the procedure. It can't be on the column list.|||Ah... well, if you're not getting many rows for your report, you could just populate a temporary table with all the columns you want (including the count), and then do something like:

declare @.thecount int
select top (1) @.thecount = cnt
from #thetemptable

select col1, col2 --, etc (but not cnt)
from #thetemptable

drop table #thetemptable|||The problem is that the report is usually big (more then 1.000 rows) and I am afraid temporary tables will cause a delay on the process. Do you think it is a good idea to use temporary tables for that?

Is there any other way of getting this value out?|||Well, if you can't include it in your main query, then you will have to requery it somewhere. Since you're only producing a page of data at a time, I think a temporary table might not be a bad option.

Otherwise you'll have to re-run your query, with a count(*) in place of the select fields. Might be just fine, but you'll have to test it to see.

Rob

Wednesday, March 28, 2012

Problems with SqlDataAdapter

I'm working on a project to dynamically create PDFs with content from an SQL server. My current approach is to get the data I need into a DataSet using SqlDataAdapter, write the DataSet to a stream as XML, transform the XML into FO and then output a pdf using FOP. For some reason I get the following exception "System.NullReferenceException - Object reference not set to an instance of an object" when I try to set the SelectCommand property of my data adapter. Code for the project follows:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As SqlConnection
Dim cmd As SqlCommand
Dim IDlist As String
Dim keyidary As Array
Dim query As String
Dim tempxml As Stream

query = "*****query is valid and very long, so it's not included here*****"

IDlist = Request.Form("chosenIDs")

keyidary = Split(IDlist, ",")
Dim makequery As New StringBuilder(query)

For Each ID As String In keyidary
makequery.Append(" OR (KeyID = '" & ID & "')")
Next

query = makequery.ToString()

'Response.Write(query) When uncommented this prints the query just fine
'Response.End()

Try
con = New SqlConnection
con.ConnectionString = "****Connection String is valid****"

cmd = New SqlCommand
cmd.CommandText = query
cmd.CommandType = CommandType.Text
cmd.Connection = con

Dim adpt As SqlDataAdapter
adpt.SelectCommand = cmd

'Response.Write(query) When these are above or between the previous 2 statements, query is printed,
'Response.End() otherwise I get the error described above.

Dim profiles As New DataSet
adpt.Fill(profiles)
profiles.WriteXml(tempxml)

Dim step1 As XslTransform = New XslTransform
Dim step2 As XslTransform = New XslTransform
step1.Load(Server.MapPath("TransAttmpt1.xslt"))
step2.Load(Server.MapPath("formatXML.xsl"))

Dim xml, pdf As String
xml = "profiles.xml"
pdf = "Profiles.pdf"

Dim temp2xml As New XPathDocument(tempxml)

Response.Write(query)
Response.End()
Dim midstream As Stream
Dim finalxml As StreamWriter = New StreamWriter(xml)

step1.Transform(temp2xml, Nothing, midstream, Nothing)
Dim xpathdoc2 As XPathDocument = New XPathDocument(midstream)

step2.Transform(xpathdoc2, Nothing, finalxml, Nothing)
GeneratePDF(xml, pdf)

'There's a lot more but it doesn't seem relevant now...

I'm somewhat at a loss on how to proceed and any help is very greatly appreciated.

Thanks!

You are not initializing the dataadpater. You should initialize like this:

Dim adptAs SqlDataAdapter =New SqlDataAdapter()
|||

Figured this out probably 3 minutes before you posted but thanks!