C# MS Access INNER Joining syntax error

Question

I'm using MS Access 2003 to generate the DataSource of a DataGridView, when I launch the program, it throws :

    "Syntax error (no operator) in expression " k.Ka = p1.Id                 
       INNER JOIN Person AS p2 ON k.Kc1 = p2.Id
       INNER JOIN Person AS p3 ON k.Kc3 = p3.Id "

My code :

     try
            {

                using (OleDbConnection conn = new OleDbConnection(connecString))
                {

                    conn.Open();

       OleDbCommand cmd = new OleDbCommand(@"Select k.[Desc],k.Family,k.Num,p1.Name 
            AS KeyAdmin, p2.Name AS KeyCustodian1, p3.Name AS KeyCustodian3,p4.Name
            AS SecurityOfficer,p5.Name AS ServiceIT
            FROM KC AS k
            INNER JOIN Person AS p1 ON k.Ka = p1.Id                 
            INNER JOIN Person AS p2 ON k.Kc1 = p2.Id
            INNER JOIN Person AS p3 ON k.Kc3 = p3.Id       
            INNER JOIN Person AS p4 ON k.So = p4.Id  
            INNER JOIN Person AS p5 ON k.It = p5.Id
            WHERE k.Num = @Num;", conn);

               OleDbDataAdapter adapt = new OleDbDataAdapter(cmd);

               cmd.Parameters.AddWithValue("@num", form.comboKC.SelectedValue);


               DataTable dt = new DataTable();

               adapt.Fill(dt);
               form.dataGridView1.DataSource = dt;
               conn.Close();

           }

I'm a student and I can't see my error and that's I use it most of the time, neither can my supervisor so... Here I am ! Thanks for your time,

It's been forever since I've last worked with ms-access but I remember it loves parenthesis. try ON (k.Ka = p1.Id) for all your joins. — Zohar Peled
– Zohar Peled, Commented May 16, 2018 at 14:21
Is form.comboKC.SelectedValue returning a valid value for you to select on? — JayV
– JayV, Commented May 16, 2018 at 14:23

Parfait · Accepted Answer · 2018-05-16 14:25:23Z

1

MS Access' SQL dialect requires parentheses pairings around FROM and JOIN clauses. Consider below adjustment. Be sure to also escape Name, a reserved word.

SELECT k.[Desc], k.Family, k.Num, p1.[Name] AS KeyAdmin, p2.[Name] AS KeyCustodian1, 
       p3.[Name] AS KeyCustodian3, p4.[Name] AS SecurityOfficer, p5.[Name] AS ServiceIT
FROM ((((KC AS k
INNER JOIN Person AS p1 ON k.Ka = p1.Id)                
INNER JOIN Person AS p2 ON k.Kc1 = p2.Id)
INNER JOIN Person AS p3 ON k.Kc3 = p3.Id)       
INNER JOIN Person AS p4 ON k.So = p4.Id)  
INNER JOIN Person AS p5 ON k.It = p5.Id
WHERE k.Num = @Num;

answered May 16, 2018 at 14:25

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# MS Access INNER Joining syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related