Passing Parameter to Crystal Report from VB

[longfellow]

Hi all

I want to pass a date as parameter to a stored procedure which on execution retreives records for that particular date. I want to pass this parameter from VB. How can i pass the parameter from VB? What are the parameter settings i should set in Crystal reports for the same? What are the references i should set in VB for Crystal Reports Ver9? My database is SQL Server 2000. Please reply to all my queries.

Thanking you in advance

Longfellow

 

[vidru]

VB6 or .Net?

Here are the sample apps for each:
Listing of RDC sample applications
Listing of .NET Sample Applications in C# and C++ .NET

-dave

 

[longfellow]

Its VB6

 

[vidru]

This FAQ of mine (faq768-5374) has more than what you need, but will get you started as far as references/components.

To pass a parameter value, you'd do something like this:

crxRpt.ParameterFields(1).AddCurrentValue "Your Param Value"

-dave

 

[longfellow]

How do i define the parameter fields or values in Crystal reports for what i am passing from VB?

 

[IdoMillet]

In the field explorer (within Crystal 9) select Parameter Fields and click New...

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[longfellow]

How do i refer that to my VB form?

 

[IdoMillet]

Please provide more information. So far, it seems like we are not answering your questions because we don't understand your situation fully.


CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[longfellow]

Versions VB 6 CR 9 and SQL Server 2000

I run a form in VB which will pass a date parameter to the crystal report which has to give the report for the day of the qname, caller id and the total calls. I am sorry for asking again Can you very breifly tell ne what i should do in both VB as well as CR for the whole process to be completed.

 

[longfellow]

There is no other code on the form. I just have to pass the parameter from the form to Cr and generate the report. If i could do this with a single form then i will integrate this across several forms in the project. So if you can help me it will be of very great use

Thanking you in advance

 

[longfellow]

Can someone please helpme? It is urgent

 

[vidru]

Have you looked at the samples, or looked at any of the information in the FAQ I pointed you to? The FAQ lists what references you need to add, and at least one of the samples will demonstrate how to pass parameters from VB.

If you're designing the report using a stored procedure that has parameters as the datasource, then the proc's parameters will be added automatically to the report. When you call the report from VB, you can reference the parameters by name, or by index.

I suggest you take a look at the samples, and download this:
Report Designer Component (RDC) Object Reference Help file

-dave

 

[longfellow]

hi

The following is my VB code written for a command button when i input the date in the text box and click the command button the report should get generated. I am not egtting any data in the report. There is data in the table in the database.

-----------------------------------------------------------
the code of stored procedure i use is

in SQL SERVER 2000

Create procedure Unass_test (@date as datetime)
as
Begin
select convert (varchar(10),CALLDATE,101)AS CALLDATE, CPN, QNAME, CALLS, ABANDONED_CALLS from DAILY_UNASSIGNED_CPN where calldate = @date
order by QNAME, CALLS
END
----------------------------------------------------------
/*my VB CODE*/

Private Sub Command1_Click()
Dim result As Long
Dim crystal As New CRAXDRT.Application
Dim report As CRAXDRT.report


Dim conn As New ADODB.Connection
Dim comm As New ADODB.Command
Dim constr As String
Dim RS As New ADODB.Recordset
Dim Calldate As Date



Const adCmdStoredProc = &H4
Const adInteger = 3
Const adVarWChar = 202
Const adParamInput = &H1
Const adParamOutput = &H2

Dim MyComm As New ADODB.Command
Dim IParam1 As New ADODB.Parameter
Dim Sql As String

Sql = "Unass_test"
Calldate = Text1.Text


constr = "Provider=SQLOLEDB;Data Source=10.0.1.99;User ID=itp;Password=1s;Initial Catalog=itp"
conn.Open constr


MyComm.ActiveConnection = conn
MyComm.CommandType = adCmdStoredProc
MyComm.CommandTimeout = 0
MyComm.CommandText = Sql

Set CommParam = MyComm.Parameters
Set IParam1 = MyComm.CreateParameter("Date", adVarWChar, adParamInput, 19)
CommParam.Append IParam1

MyComm("Date") = Calldate



Set RS = MyComm.Execute


If Not RS.EOF Then
Set report = crystal.OpenReport(App.Path & "\test-unass.rpt")

report.DiscardSavedData


Dim crpParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim crpParamDef As CRAXDRT.ParameterFieldDefinition

Set crpParamDefs = report.ParameterFields
report.EnableParameterPrompting = False


j = report.Database.Tables.Count
server = "10.0.1.99"
db = "itp"
For i = 1 To j
report.Database.Tables(i).SetDataSource RS
report.Database.Tables(i).SetLogOnInfo server, db, "itp", "1s"
Next


report.Database.SetDataSource RS, 3, 1
'LINK REPORT TO RECORDSET

CRViewer91.ReportSource = report
'LINK VIEWER TO REPORT

CRViewer91.ViewReport
'SHOW REPORT

Do While CRViewer91.IsBusy
'ZOOM METHOD DOES NOT WORK WHILE
DoEvents 'REPORT IS LOADING, SO WE MUST PAUSE
Loop 'WHILE REPORT LOADS.

CRViewer91.Zoom 94

RS.Close 'ALL BELOW HERE IS CLEANUP
Set RS = Nothing

conn.Close
Set conn = Nothing

Set crystal = Nothing
Set report = Nothing
Else
MsgBox "Data Not available"
End If
End Sub

Private Sub Form_Load()
Text1.Text = FormatDateTime(Now - 2, vbShortDate) & " 00:00:00"

End Sub

-----------------------------------------------------------
Please let me know where the problem is

 

[vidru]

If your CallDate field has both Date AND Time stored with it, and your intent is to get all the records where the Date portion of the CallDate = @date, then you probably aren't going to get the results you're expecting. Here's one way you could handle that:

CREATE PROCEDURE Unass_test  
  @Date AS DATETIME
AS 
SELECT CONVERT(VARCHAR(10),CALLDATE,101) AS CALLDATE,
       CPN, QNAME, CALLS, ABANDONED_CALLS 
  FROM DAILY_UNASSIGNED_CPN 
  WHERE YEAR(CALLDATE) = YEAR(@Date) AND
        DAY(CALLDATE) = DAY(@Date) AND
        MONTH(CALLDATE) = MONTH(@Date)
ORDER BY QNAME, CALLS

If that doesn't do it, the problem could be that you're trying to send a recordset to a report that's not expecting one?

-dave