CODEWITHSUNDEEP
c#sqlms-accessoledb
Avan
Avan

Reputation: 223

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

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 ";

Upvotes: 0

Views: 430

Answers (2)

Max
Max

Reputation: 1069

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 ";

Upvotes: 1

Tushar_G
Tushar_G

Reputation: 300

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

Upvotes: 4

Related Questions