System.Data.SqlClient.SqlException: 'Incorrect syntax near '2'.'

Question

Im trying to insert this test data in my sql database and I'm getting this error: System.Data.SqlClient.SqlException: 'Incorrect syntax near '2'.'

Any ideas how to solve this?

DateTime date = DateTime.Now;

string test = "{'payload': {'businessName': 'COMPANY1', 'subscriberName': 'JOHN DOE', 'accountNumber': 'CY68005000121234567890123456', 'numberOfRecords': 1," +
    "'currentBalance': 4195.5, 'transactions': [{'transactionNumber': 'TR00000000','sequenceNumber': '000','transactionCode': '305','actualDateTime': '201812041624'," +
    "'transactionValueDate': '2018-12-04', 'transactionCurrencyCode': 'EUR', 'transactionAmount': -1149.5, 'balance': 4195.5, 'chequeNo': '', 'depositedBy': 'CY68005000121234567890123456'," +
    "'customerReference': 'uniqueValue', 'paymentNotes': 'NOTES', 'exchangeRate': 0}]}, 'errors': null}";
trans = JsonConvert.DeserializeObject<HB_transactions>(test);

for (int i=0; i<trans.payload.transactions.Count; i++)
{
    string query = "SELECT TransactionId FROM AABankTransTable";
    SqlCommand cmd = new SqlCommand(query, con);
    SqlDataReader dataReader = cmd.ExecuteReader();

    bool exists = false;

    while(dataReader.Read())
    {
        if(dataReader[0].ToString() == trans.payload.transactions[i].transactionNumber)
        {
            exists = true;
            break;
        }
    }

    dataReader.Close();

    if (exists) continue;

    query = "INSERT INTO AABankTransTable " +
        "(TransactionId, Bank, ComID, Currency, Amount, DownloadDate, Processed, CreditorName, RemittanceDetails, ValueDate)" +
        "VALUES ('" + trans.payload.transactions[i].transactionNumber + "', 'HB', " + args[0] + ", '" + trans.payload.transactions[i].transactionCurrencyCode + "', " +
        trans.payload.transactions[i].transactionAmount + ", " + date + ", 0, '" + trans.payload.transactions[i].depositedBy + "', '" +
        trans.payload.transactions[i].paymentNotes + "', " + DateTime.Parse(trans.payload.transactions[i].transactionValueDate) + ")";

    cmd = new SqlCommand(query, con);
    cmd.ExecuteNonQuery();

See [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection). Using parameters is more secure and helps to avoid strings concatenation problems. — , Jan 15 '21 at 12:30
Please use parameters instead of string concatenation to do this. The problem is almost guaranteed to be because you didn't get commas and quote characters correct but you're also prone to SQL injection, which is the top #1 security issue. — Lasse V. Karlsen, Jan 15 '21 at 12:30
First of all: You should not create queries by string concatenation but use parameterized queries. That will prevent sql injection and get rid of such errors. Second: for instance `transactionAmount` is probalby a double or decimal value. The way you are constructing your query calls `double.ToString()` and depending on your locale this might lead to `1,12` instead of `1.12` which results in an error ... (which can easily be prevented by parameterized queries) and nobody knows, what format comes out of `DateTime.toString()` (your last parameter) but you will certainly need quotes around it. — derpirscher, Jan 15 '21 at 12:32
BTW you can easily investigate such errors if you add a breakpoint after you constructed query and inspect it ... or try executing it in SQL Management Studio — derpirscher, Jan 15 '21 at 12:37
issue possibly that the date times aren't enclosed in single quotes. You'll also need to get the date format right to not get scrambled during insert if you do it this way. As said before, a parameterized query is the best way to solve your problems. — Jon Egerton, Jan 15 '21 at 13:00
Doing parametrized queries you will A) Avoid SQL Injection attacks B) Your SQL will be far more readable and mantainable C) It will likely perform better and D) You will reduce the number of errors in your SQL statements — Cleptus, Jan 15 '21 at 13:03

score 2 · Accepted Answer · answered Jan 15 '21 at 13:09

Solved by using SQL parameters instead of string concatenation.

query = "INSERT INTO AABankTransTable " +
                    "(TransactionId, Bank, ComID, Currency, Amount, DownloadDate, Processed, CreditorName, RemittanceDetails, ValueDate)" +
                    "VALUES (@TransID, 'HB', @COMID, @curr, @amount, @dlDate, 0, @depositor, @Details, @TransDate)";

                cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@TransID", trans.payload.transactions[i].transactionNumber);
                cmd.Parameters.AddWithValue("@COMID", args[0]);
                cmd.Parameters.AddWithValue("@curr", trans.payload.transactions[i].transactionCurrencyCode);
                cmd.Parameters.AddWithValue("@amount", trans.payload.transactions[i].transactionAmount);
                cmd.Parameters.AddWithValue("@dlDate", date);
                cmd.Parameters.AddWithValue("@depositor", trans.payload.transactions[i].depositedBy);
                cmd.Parameters.AddWithValue("@Details", trans.payload.transactions[i].paymentNotes);
                cmd.Parameters.AddWithValue("@TransDate", DateTime.Parse(trans.payload.transactions[i].transactionValueDate));
                cmd.ExecuteNonQuery();

System.Data.SqlClient.SqlException: 'Incorrect syntax near '2'.'

1 Answers1