Joining DBF, Join expression not supported

Transact-SQL
1

Hi,

I am trying below code to query from DBFs. The same code I am using to query from SQL Server but I guess joining have soecial requirments when it comes to DBF.

I am getting Join expression not supported.

Can anyone help please..

sql = "SELECT CONVERT(date, DOB) AS DOB, CR_GNDLINE.STR_ID AS STR_NO, STR_NAME, 'Promotion' AS Amount_Description, SUM(AMT) AS TOTAL_AMOUNT ";
sql += "FROM CR_GNDLINE ";
sql += "LEFT JOIN CR_PROMO ON BRAND=PRO_BRAND AND COMPANY=PRO_COMPANY AND TYPEID=PRO_ID ";
sql += "LEFT JOIN CR_STORE ON BRAND=CR_STORE.STR_BRAND AND COMPANY=CR_STORE.STR_COMPANY AND CR_GNDLINE.STR_ID=CR_STORE.STR_NO ";
sql += "WHERE DOB = '2016-12-01' AND STR_NO = 1543 AND CR_GNDLINE.TYPE=2 ";
sql += "GROUP BY CR_GNDLINE.STR_ID, STR_NAME, DOB";

data_table = new DataTable();

sql_connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\ReemData\20160926;Extended Properties=dBASE IV;");
sql_connection.Open();
sql_command = new OleDbCommand(sql, sql_connection);
sql_command.CommandType = CommandType.Text;
sql_adapter = new OleDbDataAdapter(sql_command);
sql_adapter.Fill(data_table);
dataPayroll.DataSource = null;
dataPayroll.ResetBindings();
dataPayroll.DataSource = data_table;

Thanks,
Jassim

2

I don't know DBF at all but here are a couple of suggestions to try:

  1. Use LEFT OUTER JOIN instead of LEFT JOIN
  2. The "old style" outer join syntax might be in use with DBF. In it, the tables are simply a comma delimited list and the ON condition uses an asterisk along the equal sign to indicate which outer join to implement. Here's a simple example:select t1*, t2.* from Table1 t1, Table2 t2 on t1.Key_id *= t2.Key_idTry the older syntax to see if that resolves things.If it does, see if there is a newer version of DBF because the "old style" join syntax is pretty old.