Could not find installable ISAM

Small tip!! Some times things so small take up so much of your precious time that you start getting frustrated. The following is a common error people get when trying to connect with an Excel or Access DB using the Oledb:

Could not find the installable ISAM.

I agree there could be many reasons for this error to occur. For example, the drivers on your system are corrupted, in that case, you will have to reinstall the drivers or may the registry entries are corrupted. For that you will have to modify the registry. (If that is the case, you may want to look at the MS KB article that provides the solution). In my case, the problem was that I was using the latest Office 2007 and I was trying to connect to an Excel file using the following connection string:

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Tempmetadata.xls;Extended Properties=Excel 12.0";

Can you locate the problem in the connection string? Give it a try! The string is syntatically correct then where is the problem? The problem is with the "Extended Properties". If I replace "Excel 12.0" with "Excel 8.0" (which is the correct version actually), the problem goes away. I was about to modify my registry when luckily I noticed the correct version and modified my connection string and everything started working fine.

The error message that one gets is so generic that anyone will be easily deluded and this wastes time.

Good luck to anyone stuck with the same or similar problem!

Using "AND" in SPQuery

Level: Beginner

Ok, here is a small tip for the beginners. If you are trying to search records based on two values, for example, "select * from list where a=1 and b=2" then how will you write its equivalent using the SPQuery object. Here is the sample:

SPQuery query = new SPQuery();
query.Query = "<Where><And><Eq><FieldRef Name='Field1'></FieldRef><Value Type='Number'>1</Value></Eq><Gt><FieldRef Name='Field2'></FieldRef><Value Type='Number'>0</Value></Gt></And></Where>";

This query will find records where Field1 = 1 and Field2 > 0.

So you have learnt two new things, using "And" and "Gt".

What is the datatype of the Yes/No field?

Value type has to be provided when writing a query for the SPQuery object. How do you access a Yes/No field or let's put it in another way! What is the data type of the Yes/No field in SharePoint? The data type is "Boolean" and the value has to be provided as 1 or 0. Providing a value of Yes or No or True or False will not work. Ok, sometimes the query fails even if you provided the value as 1 or 0. You get a strange error. Following is the error message:

"One or more field types are not installed properly. Go to the list settings page to delete these fields"

To fix this error, you will have to use the internal name of the field in the spquery. Yes, internal names can be different from the names displayed in the site. For example, internal name for the field "My Field" can be "My_x0020_Field".

Here is the sample code:

SPQuery query = new SPQuery();
query.Query = "<Where><Eq>FieldRef Name='My_x0020_Field'></FieldRef><Value Type='Boolean'>1</Value></Eq></Where>";

This will search for records where "My Field" has the value of 1 (Yes).

Easiest way to find the internal name of the field is to open the "New" form in browser and then view it's source. Right-click any where in the "New" form to open the context menu and select "View Source". You will find the fields along with their internal names near the end of the source file.

-SSA