C# First time using an update/inner join query, receiving a syntax error

Question

I have two tables, ACTB and ACXL, both Access tables in my program. For my purposes, I wish to clear the contents of ACXL at the end of my query.

Summary:

In the program, the user accesses an openfiledialog and selects an Excel file. said file is inserted into ACXL. ACXL is matched against ACTB by the ID unique field and updates the records by adding their values. Here is my code so far:

                    string sqls = @"INSERT INTO ACXL SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" + openFileDialog.FileName + "].[" + txtSheetName.Text + "$];";
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sqls;
                    cmd.ExecuteNonQuery();
                    string updater = @"UPDATE ACTB inner join ACXL on ACTB.ID = ACXL.ID " +
                                     @"SET ACTB.GrossIncome + ACXL.GrossIncome " +
                                     @"ACTB.LessTNT + ACXL.LessTNT " +
                                     @"ACTB.TaxableIncomeCE + ACXL.TaxableIncomeCE " +
                                     @"ACTB.TaxableIncomePE + ACXL.TaxableIncomePE " +
                                     @"ACTB.GrossTaxableIncome + ACXL.GrossTaxableIncome " +
                                     @"ACTB.LessTE + ACXL.LessTE " +
                                     @"ACTB.LessPPH + ACXL.LessPPH " +
                                     @"ACTB.NetTax + ACXL.NetTax " +
                                     @"ACTB.TaxDue + ACXL.TaxDue " +
                                     @"ACTB.HeldTaxCE + ACXL.HeldTaxCE " +
                                     @"ACTB.HeldTaxPE + ACXL.HeldTaxPE " +
                                     @"ACTB.TotalTax + ACXL.TotalTax";
                    cmd.CommandText = updater;
                    cmd.ExecuteNonQuery();
                    string deleter = @"DELETE from ACXL";
                    cmd.CommandText = deleter;
                    cmd.ExecuteNonQuery();

Receiving a rather vague Syntax error on UPDATE command. See anything wrong here? Is this the correct way of achieving my goal? Is this how you add the values of the two tables?

EDIT:

Problem resolved, here is what we came up with:

                    string updater = 
                        @"UPDATE ACTB " + @"INNER JOIN ACXL on ACTB.ID = ACXL.ID "+
                        @"SET ACTB.GrossIncome = ACTB.GrossIncome + ACXL.GrossIncome, " +
                        @"ACTB.LessTNT = ACTB.LessTNT + ACXL.LessTNT, " +
                        @"ACTB.TaxableIncomeCE = ACTB.TaxableIncomeCE + ACXL.TaxableIncomeCE, " +
                        @"ACTB.TaxableIncomePE = ACTB.TaxableIncomePE + ACXL.TaxableIncomePE, " +
                        @"ACTB.GrossTaxableIncome = ACTB.GrossTaxableIncome + ACXL.GrossTaxableIncome, " +
                        @"ACTB.LessTE = ACTB.LessTE + ACXL.LessTE, " +
                        @"ACTB.LessPPH = ACTB.LessPPH + ACXL.LessPPH, " +
                        @"ACTB.NetTax = ACTB.NetTax + ACXL.NetTax, " +
                        @"ACTB.TaxDue = ACTB.TaxDue + ACXL.TaxDue, " +
                        @"ACTB.HeldTaxCE = ACTB.HeldTaxCE + ACXL.HeldTaxCE, " +
                        @"ACTB.HeldTaxPE = ACTB.HeldTaxPE + ACXL.HeldTaxPE, " +
                        @"ACTB.TotalTax = ACTB.TotalTax + ACXL.TotalTax ";

Possible duplicate of [SQL update query syntax with inner join](http://stackoverflow.com/questions/3867164/sql-update-query-syntax-with-inner-join) — sujith karivelil, Dec 06 '16 at 05:41
Why use @ on each line? You can put one up front and then put everything between one set of quotes. See http://stackoverflow.com/questions/1100260/multiline-string-literal-in-c-sharp — jeff carey, Dec 06 '16 at 05:42
@jeffcarey Temporary measure, I don't want the plus's becoming red as I might neglect it. I will make it a single statement after I am done manipulating it. — Avan, Dec 06 '16 at 05:46

Tushar_G · Answer 1 · 2016-12-06T06:12:57.280

4

In your UPDATE query use WHERE clause. This will resolve your Syntax error issue.

edited Dec 06 '16 at 06:12

answered Dec 06 '16 at 05:50

Tushar_G

300
1
9

Max · Accepted Answer · 2016-12-06T06:22:41.000

1

The syntax of your update .. set is really off. You're missing all the '=' signs to set the columns.

The most used syntax for SQL Update is this (example)

update mytable set column1 = value, column1 = value2 where id = someid etc.

so your SQL should be fixed like this:

 string updater = @"UPDATE ACTB " +
                  @"INNER JOIN ACXL on ACTB.ID = ACXL.ID ";
                  @"SET ACTB.GrossIncome = ACTB.GrossIncome + ACXL.GrossIncome, " +
                  @"ACTB.LessTNT = ACTB.LessTNT + ACXL.LessTNT, " +
                  @"ACTB.TaxableIncomeCE = ACTB.TaxableIncomeCE + ACXL.TaxableIncomeCE, " +
                  @"ACTB.TaxableIncomePE = ACTB.TaxableIncomePE + ACXL.TaxableIncomePE, " +
                  @"ACTB.GrossTaxableIncome = ACTB.GrossTaxableIncome + ACXL.GrossTaxableIncome, " +
                  @"ACTB.LessTE = ACTB.LessTE + ACXL.LessTE, " +
                  @"ACTB.LessPPH = ACTB.LessPPH + ACXL.LessPPH, " +
                  @"ACTB.NetTax = ACTB.NetTax + ACXL.NetTax, " +
                  @"ACTB.TaxDue = ACTB.TaxDue + ACXL.TaxDue, " +
                  @"ACTB.HeldTaxCE = ACTB.HeldTaxCE + ACXL.HeldTaxCE, " +
                  @"ACTB.HeldTaxPE = ACTB.HeldTaxPE + ACXL.HeldTaxPE, " +
                  @"ACTB.TotalTax = ACTB.TotalTax + ACXL.TotalTax ";

edited Dec 06 '16 at 06:22

answered Dec 06 '16 at 05:47

Max

1,049
7
9

1

You mean comma placement? I'll write up one where the set comes right after the update statement and follow it up with the inner join and see what happens. – Avan Dec 06 '16 at 05:50
1

I've edited the OP and updated it with your suggestion. Where do I put the where clause? it looks to me that it's handled by the join - on value1=value2 statement. – Avan Dec 06 '16 at 05:55
1

Inner join is fine but you are not telling SQL which column you are setting. SET expects all columns to be followed by an equal `=` operator. In your case you have `+` signs everywhere. – Max Dec 06 '16 at 06:05
1

Updated the program with your code. Initially, it didn't work, saying there's a syntax error on the inner join statement. I replaced it back at the top after the UPDATE and just before the SET like before and it worked! One question though, it's now throwing an exception on the DELETE command? – Avan Dec 06 '16 at 06:19
1

Nevermind! Delete command error was on my end, accidentally removed the FROM and it stopped working. Code works, I will update the OP with my final statement before marking the answer to finalize. – Avan Dec 06 '16 at 06:21
Great! I was wondering. I edited the answer so it's correct with the inner join up top. I never do updates with inner joins, so I hadn't paid attention to it... Tax time huh... ah. – Max Dec 06 '16 at 06:23
1

Yep. And with this, my program is officially complete! I'll probably move on to code review if they have any expert opinions on my straight forward code. – Avan Dec 06 '16 at 06:31

C# First time using an update/inner join query, receiving a syntax error

2 Answers2