Excel ISAM ADO.Net problem

[matt p]

When connecting to an .xls file in Visual C#, I get the
error 'Could not find installable ISAM driver.' I have
implemented solutions in KB article 283881, but still get
the message. I have reinstalled Office XP, Jet 4.0 SP8,
no luck.


string strConnect =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\300Input.xls;Extended Properties=Excel
10.0;HDR=Yes";


OleDbConnection oConn = new OleDbConnection(strConnect);
try
{
oConn.Open(); //ISAM error here
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message);
}

Help Please!

Tx,
Matt

 

[Miha Markic]

Responded in thread below.

 

[Guest]

I had this exact same problem. As with many error
messages, it doesn't point to the real error, the lack of
quotes around your Extended Properties=<value>.

This example illustrates the "What mother never told you
about connection strings" lesson I learned (the hard way):

Dim strConnectionString As String
Dim sbrConnectionString As New StringBuilder

' build the connection string that will allow us
to treat the Excel worksheet as a table.
' Example: note the extra quotes around the values
in Extended Properties
' Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Book1.xls;Extended Properties="Excel
8.0;HDR=NO;IMEX=1;"
With sbrConnectionString
.AppendFormat("Provider={0};", Me.Provider)
.AppendFormat("Data Source={0};",
ExcelWorkbookName)
'WARNING: This part gets very tricky. The
value attribute has an extra pair of surrounding double
quotes!
.Append("Extended Properties=")
.Append(ControlChars.Quote) ' start of tricky
part
.Append("Excel 8.0;")
.Append("HDR=No;")
.Append("IMEX=1;") ' always treat contents of
cells as text, which gives us full control/responsibility
for casting to numeric when ncessary
.Append(ControlChars.Quote) ' end of tricky
part
strConnectionString = .ToString
End With