Next N records, paginated sql server results

I have several places on my web site where I let the user page through search results and recordsets. Usually this is inneficient because your return all resulsts and essentially hide everything but the rows they want to see. This is very inneficient and the only examples of doing this correctly were difficult at best.

Well, with SQL Server 2005 there is a new feature - ROW_NUMBER() that makes this simpoler to implement. Not 100% intuitive but I was able to figure it out. The following link is a good tutorial that helped me:

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

Dynamic SQL Where statements

I was trying to figure out how to use an if statement in the where clause of a sql statement. I came across this article that explains the use of coalesce. This is a great way to handle this.

http://www.sqlteam.com/item.asp?ItemID=2077

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City) AND
Cus_Country = COALESCE(@Cus_Country,Cus_Country)

Read the article to understand it. Basically if there is a value provided to the stored procedure then the comparison is done. If there is no value it essentially includes all records or (matches itself). I know, that doesn't make sense.

Setting Parameters in Crystal Reports .NET

Well, I missed the post about setting parameters. That was one of the puzzle pieces too and I got lazy and didn't post about it. Here it is.

Setting Parameters in Crystal Reports .NET

A number of methods out there use the CrystalReportViewer control to set parameters. That's great unless you need to export your report to PDF (or XLS or RTF). This is a code snippet I use, it comes from Business Objects tech support:

rptCount = New ReportDocument
rptCount.Load(Server.MapPath("reportname.rpt"))

''Get the collection of parameters from the report
crParameterFieldDefinitions = rptCount.DataDefinition.ParameterFields
''Access the specified parameter from the collection
crParameter1 = crParameterFieldDefinitions.Item("Param1")
crParameter2 = crParameterFieldDefinitions.Item(“Param2")

''Get the current values from the parameter field. At this point
''there are zero values set.
crParameter1Values = crParameter1.CurrentValues
crParameter2Values = crParameter2.CurrentValues

''Set the current values for the parameter field
crDiscrete1Value = New ParameterDiscreteValue
crDiscrete1Value.Value = Request.Form(“param1value“)

crDiscrete2Value = New ParameterDiscreteValue
crDiscrete2Value.Value = Request.Form(“param2value“)

''Add the first current value for the parameter field
crParameter1Values.Add(crDiscrete1Value)
crParameter2Values.Add(crDiscrete2Value)

''All current parameter values must be applied for the parameter field.
crParameter1.ApplyCurrentValues(crParameter1Values)
crParameter2.ApplyCurrentValues(crParameter2Values)

Crystal Reports Database Login

The last major piece of my puzzle. This code lets me pass database and login information.

http://diamond.businessobjects.com/node/134

VB.NET
---------

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared


Dim crtableLogoninfos As New TableLogOnInfos()
Dim crtableLogoninfo As New TableLogOnInfo()
Dim crConnectionInfo As New ConnectionInfo()
Dim CrTables As Tables
Dim CrTable As Table
Dim TableCounter

'If you are using a Strongly Typed report (Imported in
'your project) named CrystalReport1.rpt use the
'following:

Dim crReportDocument As New CrystalReport1()

'If you are using a Non-Typed report, and
'loading a report outside of the project, use the
'following:

Dim crReportDocument As New ReportDocument()

crReportDocument.Load("c:\myReports\myReport.rpt")

'Set the ConnectionInfo properties for logging on to
'the Database

'If you are using ODBC, this should be the
'DSN name NOT the physical server name. If
'you are NOT using ODBC, this should be the
'physical server name

With crConnectionInfo
.ServerName = "DSN or Server Name"

'If you are connecting to Oracle there is no
'DatabaseName. Use an empty string.
'For example, .DatabaseName = ""

.DatabaseName = "DatabaseName"
.UserID = "Your User ID"
.Password = "Your Password"
End With

'This code works for both user tables and stored
'procedures. Set the CrTables to the Tables collection
'of the report

CrTables = crReportDocument.Database.Tables

'Loop through each table in the report and apply the
'LogonInfo information

For Each CrTable in CrTables
CrTableLogonInfo = CrTable.LogonInfo
CrTableLogonInfo.ConnectionInfo =
crConnectionInfo
CrTable.ApplyLogOnInfo(crtableLogoninfo)

'If your DatabaseName is changing at runtime, specify
'the table location.
'For example, when you are reporting off of a
'Northwind database on SQL server you
'should have the following line of code:

crTable.Location = "Northwind.dbo." &
crTable.Location.Substring(crTable.Location.LastIndexOf(
".") + 1)
Next

'Set the viewer to the report object to be previewed.

CrystalReportViewer1.ReportSource = crReportDocument

Crystal Reports Export to PDF

Well, I finally did it. The code below is what lets me export a crystal report to a pdf file. Previous entries outline some of the steps I have taken to get here. Not sure if all were necessary but it works. No I have to figure out how to pass parameters, datasource and login information. On to figure that out.

Option Strict On
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Partial Class _Default
Inherits System.Web.UI.Page
Private exportPath As String
Private myDiskFileDestinationOptions As DiskFileDestinationOptions
Private myExportOptions As ExportOptions
Private myReport As ReportDocument
Private selectedNoFormat As Boolean = False
Private Sub ConfigureCrystalReports()
myReport = New ReportDocument()
myReport.Load("C:\websites\rpt\report1.rpt")
'myCrystalReportViewer.ReportSource = myReport
End Sub

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
ConfigureCrystalReports()
ExportSetup()
myExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat
myDiskFileDestinationOptions.DiskFileName = exportPath & "PortableDoc.pdf"
myExportOptions.DestinationOptions = myDiskFileDestinationOptions
myReport.Export()
End Sub
Public Sub ExportSetup()
exportPath = "C:\Exported\"
If Not System.IO.Directory.Exists(exportPath) Then
System.IO.Directory.CreateDirectory(exportPath)
End If
myDiskFileDestinationOptions = New DiskFileDestinationOptions()
myExportOptions = myReport.ExportOptions
myExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
End Sub
End Class

Use a javascript confirm box to ask the user if they want to delete.

Found this here: http://psacake.com/web/iw.asp

<a onclick="return confirm('Are you sure you want to delete?')" href="delete.page?id=1">Delete</a>