Join multiple tables

Question

Hello I try connecting four tables for my OleDbCommand. 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.mestoTable1.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 sql commandtext of the command correctly please? Thank you in advance

Sanjeeb Lenka · Answer

may be your connection string is wrong: Refer to this link: http://www.connectionstrings.com/access

Nel · Answer

Yes you are right Sanjeeb. The path in cryRpt.Load was wrong. Now it works. Thanks a lot to all of you

Ish Bandhu · Answer

if u r using crystal report then you need to provide CrystalDecisions.Shared.ConnectionInfo connectionInfo = new CrystalDecisions.Shared.ConnectionInfo(); connectionInfo.ServerName = server anme; connectionInfo.UserID = user id; connectionInfo.Password = password;

Nel · Answer

I have checked the fields in the datatable in the dataset and realized that I have missed two fields. I have changed that and with this sentence: string comstring1 = Select Table1.Rbr Table1.mbr MATDAT.PRIME SIFRAB.IMEZAN ORGDAT6.IOE Table1.sifra Table1.nazivnatema Table1.propisi Table1.datumod Table1.datumdo Table1.mestoTable1.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=MATDAT.RABMES order by Table1.Rbr asc ; it works but when I run the application now I am asked for database login which is very odd for me login id and login password. Why does it happen? Can anybody help me please? Thanks P.S. the MS Access database is on the same computer and I didn't put any password or login ID.

Sanjeeb Lenka · Answer

check this query: 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 Table1.mbr=MATDAT.MBR) inner join ORGDAT6 on ORGDAT6.PE=MATDAT.PE) inner join SIFRAB on SIFRAB.RABMES=MATDAT.RABMES) order by Table1.Rbr asc or try this 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 (Table1.mbr=MATDAT.MBR) inner join ORGDAT6 on (ORGDAT6.PE=MATDAT.PE) inner join SIFRAB on (SIFRAB.RABMES=MATDAT.RABMES) order by Table1.Rbr asc

Kunal Vaishya · Answer

Hi Can you explain in details your Logical relations of tables.

Kunal Vaishya · Answer

Hi  Can you check all fields which you are using for join has same data type ?

Nel · Answer

Hi Ish I changed it but again I get an error Syntax error (missing operator) in query expression 'Table1.mbr=MATDAT.MBR inner join ORGDAT6 on ORGDAT6.PE=MATDAT.PE inner join SIFRAB on SIFRAB.RABMES=MATDAT.RABMES'.

Ish Bandhu · Answer

i think problem in line SIFRAB.RABMES=MARDAT.RABMES becuse there is no MARDAT table. Please check it. I think it will solve the probelm if it do not then your code will give some other error.

Nel · Answer

Thanks Kunal I tried this but didn't help.

Nel · Answer

Thanks Sanjeeb but I tried this and get an error Syntax error (missing operator) in query expression 'Table1.mbr=MATDAT.MBR inner join ORGDAT6 on ORGDAT6.PE=MATDAT.PE inner join SIFRAB on SIFRAB.RABMES=MARDAT.RABMES'.

Kunal Vaishya · Answer

Hi 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

Kunal Vaishya · Answer

Hi  MARDAT table you are using its not found

Sanjeeb Lenka · Answer

try this it may help you: 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 Table1.mbr=MATDAT.MBR inner join ORGDAT6 on ORGDAT6.PE=MATDAT.PE inner join SIFRAB on SIFRAB.RABMES=MARDAT.RABMES order by Table1.Rbr asc