.SQLQueryString for Asp.Net?

I used to have some code in a VB 6 application to send the whole Select statement using .SQLQueryString to Crystal Reports (not just parameters) but I don't see this option in ASP.Net. Is there another way to do this in ASP.Net/VB.Net?

VB6:

' vb 6 works
Set application = CreateObject("crystalruntime.application")
Set Report = application.OpenReport(mstrReportName)
Report.SQLQueryString = strSQL

CRViewer91.ReportSource = Report
CRViewer91.ViewReport
CRViewer91.Refresh




I was trying this in ASP.Net but I'm getting an error. I'm not sure that .SelectionFormula is equivalent to .SQLQueryString. It just says Error in Formaul and shows the Select Statement.

' .Net not working
Me.CrystalReportViewer1.SelectionFormula = strSQL
Me.CrystalReportViewer1.ReportSource = strReportPath
Me.CrystalReportViewer1.RefreshReport()




Here's the code to build my Select Statement

Dim strSearchPart As String = CType(Me.Session("SearchPart"), String)
Dim strSearchDesc As String = CType(Me.Session("SearchDesc"), String)
Dim strSearchKeyWords As String = CType(Me.Session("SearchKeyWords"), String)
Dim strSearchProdCodes As String = CType(Me.Session("SearchProdCodes"), String)

Dim strWhere As String


If Not strSearchPart = "" Then
strWhere=" PartNumber Like '%" & strSearchPart & "%' "
End IF

If Not strSearchDesc = "" Then
If Not strWhere = "" Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & " Description Like '%" & strSearchDesc & "%' "
End IF

If Not strSearchKeyWords = "" Then
If Not strWhere = "" Then
strWhere = strWhere & " And "
End If
strWhere= strWhere & " KeyWordSearch Like '%" & strSearchKeyWords & "%' "
End IF

If Not strSearchProdCodes = "" Then
If Not strWhere = "" Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & " ProdCode Like '%" & strSearchProdCodes & "%' "
End IF

If Not strWhere = "" Then
strWhere = " Where " & strWhere
End IF

Dim strSQL As String
If strWhere ="" Then
strSQL = "SELECT * FROM tWebInfo ORDER BY PartNumber"
Else
strSQL = "SELECT * FROM tWebInfo " & strWhere & " ORDER BY PartNumber"
End IF


' .Net not working
Me.CrystalReportViewer1.SelectionFormula = strSQL
Me.CrystalReportViewer1.ReportSource = strReportPath
Me.CrystalReportViewer1.RefreshReport()



_

In the Selection Criteria, I think you just pass in the where clause. The SELECT and FROM clauses should be provided by the report.

Thank you. That works. It seems to need the brackets and also the word Where should not be used.


Here's an example:

strWhere = "{tProcessPartDetail.PartNumber}='" & strPart1 & "' Or {tProcessPartDetail.PartNumber}='" & strPart2 & "'"

Me.CrystalReportViewer1.SelectionFormula = strWhere

Me.CrystalReportViewer1.ReportSource = strReportPath
Me.CrystalReportViewer1.RefreshReport()

This may be becoming more of a database/reports question but I made a small change to this one and I ran into another problem.

I'm not using the Crystal Viewer now so I switched from .SelectionFormula to .RecordSelectionFormula.

Then I tried to switch from an "=" to a Like



This code returns a record


Dim Report As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim strPart1 As String = "01-GL215287"

strWhere = "{tProcessPartDetail.PartNumber}='" & strPart1 & "'"
Report.Load(strReportPath)
Report.RecordSelectionFormula = strWhere
Report.Refresh()





but this one doesn't return any records:

Dim Report As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim strPart1 As String = "01-GL215287"

strWhere = "{tProcessPartDetail.PartNumber} Like '%" & strPart1 & "%'"
Report.Load(strReportPath)
Report.RecordSelectionFormula = strWhere
Report.Refresh()

yeah, this is getting into Crystal Reports. The way this is working, all of the records are getting passed to the report and then the report is doing the filtering. Depending on how big your table is, you may want to try and use a stored procedure or something like that as your source, so that the filtering happens in SQL.

That being said, instead of Like, you may be able to use an InStr or PatIndex type function. Just see if the string you're looking for exists inside the full field.