Incorrect sql syntax? (Beginner stuff)

Question

Running a method to insert some data into a table EventsManaged using an sql query which returns an error

"incorrect syntax near 'WHERE'"

but I can't see my mistake?

The method is in an EventDAL and being called from an open form. I used breakpoints and checked the correct type of data was being entered- any help?

public static int AddAllToEventsManaged( string areaType, string areaName, double cost, double costPer, int v, int id)            
{    
            using (SqlConnection connection = new SqlConnection(_connectionstring))
            {
                connection.Open();
                string SQLquery1 = string.Format("INSERT INTO EventsManaged (AreaType, AreaName, Cost, CostPer) VALUES ('{0}','{1}','{2}','{3}') WHERE AreaID = '{4}' AND CustomerID = '{5}'", areaType, areaName, cost, costPer, v, id);
                SqlCommand insertProjectCommand = new SqlCommand(SQLquery1, connection);
                int rowsAffected = insertProjectCommand.ExecuteNonQuery();
                connection.Close();
                return rowsAffected;
            }
        }
}

You cannot combine an `Insert` with `where`. The WHERE clause is to filter data but that does not make sense if the data is not there yet and is going to be inserted. Maybe you meant to UPDATE the data? — Peter Bons, Mar 10 '18 at 08:19
Please use a parametrized query. You are opening your database to hackers. https://stackoverflow.com/a/652999/4180382 — Ole EH Dufour, Mar 10 '18 at 08:21
It's just for a high school project but I will try this, thanks. — Naomi, Mar 10 '18 at 08:27
1. Backup your database. 2. Try your code with an AreaType of `', '', 0, 0); DROP TABLE EventsManaged; -- ` — ProgrammingLlama, Mar 10 '18 at 08:37

score 1 · Answer 1 · answered Mar 10 '18 at 08:24

Two things to notice:

Instead of formatting the command string with the values included, you should use the sql command parameters, that you can add to the Parameters collection of your SqlCommand.
You are mixing 2 ways to INSERT things in SQL. You can INSERT ... SELECT ... WHERE or you can INSERT ... VALUES, but the one with VALUES does not allow/need WHERE. I put the links to MySql documentation but I think it is the same for other SQL RDBMS (at leas for SQL Server)

score 0 · Answer 2 · answered Mar 10 '18 at 08:27

I think you want an UPDATE query like this:

string SQLquery1 = string.Format("UPDATE EventsManaged SET AreaType = '{0}', AreaName = '{1}', Cost = '{2}', CostPer = '{3}' WHERE AreaID = '{4}' AND CustomerID = '{5}'", areaType, areaName, cost, costPer, v, id);

This will update your existing row which satisfies the WHERE condition. INSERT statement adds a new row so there is no WHERE clause here. Hope it helped.

Incorrect sql syntax? (Beginner stuff)

2 Answers2