System.data.sqlclient.sqlexception: 'incorrect syntax near ')'.'

Question

0.00/5 (No votes)

See more:

i try this Code for Insert and Update in DataBase by Single Query if Exists then Update else insert but Problem Showing in Query Syntax

What I have tried:

C#

private void SaveUpdateMess()
        {
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString);
                string SaveUpdateBM = "if exists (Select * from BD where OrderNo = @OrderNo and BodyM = @BodyM) update BD set Mess = @Mess where OrderNo = @OrderNo and BodyM = @BodyM else begin insert into BD (OrderNo, BodyM, Mess) VALUES (@OrderNo, @BodyM, @Mess)";
                using(SqlCommand cmd = new SqlCommand(SaveUpdateBM, con))
                {
                    con.Open();

                    cmd.Parameters.AddWithValue("@OrderNo", txtOrder_No.Text);
                    cmd.Parameters.AddWithValue("@BodyM", row.Cells["BodyM"].Value);
                    cmd.Parameters.AddWithValue("@Mess", row.Cells["Mess"].Value);
                    
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
                
        }

Posted 13-Feb-21 0:53am

Amar chand123

Updated 13-Feb-21 1:47am

Herman<T>.Instance

v2

Add a Solution

Comments

PIEBALDconsult 13-Feb-21 10:30am

Also, look into the MERGE statement.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Luc Pattyn · Answer 1 · 2021-02-13T01:38:00

Solution 1

Hi,

the ELSE clause of your SQL query contains a begin keyword without a matching end.

the THEN clause does not have begin/end, why this inconsistency?

BTW: I don't expect your SQL query to be correct as you are reusing all three parameters. AFAIK all parameters must be unique, so you should come up with more parameter names and more calls to the AddWithValue method.

:)

Posted 13-Feb-21 1:38am

Luc Pattyn

Comments

Amar chand123 13-Feb-21 7:56am

New Error show'String or binary data would be truncated.
The statement has been terminated.'
when i use this
private void SaveUpdateMess()
{
foreach (DataGridViewRow row in dataGridView1.Rows)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString);
string SaveUpdateBM = "if exists (Select * from BD where OrderNo = @OrderNo and BodyM = @BodyM) Begin update BD set Mess = @Mess where OrderNo = @OrderNo and BodyM = @BodyM End else Begin insert into BD (OrderNo, BodyM, Mess) VALUES (@OrderNo, @BodyM, @Mess) End";
using(SqlCommand cmd = new SqlCommand(SaveUpdateBM, con))
{
con.Open();

cmd.Parameters.AddWithValue("@OrderNo", txtOrder_No.Text);
cmd.Parameters.AddWithValue("@BodyM", row.Cells["BodyM"].ToString());
cmd.Parameters.AddWithValue("@Mess", row.Cells["Mess"].ToString());

cmd.ExecuteNonQuery();
con.Close();
}
}

}

Luc Pattyn 13-Feb-21 8:06am

You haven't told us what the data types are (in database and in code).
My best guess would be OrderNo is a numeric field, intended to hold an order number; so

cmd.Parameters.AddWithValue("@OrderNo", txtOrder_No.Text);

is not OK, as you are providing a string.
If so, use int.TryParse() to convert your textbox(?) content to a number.

:)

OriginalGriff · Answer 2 · 2021-02-13T01:47:00

Where is the END?
I'd suggest adding BEGIN and END to the "true" side of the IF, but you give a BEGIN to teh ELSE side without a matching END.

I'd also suggest that instead of that code, you try do an UPDATE and check the number of rows it affected using @@ROW_COUNT. If that is zero, there were no matching rows, so do an INSERT instead.
I'd probably use a TRANSACTION round the whole thing as well.
You have to remember that SQL Server is a multiuser system, and IF EXISTS tests can lead to primary key violations very easily if many users are involved.