I have been attempting to open a dbf file using oledbconnection and oledbcommand in my Web Service in C#.

I have been able to open a connection using the following code.

However when I attempt to run the ExecuteReader line below, I get the following message:

"Unexpected error from external database driver (9218)."

string strConnStr;
strConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\perchhold\\OnlineOnlineData\\SETDBFS;Ex tended Properties=DBASE IV;";

OleDbConnection dbConn;
dbConn = new OleDbConnectio(strConnStr);
if(dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}


try
{
string strSQL;
strSQL = dbConn.DataSource;
strSQL = "Select * from weblogin.dbf";
OleDbCommand cmdLogins = dbConn.CreateCommand();
cmdLogins.CommandText = strSQL;
OleDbDataReader myReader = cmdLogins.ExecuteReader();
}
catch(Exception e)
{

}


I have also tried to open a connection with the following connection string. I get the following error on the creation of the object:

"The .Net Data OLE DB Provider(System.Data.OleDb) does not support the MSDASQL Provider, Microsoft OLE DB Provider for ODBC Drivers. "


string strConnStr;

strConnStr = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=SETDBFS";

OleDbConnection dbConn;
bConn = new OleDbConnection(strConnStr);
if(dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}



Anyone have any advice?

Thanks,

HH

Your first mistake was to try and open a dbf through odbc ;) They're a nightmare, having been working on this sort of thing for ages.

First off, however, I'd try using the microsoft odbc driver for dbase - dsn connection is as follows;

"Provider=MSDASQL.1;Persist Security Info=False;Data Source=dBASE Files;Initial Catalog=" & path

whereas the dsn-less connection string is as follows;

“Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;” & _ Dbq=” & path

I find errors occur more often, however, when using the dsn-less connection than when using the dsn connection. Even using the odbc driver, you'll find it regularly throws up the error you experienced and even when it does work, exhibits very sluggish performance. I'd advise the following;

1. Retry the open attempt several times, because sometimes if you try it again it works when previously it didn't (I did say it was rubbish!)

2. Try adjusting the select command - enclose the table names in ` characters (this is different to the inverted comma, ' - on a UK keyboard it is the key to the left of 1, otherwise I'd do a cut and paste). Also try square brackets around table names([]).

3. If you're trying to specify dates, these go in the following format (date is specified yyyy-mm-dd)
 SELECT `Field1`, `Field2` FROM `Table1`
 WHERE `Field2` >= { d '2002-10-01' }

4. If possible, try and make the table smaller (if you have access to dbase to open the dbf, do a COPY TO NewTable FOR ...) because I've also found that if you get above 50-60,000 rows in a dbf selecting from it via ODBC gets even flakier!

Hope this helps

Phil

Also, you've tried to access ODBC through the OLEDB provider - in .NET, there is a provider for ODBC so use that. In this case, go with my DSN-less connection.

Phil