Saturday, May 05, 2007

Crystal Reports "Query Engine Error"

You want to create a report with Crystal Reports and you create that weird ADO.Net DataSet, then you add a Crystal Report, you select the tables you want from the created DataSet, then you drag the Id references to create the table links, you go through all that weird Report Expert, you add a Crystal ReportViewer to a Windows Form and you press F5! And you get "Query Engine Error".

Well, two main reasons for this are explained in this nice article: "Query Engine Error" With Crystal Reports .NET, but you just created the report, there is no way you changed the XSD or the name of the tables.

I tried a lot of things until I found out what was going on. You see, I had this tables that had an "Id" column and a many-to-many table that had "UserId" and "MenuId" columns. In order to link them, I did what I also did in the XSD, I dragged the Id (primary key of each table) to the UserId and MenuId columns. That was the problem! You have to do it the other way around, drag the Foreign Key columns to the Primary Key columns.

Hopefully, you would have read this article before wasting hours to find out what the hell is that error and where it comes from... It happened to me with Crystal Reports 9.0 and Visual Studio 2003.

Oh! And don't bother to link the tables in the DataSet XSD, since Crystal Reports seems oblivious to that.