.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
Go Back  Xtreme Visual Basic Talk > > > .SQLQueryString for Asp.Net?


Reply
 
Thread Tools Display Modes
  #1  
Old 10-26-2006, 09:08 AM
kingesk kingesk is offline
Centurion
 
Join Date: May 2004
Posts: 108
Default .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:
Code:
' 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.
Code:
' .Net not working
Me.CrystalReportViewer1.SelectionFormula = strSQL
Me.CrystalReportViewer1.ReportSource = strReportPath 
Me.CrystalReportViewer1.RefreshReport()

Here's the code to build my Select Statement
Code:
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()
_
Reply With Quote
  #2  
Old 10-26-2006, 10:16 AM
wayneph's Avatar
wayneph.SQLQueryString for Asp.Net? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

In the Selection Criteria, I think you just pass in the where clause. The SELECT and FROM clauses should be provided by the report.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #3  
Old 10-27-2006, 08:31 AM
kingesk kingesk is offline
Centurion
 
Join Date: May 2004
Posts: 108
Default

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


Here's an example:
Code:
strWhere = "{tProcessPartDetail.PartNumber}='" & strPart1 & "' Or {tProcessPartDetail.PartNumber}='" & strPart2 & "'"

Me.CrystalReportViewer1.SelectionFormula = strWhere

Me.CrystalReportViewer1.ReportSource = strReportPath 
Me.CrystalReportViewer1.RefreshReport()
Reply With Quote
  #4  
Old 10-30-2006, 06:06 PM
kingesk kingesk is offline
Centurion
 
Join Date: May 2004
Posts: 108
Default

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
Code:
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:
Code:
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()
Reply With Quote
  #5  
Old 10-30-2006, 08:31 PM
wayneph's Avatar
wayneph.SQLQueryString for Asp.Net? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

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.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net? .SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
 
.SQLQueryString for Asp.Net?
.SQLQueryString for Asp.Net?
 
-->