Inserting and Updating Data with Crystal Reports

A powerful feature of Crystal Reports is that it allows full access to the SQL language for manipulating databases. This means that not only can you pull information out of a database for printing, but you can also make changes to the live data in the database as the report prints. You can insert new records into the database as well as modify existing records. If you’ve always thought of reports as a passive viewer of data, then this changes that idea completely.

With this power comes the responsibility to use it correctly. Prior to using a report to modify data, you should always consult with your database administrator so that he/she knows what you plan on doing, as well as making sure you go about it the proper way.

If this is used incorrectly, you could delete or overwrite critical data on the server. And you’ll probably get invited to meetings with people at a much higher level than you whom you would have been happier never meeting. So please be careful with the following information.

Giving your report the ability to modify the database involves a few different steps and has a few rules to it. Let’s look at the rules involved in creating an updateable command object and then discuss each one in detail.

1. The database must support using two SQL statements in one command object.
2. The command object must return data.
3. Updateable command objects must placed be on a subreport.
4. Place the subreport in the appropriate section

The database must support using two SQL statements in one command object.

As we’ll see later in the tutorial, updateable command objects require using two separate SQL statements in one command object. The first statement modifies the data and the second statement returns a dummy value. Databases like MS Access only allow one SQL statement per command object. Thus, it can’t use updateable command objects. SQL Server does allow this.

The command object must return data.

The nature of an updateable command object is that its job is to perform an action on the database server. This could be inserting or updating data. It isn’t worried about returning data. Unfortunately, this causes a conflict with the way Crystal Reports processes data sources. When Crystal Reports generates a report, it tries to optimize the processing time by analyzing each table in the data source. If it finds that one of the tables doesn’t return any data, it drops that data source. It only keeps tables that return data that gets printed on the report.

As an example, assume there is a table with only one of its fields being used on the report. You later delete that field and forget to remove the table from the data source. When you run the report, Crystal Reports realizes that the field has been deleted and that the table isn’t needed anymore. It removes the table from the database request. This makes the report more efficient by not requesting unnecessary information.

The problem this creates for updateable commands is that since they don’t return any data, Crystal Reports assumes that they aren’t necessary and skips them. They don’t get executed and your data never gets updated. The way to fix this is to put some type of SELECT statement in the command object to create a dummy field and print this field on your report. Since you are selecting a dummy value just to appease Crystal Reports, you can hide it on the report so that it isn’t shown. What I like to do is use the SELECT statement to return the number 1.

SELECT 1 AS Dummy

This is fast because it doesn’t have to query the database for any information. I also use the AS keyword to give the field an alias. This makes it easy for me to remember that this is just a dummy number that was created to make sure the command is executed.

Updateable command objects must be placed on a subreport.

If you create an updateable command object and put it on the main report, it will run fine, but Crystal Reports will run it twice. For reasons beyond my understanding, Crystal Reports always executes updateable command objects twice.  This results in your action being executed twice. So if you insert a record, it gets inserted twice. If you update data, it gets updated twice. Not only is this inefficient, but it can corrupt your data. The way around this problem is to create a subreport and call the command object from the subreport. The subreport will only get called once and there won’t be any surprises.

Place the subreport in the appropriate section

You have to be careful when deciding in which section to place the subreport that contains the command object. It gets called every time that section is displayed. If you just want the command to be executed once, then put the subreport in the Report Header or Report Footer. If you have a command that updates the data each time a new record is printed, you should put the subreport in the Details section. If you put the subreport in the wrong section, it is possible that it will either get called too many times or not enough.

If your report has the option Save Data With Report turned on, then the SQL statements in the command object won’t be called when you view the report. The command object is only called when the report queries the database for a new result set. Since the report is using saved data, it doesn’t need to query the database for a new result set. To force the report to execute the SQL in the command object you either have to turn off the option to save data with the report, or manually refresh the report data.

Let’s put all these ideas together and try them out for ourselves with a tutorial. This tutorial creates a report history that tracks every report that was printed and when it was printed.