- Windows NT 4.0
- Windows 2000
- Windows XP
- Windows Server 2003
- Windows Vista
- Windows Server 2008
- Windows 7
When you attempt to connect to a Microsoft Access database using the ODBC provider Microsoft.Jet.OLEDB.4.0 you receive the error message:
There are a number of issues that can cause this error, they are poorly documented elsewhere online but the information is there; connection string, registry values slightly offset and so fourth. This document deals with one specific issue which is related to the idea of this being caused by an incompatible type value.
The chances of you running into this are quite extreme, however it is an issue in JET (Microsoft Access) that doesn’t exist in Microsoft SQL Server and thus if you are dealing with code transitioning you may want to know about the problem in this very extreme and unlikely situation.
You shouldn’t need to reboot to solve the problem, it didn’t in my case. However, if nothing happens do restart before progressing to the next step.
The following SQL Statement will execute on Microsoft SQL Server and return a RecordSet however the same SQL on JET 4.0 will result in the error message outlined above.
No, it isn’t the T-SQL LEFT() statement that is the problem. The issue lies in the “null AS studentPhotoBLOB”.
The idea behind this is that space has been reserved in the record set. Why would you want to do this? If you want to drop the record set directly into an array, then ADO can do this for you using the GetRecords method. However if you want to change the structure you have to re-parse the array and relocate the data into a new structure. You can inset a null named record and use the space in the GetRecords array later for other purposes.
The problem here is that null is not a data type that is supported by JET; it is supported by Microsoft SQL Server however.
The simplest way to remedy the situation is to either change the SQL or dynamically detect JET to change the variable type to a commonly supported data type, for example a String.
This is naughty, however in a weak typed language such as ASP it isn’t a real problem given the situation outlined above. The use of ” instead of null forces the data type of the column into a string and thus it can be parsed by JET.