Trying to pass a parameter to the IReport object

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.
borg48Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

borg48Author Commented:
This is the correct parameter:
_bstr_t bsString("@Application_Id; 2454276; 1");

Do i need to create one parameter for each?
0
mnyeCommented:
0
mlmccCommented:
Did you add a parameter to the report?

mlmcc
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

borg48Author Commented:
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
borg48Author Commented:
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
borg48Author Commented:
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
borg48Author Commented:
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
borg48Author Commented:
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
borg48Author Commented:
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
moduloCommented:
PAQed, with points refunded (110)

modulo
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.