Solved

Trying to pass a parameter to the IReport object

Posted on 2003-11-26
11
993 Views
Last Modified: 2009-03-05
Here is my code.  It works fine b/c i can get it to print without passing a parameter but now i want to
try and pass 1 parameter.

_variant_t varParameter;
varParameter.vt = VT_BSTR;

_bstr_t bsString("@Application_Id; 2454276");
varParameter.bstrVal = bsString;
hr = spReport->ParameterFields->Item[1]->AddCurrentValue(varParameter);
OR
hr = spReport->ParameterFields->Item[0]->AddCurrentValue(varParameter);


but it keeps getting caught in the catch(_com_error &err) exception

The error  is :
err: Type mismatch
__vfptr: const _com_error: vftable
m_hersult = -2147352571
m_pszMsg = <Bad Ptr>
...
Not sure if this is enough.

Any help would be appreciated.
0
Comment
Question by:borg48
11 Comments
 

Author Comment

by:borg48
Comment Utility
This is the correct parameter:
_bstr_t bsString("@Application_Id; 2454276; 1");

Do i need to create one parameter for each?
0
 
LVL 7

Expert Comment

by:mnye
Comment Utility
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Did you add a parameter to the report?

mlmcc
0
 

Author Comment

by:borg48
Comment Utility
I guess this is my confussion.  I have this report that when i open with
crystal reports designer i connect to the database using a odbc dsn named
FinanceReports.  Then i pull up Data Explorer and in the options select
stored procedures.  Which now allows me to pull up a list of stored procedures
which i select one called spr_sel_DraftInfo which takes one parameter named
@Application_Id.
I can run the report in the designer which propts me for the @Application_Id to
enter.

Now in the code is where i'm a bit confused.  I dont really want to use the
ODBC DSN name i would rather pass it a ADO connection. How do i go about
using a ado connection, stored procedure name and pass the 1 parameter to
crystal reports?

Every sample that i have seen shows openning a connection and passing a sql.

Ex.

I open the ADO connection:
_ConnectionPtr spADOConn(__uuidof(Connection));
hr = spADOConn->Open( _bstr_t(strConnection.c_str()), _bstr_t(strUser.c_str()), _bstr_t(strPassword.c_str()), adOpenUnspecified );
if( FAILED(hr))
      throw "Error connection to database";

spReport is my Report object of Crystal Report
and
spApplication is my Application object of Crystal report

How do i go about setting the connection, then the stored procedure then
the 1 parameter value (for ex. 223485 ) that the stored procedures
requires?
0
 

Author Comment

by:borg48
Comment Utility
I think i'm over thought this.  The report uses a stored procedure so all i have to do is pass it the one parameters.
So i did the code below the problem is when i run the program it comes up and asks me to enter the value for the parameter i just set and when i do set it to 1 on and it creates the pdf.  the pdf is corrupted making me think it cant connect to the database to get the data.
Any ideas on how to do this properly?



                        spReport = spApplication->OpenReport(reportFile, varDummy);
                spReport->DiscardSavedData();
                        if( FAILED( spReport->Database->Tables->Item[1]->SetLogOnInfo(_bstr_t(""), _bstr_t("Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Daytona;Data Source=localhost"), _bstr_t("sa"), _bstr_t("3st00g3s")) ))
                              throw "Erorr";


                        _variant_t varInt;
                        varInt.vt = VT_I4;
                        varInt.lVal      = 1;
                        spReport->ParameterFields->Item[1]->AddDefaultValue( varInt );


But either its not logging int or something b/c it keeps asking me to set
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:borg48
Comment Utility
I think i'm over thought this.  The report uses a stored procedure so all i have to do is pass it the one parameters.
So i did the code below the problem is when i run the program it comes up and asks me to enter the value for the parameter i just set and when i do set it to 1 on and it creates the pdf.  the pdf is corrupted making me think it cant connect to the database to get the data.
Any ideas on how to do this properly?



                        spReport = spApplication->OpenReport(reportFile, varDummy);
                spReport->DiscardSavedData();
                        if( FAILED( spReport->Database->Tables->Item[1]->SetLogOnInfo(_bstr_t(""), _bstr_t("Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Daytona;Data Source=localhost"), _bstr_t("sa"), _bstr_t("3st00g3s")) ))
                              throw "Erorr";


                        _variant_t varInt;
                        varInt.vt = VT_I4;
                        varInt.lVal      = 1;
                        spReport->ParameterFields->Item[1]->AddDefaultValue( varInt );


But either its not logging int or something b/c it keeps asking me to set
0
 

Author Comment

by:borg48
Comment Utility
OK I'm getting closer:

spReport = spApplication->OpenReport(reportFile, varDummy);
spReport->DiscardSavedData();

int nTParamSize = spReport->ParameterFields->Count;
string strTParam = spReport->ParameterFields->Item[1]->ParameterFieldName;

if( FAILED( spReport->Database->Tables->Item[1]->SetLogOnInfo(_bstr_t(""), _bstr_t("Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=SampleDB;Data Source=localhost"), _bstr_t("sa"), _bstr_t("password")) ))
or
if( FAILED( spReport->Database->Tables->Item[1]->SetLogOnInfo(_bstr_t("wshb1890"), _bstr_t("SampleDB"), _bstr_t("sa"), _bstr_t("password")) ))
      throw "Erorr";

_variant_t varInt;
varInt.vt = VT_I4;
varInt.lVal      = 1;

// Not using this causes type mismatch
//spReport->ParameterFields->Item[1]->AddDefaultValue( varInt );


hr = spReport->ParameterFields->GetItemByName(_bstr_t(strTParam.c_str()))->AddCurrentValue(varInt);

IExportOptionsPtr spExportOptions;
spExportOptions = spReport->ExportOptions;
spExportOptions->FormatType = (CRExportFormatType)type;
ProcessExportPages(spExportOptions, type );
spExportOptions->DiskFileName = destinationFile;
hr = spReport->Export();

It doesnt ask me for the parameter anymore but when it writes the pdf its 0 bytes so there is something
wrong.  I started a small test with a database called SampleDB with one table and 1 stored procedure:

CREATE PROCEDURE [dbo].[spRetrieveApps]
  @Type int
AS
      select * from Applications where IdType = @Type;
GO

So not quite sure what the culprit is and require some help
thanks

0
 

Author Comment

by:borg48
Comment Utility
I even created a small vb app it does seem the problem is when i pass the parameter value to the report:

    Dim App As New Application
    Dim Rep As New Report
   
   
    On Error GoTo Err
    Set Rep = App.OpenReport("C:\Projects\ReportMill.Interfaces\docs\RMillTest.rpt")
   
   ' Set Rep = App.OpenReport("C:\Projects\ReportMill.Interfaces\docs\Balance.rpt")
   Rep.DiscardSavedData
   
    Dim nItem As Integer
    Dim strPara As String
   
    nItem = Rep.ParameterFields.Count
    strPara = Rep.ParameterFields(1).ParameterFieldName

' Either one fails so cant see a difference    
    Call Rep.Database.Tables(1).SetLogOnInfo("", "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Daytona;Data Source=SampleDB", "sa", "password")
    or
'    Call Rep.Database.Tables(1).SetLogOnInfo("wshb1890", "SampleDB", "sa", "password")

    Rep.ParameterFields.GetItemByName(strPara).AddCurrentValue (1)
   
    Rep.ExportOptions.DiskFileName = "c:\pdf_report.pdf"
    Rep.ExportOptions.DestinationType = crEDTDiskFile
    Rep.ExportOptions.FormatType = crEFTPortableDocFormat
    Rep.Export False

But if i leave out the both SetLogOnInfo and ParameterFileds and used a non database report it works great.
If i leave out the ParameterFileds so it will ask me to enter the value it still fails.  Again to me it seems that i'm using the wrong code to set a stored procedure.

Help....
Thanks
0
 

Author Comment

by:borg48
Comment Utility
Finally figured it out:

                        IApplicationPtr spApplication(__uuidof(Application));

                        _variant_t varDummy;
                        varDummy.vt = VT_EMPTY;

                        _variant_t varPrompt;
                        varPrompt.vt = VT_BOOL;
                        varPrompt.boolVal = false;

                        spApplication->LogOnServer(_bstr_t("P2SSQL.DLL"), _bstr_t("wshb1890"), _bstr_t("Daytona"), _bstr_t("sa"), _bstr_t("3st00g3s"));
                        spReport = spApplication->OpenReport(reportFile, varDummy);
                spReport->DiscardSavedData();
                        spReport->EnableParameterPrompting = varPrompt;

                        _variant_t varInt;
                        varInt.vt = VT_I4;
                        varInt.lVal      = 0;

                        spReport->ParameterFields->Item[1]->SetCurrentValue(varInt);

                        spReport->ExportOptions->DiskFileName = "c:\\pdf_report.pdf";
                        spReport->ExportOptions->DestinationType = crEDTDiskFile;
                        spReport->ExportOptions->FormatType = crEFTPortableDocFormat;
                        spReport->Export(varPrompt);
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
PAQed, with points refunded (110)

modulo
Community Support Moderator
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now