insert code error

Question

insert into customer (Advance,status)
values(@Advance,@status)
where Name='" + txtcname.Text.Trim() + "'";

in the above insert statement in going to insert 2 values based in condition but i'm getting error in where condition...

incorrect syntax near keyword where

this is the error

Munawar · Answer 1 · 2018-05-22T06:06:02.940

4

Insert query do not needs Where clause. Just write

insert into customer (Advance, status) values(@Advance, @status)

Are you trying to insert or update? if you need to update an existing record then use update instead of insert like this:

update customer set Advance=@Advance, status=@status 
where Name='" + txtcname.Text.Trim() + "'";

EDIT

Aforementioned update query will serve the purpose but its recommended to use stored procedures/parameterized queries for SQL injection safety. You should following use approach:

Private void UpdateRecord(string advance,string status, string name)
{
//SqlConnection con
SqlCommand cmdUpdate = new SqlCommand("update customer set Advance = @Advance, status = @Status where Name=@Name", con);
            cmdUpdate.Parameters.AddWithValue("@Advance", advance);
            cmdUpdate.Parameters.AddWithValue("@Status", status);            
            cmdUpdate.Parameters.AddWithValue("@name", name);
            cmdUpdate.ExecuteNonQuery();

}

Pass your data as following:

UpdateRecord(@Advance,@Status,txtcname.Text.Trim());

edited May 22 '18 at 06:06

answered May 22 '18 at 05:29

Munawar

2,588
2
26
29

While your answer is probably correct, it should also change the `where` clause to work with parameters, and explain why. – Zohar Peled May 22 '18 at 05:31
Where clause cannot be used with insert, see the syntax examples here:https://stackoverflow.com/questions/13386894/sql-server-insert-example , https://www.w3schools.com/sql/sql_insert.asp – Munawar May 22 '18 at 05:35
Unless you do an `insert into tableA select * from tableB` **where**. – P. Pat May 22 '18 at 05:37
1. `Where` clause can be used with `insert`, if it's an `insert...select`. 2. I agree that the OP is probably looking for an `update` statement, and not an `insert` statement. 3. The `where` clause in **your** `update` statement is not parameterized, making the statement vulnerable to SQL Injection attacks. Fix that, and you'll get my upvote as well. – Zohar Peled May 22 '18 at 05:38
@ZoharPeled,as per documentation insert..select still does not make the where part of insert instead its part of select. having a work around to combine two queries does not mean its part of insert. – Munawar May 22 '18 at 05:42
@Munawar while true, this nitpicking is not related to my original (and more important) comment. The statement you posted is vulnerable, you should fix it. – Zohar Peled May 22 '18 at 05:54

score 0 · Answer 2 · answered May 22 '18 at 05:33

You can't use 'where' in an insert-statement. To achieve the same result, you can insert all entries and delete the wrong.

You can use a select-statement after an insert, where you select entries from a table into another. This could be a solution for you, too.

Insert into customer (advance, status) values (...)
select advance, status
from anotherCustomerTable
where ...

P.S. try to prepare the where-part, too.

score 0 · Answer 3 · answered May 22 '18 at 05:33

You can not add where clause with values. You can achieve this with following way if you really want to insert new rows else you can follow the @Munawar solution

insert into customer (Advance, status)

SELECT @Advance,@status
FROM  customer where Name='" + txtcname.Text.Trim() + "'"

insert code error

3 Answers3