C# if available in SQL Update else insert

Question

I have checked many posts on Stackoverflow but I am still stuck with the query below. I am a beginner learning C# and would request help.

Error: Incorrect Syntax near keyword ELSE

I am trying to check if the Question1 or Question2 if either of them is selected the below should be executed. Also other time it gave me an error @answer2 not defined.

string query = @"IF EXISTS(SELECT * FROM dbo.tbl_pkt_Answers_Submitted  WHERE (Questions = @Question1 or Questions = @Question2))

    UPDATE dbo.tbl_pkt_Answers_Submitted 
    SET AnswersSubmitted = @Answer1
    WHERE Questions = @Question1;

    UPDATE dbo.tbl_pkt_Answers_Submitted 
    SET AnswersSubmitted = @Answer2
    WHERE Questions = @Question2;

    ELSE

    INSERT INTO dbo.tbl_pkt_Answers_Submitted(Questions, AnswersSubmitted)   
    VALUES (@Question1, @Answer1), (@Question2, @Answer2);";

SqlCommand cmd = new SqlCommand(query, con);

cmd.Parameters.Add("@Question1", lblQuestion1.Text);
cmd.Parameters.Add("@Question2", lblQuestion2.Text);

if (rdq1a1.Checked)
{
    cmd.Parameters.Add("@Answer1", rdq1a1.Text);
}

if (rdq1a2.Checked)
{
    cmd.Parameters.Add("@Answer1", rdq1a2.Text);
}

if (rdq1a3.Checked)
{
    cmd.Parameters.Add("@Answer1", rdq1a3.Text);
}

if (rdq1a4.Checked)
{
    cmd.Parameters.Add("@Answer1", rdq1a4.Text);
}

if (rdq2a1.Checked)
{
    cmd.Parameters.Add("@Answer2", rdq2a1.Text);
}

if (rdq2a2.Checked)
{
    cmd.Parameters.Add("@Answer2", rdq2a2.Text);
}

if (rdq2a3.Checked)
{
    cmd.Parameters.Add("@Answer2", rdq2a3.Text);
}

if (rdq2a4.Checked)
{
    cmd.Parameters.Add("@Answer2", rdq2a4.Text);
}

con.Open();
cmd.ExecuteNonQuery();

MessageBox.Show("Your Data has been saved");
con.Close();

Instead of ELSE use where ((Questions<>@Question1) AND (Questions<>@Question2)) — jdweng, Dec 31 '19 at 10:44
Does this answer your question? [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — AsheraH, Dec 31 '19 at 10:48

score 5 · Accepted Answer · edited Dec 31 '19 at 10:55

The 'true' part of the IF statement contains multiple SQL statements, you must surround these statements within BEGIN and END

string query = @"IF EXISTS(SELECT * FROM dbo.tbl_pkt_Answers_Submitted  WHERE (Questions = @Question1 or Questions = @Question2))
                 BEGIN
                    UPDATE dbo.tbl_pkt_Answers_Submitted 
                    SET AnswersSubmitted = @Answer1
                    WHERE Questions = @Question1;

                    UPDATE dbo.tbl_pkt_Answers_Submitted 
                    SET AnswersSubmitted = @Answer2
                    WHERE Questions = @Question2;
                 END
                 ELSE
                    INSERT INTO dbo.tbl_pkt_Answers_Submitted(Questions, AnswersSubmitted) 
                    VALUES(@Question1, @Answer1), (@Question2, @Answer2);";

score 2 · Answer 2 · answered Dec 31 '19 at 10:49

The main problem here is that your UPDATE statements after the IF are not enclosed with BEGIN and END. Not using those only works with single batch statements, not multiple. So your statement should look like:

IF EXISTS(..)
BEGIN
UPDATE 1
UPDATE 2
END
ELSE
yadayada

Gauravsa · Answer 3 · 2019-12-31T10:56:06.440

0

It appears that your Insert statement looks suspicious.

INSERT INTO dbo.tbl_pkt_Answers_Submitted(Questions, AnswersSubmitted) 
VALUES(@Question1, @Answer1)";

also, it isnt surrounded by begin and end statement. It should be

If exists ...
begin
...
end
else 
insert

edited Dec 31 '19 at 10:56

answered Dec 31 '19 at 10:47

Gauravsa

6,330
2
21
30

score 0 · Answer 4 · answered Dec 31 '19 at 10:49

You have multiple statements in your if condition being true. If there is one statement then it is okay, but with multiple statements, you need to use BEGIN...END

IF EXISTS(SELECT *
          FROM dbo.tbl_pkt_Answers_Submitted
          WHERE (Questions = @Question1 OR Questions = @Question2))
    BEGIN
        UPDATE dbo.tbl_pkt_Answers_Submitted
        SET AnswersSubmitted = @Answer1
        WHERE Questions = @Question1;

        UPDATE dbo.tbl_pkt_Answers_Submitted
        SET AnswersSubmitted = @Answer2
        WHERE Questions = @Question2;
    END
ELSE
    INSERT INTO dbo.tbl_pkt_Answers_Submitted(Questions, AnswersSubmitted)
    VALUES (@Question1, @Answer1),
           (@Question2, @Answer2);

C# if available in SQL Update else insert

4 Answers4