Crystal Reports…where did you go wrong?

Many moons ago, when you could write useful software on a computer with less processing power than my last cellphone, there was a reporting tool called Crystal Reports that was incredibly useful for those of us who spent our working lives using tools such as Visual BASIC 3 to write windows applications.  It had a few gremlins, but they tended to be the sort of thing that you wrote in your notebook and turned to when you deployed an application that used CR…sort of:

“All report files verified against database…check.  All report files in distribtion package….check.  All CR runtimes in distribution package….check. ”

And that was it – the whole thing fitted on a couple of floppy discs (remember those?  If not, the contents of 400 of them will fit on a CDROM) and after I got my checklist sorted I was good to go and was happy to use Crystal Reports whenever I needed a quick and straightforward reporting solution.

The years passed and I found myself working on various projects which either used different reporting technologies or that didn’t involve me with reporting systems, and I gradually lost track of Crystal Reports until a couple of years ago when I found myself having to use the package again.  And most of the time it’s fine – but when used with Visual Studio to develop and deploy Internet Web sites and applications….oh dear.

As always you tend to blame yourself for being stupid with these sorts of things.  You are, after all, dealing with a couple of packages that could easily have knocked you back over £600 if you buy the full packages.  So, you kind of think that by following the instructions, you’ll get a working system without any real problems.  And, if it all goes pear-shaped, you assume that somewhere along the way you’ve dropped a clanger, so you repeat stuff, reinstall stuff, restart machines, uninstall stuff, sacrifice chickens…the usual persistent efforts to solve problems adopted by software developers.

Of course, we’re now aided by Google (how did we manage to resolve these issues before the Web?  I really can’t remember, but software seemed to go wrong less frequently back in the early 1990s) and so I did a quick Google of:

  1. Why Crystal Reports viewers failed to run properly when added to a web page, even if you used the exact code on Microsoft’s and Business Objects’s web sites?  Which led to….
  2. Why a particular folder called aspnet_client wasn’t being created when I created a new website.  Which led to….
  3. Why, when I manually added the folder (again, as per the instructions) , things still failed. 

6 hours of my life disappeared down the maw of this problem – 6 hours that I could happily have spent doing other things.  Eventually, rather than spend my life going round and round in ever decreasing circles (or re-installing EVERYTHING – not something I wanted to do on someone else’s server) I came up with what I ended up describing on Twitter as a ‘wanky bodge’ to work around the problem. 

What was incredibly scary was the number of times the issue turned up on Google with the comments ‘Don’t know how to fix, it sorted itself out after re-installing, Couldn’t fix it and so didn’t use Crystal Reports’.  It’s not just me – looks like the combination of Crystal Reports XI and some instances of Visual Studio (but not all) and some Web sites on the same server (but not all) can give rise to the situation where it’s impossible to view a report without bodging things. 

Guys…it shouldn’t be  like this.  There’s an old joke that says that if we built bridges the way we built  software we’d never dare to drive across them.  I think that there’s a little too much truth in that joke.

ASP.NET and Oracle – how to stay sane!

I’m currently doing some development work using ASP.NET against an Oracle database.  I have to say that I’ve had more frustrating development experiences, but most of those involved mainframe computers or…oh yes….Visual BASIC 6.0 against Oracle.  Just what is it about Oracle and Microsoft?  Gah!

Anyway – rant over.  In this piece I’d like to share a few useful tips for developing with ASP.NET and Oracle if you’re used to developing with ASP.NET and SQL Server.  There’s nothing magic here, and I’m no expert, but hopefully these pointers might assist anyone else in the position that I’ve found myself in!

Identity Fields

One thing that looks missing from Oracle in the first instance is the ‘Identity’ field that is often used a Primary key field in SQL Server.  It IS possible to implement this in Oracle – one has to use what’s called a ‘Sequence’ and either include a trigger on the ID field of the table to give you the sequence number added automatically or remember to add it via the INSERT command:

    MAXVALUE 999999999999999999999999999
    CACHE 20;

This generates a sequence called table_seq, starting at 1, incrementing by 1 each time, and going up to a VERY large number!  The CACHE 20 line tells Oracle to generate a cache of 20 values from the sequence.  To use this sequence after creation, you can access it via an INSERT command as follows:

INSERT INTO datatable
(id, name)
(table_seq.nextval, 'Joe Pritchard');

the ‘id’ field is the PK field of the table, and the table_seq.nextval gets the next value from the sequence.  To create a truly ‘auto incrementing’ PK field, you create a trigger on the table:

create trigger datatable_trigger
before insert on datatable
for each row
select table_seq.nextval into from dual;

Run this and then you can add a new row to the table without specifying the id field:

INSERT INTO datatable
( 'Joe Pritchard');

Boolean Fields

Oracle doesn’t support them.  the best approach I’ve found is to have an integer field and treat 0 as false and 1 as true.  This then works well with ASP.NET checkboxes.  For example:

<asp:TemplateField HeaderText=”Is Admin.” SortExpression=”IsAdministrator” > 

<ItemTemplate > <asp:CheckBox runat=”server” ID=”IsAdministrator” Text=’<%# Bind(“IsAdministrator”) %> Checked=’<%# Bind(“IsAdministrator”) %> />  


Don’t forget the provider Name

When setting up a SQLDataSource control, don’t forget to specify the provide Name in the ConnectionString.  If you do, the error message obtained is not exactly meaningful at first glance, referring as it does to Unicode!  


<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:ConnectionString %> ProviderName=”<%$ ConnectionStrings:ConnectionString.ProviderName %>

This also requires you to specify the Provider in the ConnectionString:

 < add name=ConnectionString connectionString=Data;User ID=jp;Password=test;Unicode=True providerName=System.Data.OracleClient/>

Watch table and field name lengths

This can be extremely frustrating.  And I mean extremely!  If you are likely to find yourself explicitly specifying the table name and the field name in a SELECT statement, for example, then the combained length MUST NOT exceed 30 character (this includes the ‘.’ separating table and field – so keep table and field names as short as is practicable.

Quote marks around table and field names

When putting SQL statements together for use by SQLDataSource or other ASP.NET controls that use the OracleClient provider, don’t forget to surround the Oracle field and table names with quotation marks:

SELECT "id", "name" FROM "names"


SELECT "names"."id", "names"."name" FROM "names"

Parameter Handling

If you are using Parameters with a SQLDataSource control, don’t forget that the OracleClient uses a colon instead of the ‘@’ sign:

DeleteCommand=’DELETE FROM “moad_agrippa_users” WHERE “UserID” = :UserID’

The other thing to note is that the parameter does not require quotation marks around it.

I hope this piece has been useful – it will act as an aide-memoire for me the next time I come back to work on Oracle / ASP.NET sites!