Working with dates in Crystal Reports with a Visual FoxPro database

How to deal with 'DATETIME not found' errors when using the FoxPro OLE DB provider with Crystal Reports.

By Mike Lewis

If you are using the Visual FoxPro OLE DB Provider with Crystal Reports, you might have seen a pair of unexpected error message when you try to set select records based on a date field.

The first error message simply says "Cannot open rowset". When you dismiss this message, you see a more detailed message, with the following text (see also Figure 1):

Failed to open a rowset.
Details: ADO Error Code: 0x80004005
Source: Microsoft OLE DB Provider for Visual FoxPro
Description: SQL: Column 'DATETIME' is not found.
Native Error: 806 [Database Vendor Code: 806]
DATETIME not found error message

Figure 1: This error message might appear when
you try to select a record based on a date field.

These messages appear when you are using the Record Select Expert to select on a date field in your FoxPro database.

Why this happens

The Show SQL Query window (which you access from Crystal's Database menu) gives us a clue to why this is happening. The window will show something like this:

SELECT events.id, events.startdate, events.title
FROM  events events
WHERE events.startdate=CONVERT(DATETIME, '05-01-2011', 110)

The important item here is the WHERE clause. Essentially, this tells us that CR is asking the FoxPro OLE DB Provider to convert a date from a character string ('05-01-2011' in this example) to a datetime. It's using the CONVERT() function to do so.

The problem is that CONVERT() is not a Visual FoxPro function. In fact, the syntax is specific to Microsoft SQL Server, which is why it fails in this case. On seeing this function, VFP first tries to resolve DATETIME; since this is not delimited, it assumes that it's a column name - hence the "column not found" message.

What to do about it

Fortunately, there are a couple of workarounds for this problem. The preferred option is to use a SQL expression as the record selection criterion. Staying with this same example, you could code the SQL expression like this:

events.startdate = date(2011, 1, 5)

In the Record Select Expert, you would then select records where this expression is true. (For further information about how to use SQL expressions in this way, see Using SQL expressions in Crystal Reports.)

The point about this SQL expression is that conforms to Visual FoxPro syntax, and therefore doesn't produce an error.

But if you're not familiar with VFP syntax - or if you don't feel comfortable writing SQL expressions in Crystal Reports - you have another option.

Start by using the Record Select Expert to establish the selection criterion in the usual way. So you will select the field that you want to base the selection on (events.startdate in this example), then choose the selection type (such as "is equal to"), and then either enter the relevant date in the box to the right of dialog, or pick a date from the drop-down list.

Next, click the Show Formula button. This will open an editing box which displays a formula, similar to this one:

{events.startdate} = Date (2011, 05, 01)

What you need to do is to edit the formula, as follows:

{events.startdate} = DateTime (2011, 05, 01, 0, 0, 0)

In other words, change the Date() function to a Datetime() function, and add three zeros to the parameter list as place-holders for the time element (see also Figure 1).

Crystal Reports Select Expert

Figure 1: Record Select Expert showing the corrected formula

Click OK to come out of the Record Select Expert, then refresh the report in the usual way. You should now see the correct records - with no error messages.

If you now re-visit the View SQL Query window, you will see that the WHERE clause is missing. CR is no longer telling VFP to perform the record selection, but will handle that task itself. So there is no erroneous syntax being sent to the database - and no error messages.

December 2011

Please note: The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly with your sites or applications.

If you found this article useful, please tell your friends: