Stored Procedures and Crystal Reports with different tables

Question

0.00/5 (No votes)

See more:

I am running a crystal report with four different tables using a stored procedure. The select statement in the Stored Procedured is as follows:

SQL

SELECT DISTINCT [tblWHOModelEML].[ActiveIngredient]
		  ,[tblWHOModelEML].[Strength]
          ,[DosageFormRoute]
		  ,[tblWHOATCLevelTwoCodDescrpt].[ATCLevelTwoDescript]
		  ,[tblWHOATCLevelOneCodeDescript].ATCLevelOneDescript
           FROM [dbo].[tblWHOModelEML] LEFT OUTER JOIN [dbo].[tblMCAZ_Approved] ON [dbo].[tblWHOModelEML].[EMLCode] = [dbo].[tblMCAZ_Approved].[EMLCode] INNER JOIN [dbo].[tblWHOATCLevelTwoCodDescrpt] ON [dbo].[tblWHOModelEML].[ATCLevelTwoCode] = [dbo].[tblWHOATCLevelTwoCodDescrpt].[ATCLevelTwoCode] INNER JOIN tblWHOATCLevelOneCodeDescript ON tblWHOModelEML.ATCLevelOneCode = tblWHOATCLevelOneCodeDescript.ATCLevelOneCode
	       WHERE NOT EXISTS (SELECT [dbo].[tblMCAZ_Approved].[ActiveIngredient], [dbo].[tblMCAZ_Approved].[Strength], [dbo].[tblMCAZ_Approved].[DosageForm] FROM [dbo].[tblMCAZ_Approved] WHERE [dbo].[tblWHOModelEML].[EMLCode] = [dbo].[tblMCAZ_Approved].[EMLCode]) 
	       ---AND [tblMCAZ_Approved].[Applicant] Like '%@ApplicantName%' 
		   ORDER BY ActiveIngredient

I have commented out the "AND" which was supposed to further filter the result set using a particular selected Aplicant as this is not working. Can anyone assist me in including this where Applicant LIKE statement into the above SQL statement to make it work. Someone was suggesting use of subqueries and I am blank on this.

Regards.

Chris

Posted 26-Jun-12 14:21pm

Member 8863655

Updated 26-Jun-12 18:12pm

Prasad_Kulkarni

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kiran Susarla · Answer 1 · 2012-06-27T00:16:00

Solution 1

1. Make ApplicationName as the parameter in your storedprocedure.
2. In the crystal reports create a Report parameter and pass this as parameter value to Storedprocedure.

So when the report is generated this value is passed to storedprocedure and the result will be further filtered.

Posted 27-Jun-12 0:16am

Kiran Susarla

Comments

Member 8863655 27-Jun-12 6:47am

Many thanks, Kiran, I am new to programming and Crystal reports, how do I accompllish the second step of passing the parameter value to the Storedprocedure?

Chris

Kiran Susarla · Answer 2 · 2012-06-27T01:18:00

Solution 2

Hello Chris
I don't have a sample available immediately. The following links
http://www.c-sharpcorner.com/UploadFile/Yoyi/passparameterstoreport09202005005343AM/passparameterstoreport.aspx?ArticleID=5b9f2758-d3a3-4a77-9ef1-e3bf7044500d[^]

Loading Crystal Report reports which use Stored Proc in C#[^]

should help you out.

Kiran

Posted 27-Jun-12 1:18am

Kiran Susarla

Comments

Member 8863655 27-Jun-12 8:45am

Many thanks Kiran

i have the following code in my prompt for parameter values combobox:

Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
Dim myReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument()
Dim reportPath As String = "C:\Reports\" & "MCAZApprovedWHOEMLReport.rpt"
myReportDocument.Load(reportPath)
myReportDocument.Refresh()
myReportDocument.SetParameterValue("@ApplicantName", appComboBox.Text.Trim())
Dim frm As New frmReportViewer(myReportDocument)
frm.ShowDialog()
End Sub

When i click search, this calls the following code:

Imports CrystalDecisions.CrystalReports.Engine
Imports System.Configuration
Public Class frmReportViewer
Inherits Form
Private m_cryRpt As ReportDocument
Public Sub New(myReportDocument As ReportDocument)
InitializeComponent()
m_cryRpt = myReportDocument
End Sub
Private Sub frmReportViewer_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
DbConnectionInfo.SetConnectionString(ConfigurationSettings.AppSettings(0))
Dim logOnInfo As CrystalDecisions.Shared.TableLogOnInfo
Dim connectionInfo As CrystalDecisions.Shared.ConnectionInfo

For Each Table As Table In m_cryRpt.Database.Tables
logOnInfo = Table.LogOnInfo
connectionInfo = logOnInfo.ConnectionInfo
'Set the connection parameters.
connectionInfo.DatabaseName = DbConnectionInfo.InitialCatalog
connectionInfo.ServerName = DbConnectionInfo.ServerName
If Not DbConnectionInfo.UseIntegratedSecurity Then
connectionInfo.Password = DbConnectionInfo.Password
connectionInfo.UserID = DbConnectionInfo.UserName
Else
connectionInfo.IntegratedSecurity = True
End If
Table.ApplyLogOnInfo(logOnInfo)
Next
myCrystalReportViewer.ReportSource = m_cryRpt
myCrystalReportViewer.Refresh()
End Sub
End Class

I am however getting an exception at the passing parameter stage which says:
System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2147352565
Message=Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
Source=CrystalDecisions.ReportAppServer.DataDefModel
StackTrace:
at CrystalDecisions.ReportAppServer.DataDefModel.FieldsClass.get_Item(Int32 Index)
at CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions.get_Item(Int32 index)
at CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions.get_Item(String fieldName)
at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetParameterValue(String name, Object val)
at MCAZApprovedWHOEMLAnal.frmMain.btnSearch_Click(Object sender, EventArgs e) in C:\Users\Chitemerere\Documents\Visual Studio 2010\Projects\MCAZApprovedWHOEMLAnal\MCAZApprovedWHOEMLAnal\frmMain.vb:line 39
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32