Create index command hangs up on dBase III .dbf file

Question

Hello!

I have dBase III .dbf file 17M of size.

I create index on it next way:

// Extended Properties=dBASE IV or dBASE III - result is the same
string oleDbConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\;Extended Properties=dBASE IV;User ID=;Password=;";

private void CreateIndex()
{
 using (var oleDbConnection = new OleDbConnection(oleDbConnectionString))
 {
  oleDbConnection.Open();

  string createIndexCmd = "CREATE INDEX [INDNAME] ON [TBLNAME]([FIELD1], [FIELD2]);";
  using (var oledbCommand = new OleDbCommand(createIndexCmd, oleDbConnection))
   oledbCommand.ExecuteNonQuery();
 }
}

 

As result I get TBLNAME.MDX index file.

Problem: on few Windows XP SP3 computers this code generates INDNAME.NDX (not MDX(!)) file and hangs up forever:

oledbCommand.ExecuteNonQuery(); // hang's up forever, despite default 30 timeout seconds 

All these computers have latest OleDb Jet4.0 provider (SP8) and even newest versions.

Also I tried to use ODBC provider:

 

 

string odbcConnectionString = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\path\;";<br/> <br/>using (var odbcConnection = new OdbcConnection(odbcConnectionString))
 {
  odbcConnection.Open();

  string createIndexCmd = "CREATE INDEX [INDNAME] ON [TBLNAME]([FIELD1], [FIELD2]);";
  using (OdbcCommand odbcCommand = new OdbcCommand(createIndexCmd, odbcConnection))
   odbcCommand.ExecuteNonQuery();
 }

 

And I tried to use ADODB2.8 COM component... but result is the same:

on few computers everyting is ok, on others - hangs up and .ndx file instead of .mdx.

.ndx file is filled with data 7.5M of size and hangs up, .mdx file - 10M bytes and doesn't hangs up.

 

What is the problem of this strange behaviour?

 

With best regards, Alexei.

Answer 1

Thank you for posting.

Typically, ExecuteNonQuery method was hung because some previous operation didn't commit. So we suggest you can use TransactionScope in your project to ensure previouse command was succeeded. Please check this document about TransactionScope class for more information. http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

Please feel free to let us know if you have any question.

Best Regards,

---

Larcolais Gong[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Larcolais Gong, thank you for answer!

We found a solution!

The problem was in "Language driver" byte of .dbf file.

The value of this byte was 0x00 and it's caused problems on few Windows XP SP3 and Windows 2003 Server SP2 computers if use OleDb.Jet4.0 provider (SP8).

If use foxpro oledb provider - everything is ok.

If you set value of this byte explicitly, problem is gone.

Solution code:

using (var fileStream = new FileStream(@"c:\test.dbf", FileMode.Open))
{
 fileStream.Seek(29, SeekOrigin.Current); // Language driver byte
 fileStream.WriteByte(0x65); // 866 code page (Russian MS-DOS)
}

Also I found that if "Record deleted flag" in .dbf file is 0x00 (SocialExplorer.FastDbf generates such files) OleDb.Jet4.0 provider under Windows 7 fails with message "Record was deleted" (under other OS such as Windows XP with JET4 (SP8) everything is ok)

If you change this byte to 0x20 (32 dec) then problem is gone.

 

I hope Microsoft programmers will fix this nasty bug :)

 

Thank you!

 

With best regards, Alexei Bouravtsev.