System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'ON'.'

Joined
Jan 25, 2023
Messages
1
Reaction score
0
foreach (JProperty currency in currencyJson)
{
tCommand.Parameters.Clear();

tCommand.CommandText =
"INSERT INTO Currencies (ISO_CODE, VALUE ) VALUES(@isoCode, @value) ON DUPLICATE KEY UPDATE ISO_CODE = @isoCode";
Console.WriteLine(currency.Name);
tCommand.Parameters.AddWithValue("@isoCode", (string)currency.Name);
tCommand.Parameters.AddWithValue("@value", (double)currency.Value);
tCommand.ExecuteNonQuery();
}

So code above has error that i put on post thread. I cant find any solution to it. When i remove ON DUPLICATE KEY UPDATE ISO_CODE = @isoCode, code works just fine. Meanted code needs to be on my button refresh currency data, thats why i need this ON DUPLICATE KEY UPDATE, but for some reason it puts error on post thread. Sorry for my English.
 
Joined
Jan 30, 2023
Messages
107
Reaction score
13
The error is because the syntax of the SQL statement is incorrect. The ON DUPLICATE KEY UPDATE clause is specific to MySQL, not SQL Server. To perform an upsert in SQL Server, you need to use a combination of IF EXISTS and UPDATE statements.

Here's an example of the corrected code:

Code:
foreach (JProperty currency in currencyJson)
{
  tCommand.Parameters.Clear();

  tCommand.CommandText =
    @"IF EXISTS (SELECT * FROM Currencies WHERE ISO_CODE = @isoCode)
    BEGIN
        UPDATE Currencies SET VALUE = @value WHERE ISO_CODE = @isoCode
    END
    ELSE
    BEGIN
        INSERT INTO Currencies (ISO_CODE, VALUE) VALUES (@isoCode, @value)
    END";

  Console.WriteLine(currency.Name);
  tCommand.Parameters.AddWithValue("@isoCode", (string)currency.Name);
  tCommand.Parameters.AddWithValue("@value", (double)currency.Value);
  tCommand.ExecuteNonQuery();
}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top