Solved

Trying to pass a parameter to the IReport object

Posted on 2003-11-26
11
1,010 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 

Author Comment

by:borg48
ID: 9823242
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
ID: 9824700
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 9824794
Did you add a parameter to the report?

mlmcc
0
Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

 

Author Comment

by:borg48
ID: 9828389
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
ID: 9834350
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
ID: 9834351
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
ID: 9834429
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
ID: 9834528
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
ID: 9834701
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
ID: 11863390
PAQed, with points refunded (110)

modulo
Community Support Moderator
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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