Edit Parameters data type through programatically

[Former Member]

Hi , I am working on to edit / modify parameters data type in SQL command programmatically using c#? Can please anybody let me know how to edit parameters data type which are there in the command through code. currently I can find Command like this foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table tmpTbl in reportClientDocMtf.DatabaseController.Database.Tables)                 { if (tmpTbl is CommandTable)                         {                             CommandTable cmdTbl = (CommandTable)tmpTbl; } }

[former_member11696 · Accepted solution]

Moved to .NET Forum.

Search for Doc 70646, there is a sample app attached on getting/setting parameters.

Don

[Former Member · Accepted solution]

Hi Don,

Thanks a lot for your reply. I went through the Document 70646 as you have mentioned in your reply. I feel your are trying to set parameter value.

There is a  Saying  " A Picture is Worth a Thousand Words" . So I would like to attach Screen Shots of my concern. Please help me on this.

We have recently migrated our database structure , all the "Integer" columns are converted to GUID. Since Crystal Reports doesn't understand GUID we are converting all the data type to string. For Parameter Fields through Manually we can do like this.

Through Code

Using Modify method I can Modify the parameter field data type as shown in the code above.

But for the parameter fields which is inside SQL Command I am having problem.

through manually I can do like this.

If I try to follow the same method to modify this type of parameter fields data type , It throws an error saying "Stored Procedure parameter cannot be modified."

Can you please let me know any solution on this. Currently I am using

[Former Member · Accepted solution]

Currently I am using Crystal report Version 13

[former_member11696 · Accepted solution]

Ah, that would be different.

Since you converted your field type you need to Clone the field object and replace it with the updated object from the Command.

In your code where to use .Modify change it to .Clone(), then you need to delete the parameter and then use the .Add to put it back into the report.

Also if the field is used in the report it too must be cloned and replaced.

What is happening is when changing field types the Field mapping UI will pop up in CR Designer. The problem is in Code there is no access to the Field Mapper so the engine simply deletes the field which of course will cause other errors to generate.

If you don't have many reports I suggest using CR Designer to update them rather than doing it in code. Will be much easier and a one time thing.

CR assumes the data field types never change, if they do it can cause all sorts of problems. Safer to update them manually first.

Don

[Former Member · Accepted solution]

Hi Don,

Thank you for the explanation! I am happy that you have understood the problem. I will try out your suggestion.

Even I had tried of similar approach like adding new command to table and removing the old one by modifying parameter data type.

As you said since the fields are linked in the reports it was throwing the same error, asking us to remove the linked fields from the report.

But I did not get how to remove linked fields.

I did not try clone() thing. It would be of really great help if you could provide me any code snippet or document like how to clone the table or command then removing and adding by modifying parameter data type as you have explained.

Also either by manually or by code can you please tell us how to remove the linking fields in the report.

As you have mentioned we are trying manual conversion also for testing purpose so that we can make sure everything goes well. Meanwhile we are creating a tool kind of application where it converts more than 10 reports which is of similar kind. Please help !

[former_member11696 · Accepted solution]

How many reports do you have?

I suggest using CR Designer to update the reports, it will be much easier in the long run. You can spend a lot DEV time and resources trying to convert in code.

SDK help files are on the Overview Tab on the left. Links to the SDK help files and samples.

Don

[Former Member · Accepted solution]

Hi Don,

There are more than 10 reports.

I dint find the links to SDK help files and Samples.

Thank You.

[former_member11696 · Accepted solution]

Well you did not look very hard then...

[former_member11696 · Accepted solution]

It will take you an hour at most to update 10 reports, it will take you 2 plus days to update the same reports in code, including QA testing etc.

[Former Member · Accepted solution]

Hi Don,

I have cloned the field object, but how do I delete the parameter through code?

I tried with remove().

Also I have gone through the sample SDK help files , I found retrieving and changing the text of an SQL command. Not exactly modifying parameters inside an SQL command.

[former_member11696 · Accepted solution]

You can edit Command all you want, just make sure the SQL is valid:

// check if the report is based on a Command and if so then display the SQL. This causes a huge delay opening report

btnReportObjects.AppendText("");

dtStart = DateTime.Now;

int dbConCount = rptClientDoc.DatabaseController.GetConnectionInfos().Count;

difference = DateTime.Now.Subtract(dtStart);

btnReportObjects.Text += "GetConnectionInfos().Count took: " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + "\r\n";

String DBDriver = "";

for (int x = 0; x < dbConCount; x++)

{

    try

    {

        DBDriver = rptClientDoc.DatabaseController.GetConnectionInfos()[x].Attributes.get_StringValue("Database DLL").ToString();

        btnDBDriver.Text += DBDriver + " :";

        if (((dynamic)rptClientDoc.Database.Tables[0].Name) == "Command")

        {

            CrystalDecisions.ReportAppServer.Controllers.DatabaseController databaseController = rpt.ReportClientDocument.DatabaseController;

            ISCRTable oldTable = (ISCRTable)databaseController.Database.Tables[0];

            btnSQLStatement.Text = "Report is using Command Object: \n" + ((dynamic)oldTable).CommandText.ToString();

            btnSQLStatement.Text += "\n";

            IsLoggedOn = false;

            IsCMD = true;

        }

        if (DBDriver.ToString() == "crdb_bwmdx.dll")

            IsBEX = true;

    }

    catch (Exception ex)

    {

        //btnDBDriver.Text = "ERROR: " + ex.Message;

        btnDBDriver.Text += "Main Report has no Data Driver";

    }

}

Need to use RAS to get/set the SQL in a Command.

Try doing this in CR Designer, change the parameters, the work flow will be the same in code...

Don

[Former Member · Accepted solution]

Why I wanted to code the report conversion ?

End users of the application have created their own .rpt files (count is not known).  Recently the DB schema changed and all UID (Int) fields became GUID fields.

We wanted to write a tool that updates all existing .rpt files and work for the new DB schema.

Now I realise, there are many types of reports, that include data comes from table, views, command/stored procedures, report paramenters...each having its own complication...

You have suggested to go with manual conversion.  Is that the only way? Any other method that can ease my job ?

[former_member11696 · Accepted solution]

Ah, good to know. The problem is the field mapping...

In CR Designer a Mapping UI will pop up when you Verify Database and you can then map the old field to the new field.

In CR for VS there is no Mapping API's to do the same, CR for VS has Mapping set to Automatic so when you log on it simply deletes the fields. It does not have the ability to map old to new.

So unfortunately when they made that change they also forced themselves into manually modifying EVERY report that uses that table.

Your only option is to open each report in CR Designer and manually map the field.

Don

[Former Member · Accepted solution]

Hi Don,

As per your reply, I have cloned command table and removed the parameter field from the command and updated the modified parameter field to the command.

boNewCommandTable.Parameters.Remove(0);

boNewCommandTable.Parameters.Add(parameterField);

But there are other parameter in the report which are not inside Command but it is of type storeprocedure.

"CrParameterFieldTypeEnum.crParameterFieldTypeStoredProcedureParameter" - this is how i'm checking the parameter type.

If I try to modify this type it says it cannot modify storeprocedure parameter.

And only report type parameter can be modified.

But deleting the parameter through report client document, like below

reportClientDocCalcert.DataDefinition.ParameterFields.Remove(iParamMainRptCount);

Its removing the parameter , but after adding the modified parameter like below,

reportClientDocCalcert.DataDefinition.ParameterFields.Add(newparam);

No changes are reflecting. How do I modify parameter type storeprocedure.?

[former_member11696 · Accepted solution]

You can't, the Store Procedure Parameter type means it's from the external data source.

Since you are using a Command object it's hard coded into the report. Same as if you changed a Stored Procedure Parameter, you would need to manually update the report in CR designer, CR really doesn't like it when you change the database structure since we keep that in the RPT file and expect it to not change. We assume your Data structure is finalized.

I have not tried it but you will likely need to close the report object itself and then try changing the Command Parameter since you can't change it due to the report design is expecting it not to change.

You may however run into a problem with the Auto field Mapping function, the Engine will simply delete fields that would require manually mapping it.

Try changing the Command in CR Designer, use the same work flow in Code as you did in CRD to make it work.

Don