Writing commandtext which joins several tables

Nel

Hello,

I have to take several fields from several tables and write the OleDbDataAdapter, but I have problem with connecting the tables. The database I use is Ms Access.

Here is the commandtext I wrote:

string comstring1 = "Select Table1.Rbr, Table1.mbr, MATDAT.PRIME, SIFRAB.IMEZAN, ORGDAT6.IOE, Table1.sifra, Table1.nazivnatema, Table1.propisi, Table1.datumod, Table1.datumdo, Table1.mesto,Table1.izveduvacnaobuka from MATDAT inner join Table1 on MATDAT.MBR=Table1.mbr join ORGDAT6 on MATDAT.PE=ORGDAT6.PE join SIFRAB on SIFRAB.RABMES=MARDAT.RABMES order by Table1.Rbr asc";

but when I run the windows forms application I get an error:

Syntax error (missing operator) in query expression 'MATDAT.MBR=Table1.mbr join ORGDAT6 on MATDAT.PE=ORGDAT6.PE join SIFRAB on SIFRAB.RABMES=MARDAT.RABMES'.

Can anybody help me how to write the commandtext of the command correctly please?

Thank you in advance

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nel13y ago
Thanks, but now I get this error

Syntax error (missing operator) in query expression 'OR.IOE'.
Kunal Vaishya13y ago
Try this

Select
T.Rbr, T.mbr,
MD.PRIME,
SI.IMEZAN,
OR.IOE,
T.sifra,
T.nazivnatema,
T.propisi,
T.datumod,
T.datumdo,
T.mesto,
T.izveduvacnaobuka
from MATDAT as MD inner join Table1 as T on
MD.MBR=T.mbr
join ORGDAT6 as OR on MD.PE=OR.PE
join SIFRAB as SI on SI.RABMES=MD.RABMES
order by T.Rbr asc
Nel13y ago
Thanks, but I tried that like

string comstring1 = "Select Table1.Rbr, Table1.mbr, MATDAT.PRIME, SIFRAB.IMEZAN, ORGDAT6.IOE, Table1.sifra, Table1.nazivnatema, Table1.propisi, Table1.datumod, Table1.datumdo, Table1.mesto,Table1.izveduvacnaobuka from ((MATDAT inner join Table1 on MATDAT.MBR=Table1.mbr) inner join ORGDAT6 on MATDAT.PE=ORGDAT6.PE) inner join SIFRAB on SIFRAB.RABMES=MARDAT.RABMES order by Table1.Rbr asc";

but I get the same error.
Syntax error in JOIN operation.

I also tried this

string comstring1 = "Select [Table1].[Rbr], [Table1].[mbr], [MATDAT].[PRIME], [SIFRAB].[IMEZAN], [ORGDAT6].[IOE], [Table1].[sifra], [Table1].[nazivnatema], [Table1].[propisi], [Table1].[datumod], [Table1].[datumdo], [Table1].[mesto],[Table1].[izveduvacnaobuka] from (([MATDAT] full inner join [Table1] on [MATDAT].[MBR]=[Table1].[mbr]) full inner join [ORGDAT6] on [MATDAT].[PE]=[ORGDAT6].[PE]) full inner join [SIFRAB] on [SIFRAB].[RABMES]=[MARDAT].[RABMES] order by [Table1].[Rbr] asc";

and I get the following error

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Iftikar Hussain13y ago
Hi,
Please refer the below link

http://www.codeproject.com/Questions/549042/Howplustoplusjoinplusmultipleplustableplusofplusms

Regards,
Iftikar