'ORA-00917: missing comma' In insert Query in Oracle From .Net Project

Question

I have been trying to run an insert query into oracle table from .Net project. The query is as follows:

var commandTextgpt = $"INSERT INTO tl_order " +
                  $"(ORDER_ID, ORDER_DATE, ORDER_GROSS_AMOUNT, ORDER_NUMBER, ORDER_DESCRIPTION, ORDER_TYPE_LKP_ID, REQUIRED_BY_DATE, CUSTOMER_MASTER_ID, " +
                  $"DISCOUNT_AMOUNT, ORDER_NET_AMOUNT, TAX_AMOUNT, USER_ID, EMAIL_ADDRESS, PAYMENT_TERMS, LOCATION_ID, CUSTOMER_SIGNATURE_IMAGE_URL, ACTIVE_FLAG, " +
                  $"PAYMENT_METHOD, MOBILE_ORDER_ID, TMX_STATUS, DST_STATUS, CUSTOMER_MASTER_CODE, LOCATION_CODE, ITRANSACTION_NUM) " +
                  $"VALUES (" +
                  $"{orderDatafromDynastyTables.Rows[0]["ORDER_ID"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["ORDER_DATE"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["ORDER_GROSS_AMOUNT"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["ORDER_NUMBER"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["ORDER_DESCRIPTION"]}, " +
                  $"NULL, " +
                  $"{orderDatafromDynastyTables.Rows[0]["REQUIRED_BY_DATE"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["CUSTOMER_MASTER_ID"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["DISCOUNT_AMOUNT"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["ORDER_NET_AMOUNT"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["TAX_AMOUNT"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["USER_ID"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["EMAIL_ADDRESS"]}, " +
                  $"NULL, " +
                  $"NULL, " +
                  $"NULL, " +
                  $"{orderDatafromDynastyTables.Rows[0]["ACTIVE_FLAG"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["PAYMENT_METHOD"]}, " +
                  $"NULL, " +
                  $"{orderDatafromDynastyTables.Rows[0]["TMX_STATUS"]}, " +
                  $"{orderDatafromDynastyTables.Rows[0]["DST_STATUS"]}, " +
                  $"NULL, " +
                  $"NULL, " +
                  $"NULL)";

the orderDatafromDynastyTables is a DataTable object getting data from an Mssql database its defination is as follows

var orderDatafromDynastyTables = FetchSQLQueryForDyastyData(sqlQuery, orderId, ordDate, 
ordShipDate, customerCode);

The following is the data I am getting in orderDatafromDynastyTables

ORDER_ID: 65121
ORDER_DATE: 2013-12-14 09:18:52.000
ORDER_GROSS_AMOUNT: 1023.6200
ORDER_NUMBER: ORD011805065121
ORDER_DESCRIPTION: NESTLE
ORDER_TYPE_LKP_ID: NULL
REQUIRED_BY_DATE: 2013-12-14 09:18:52.000
CUSTOMER_MASTER_ID: 100008
DISCOUNT_AMOUNT: 0.0000
ORDER_NET_AMOUNT: 1023.6200
TAX_AMOUNT: 0.0000
USER_ID: 103.53.46.250
EMAIL_ADDRESS: NULL
PAYMENT_TERMS: NULL
LOCATION_ID: NULL
CUSTOMER_SIGNATURE_IMAGE_URL: NULL
ACTIVE_FLAG: 1
PAYMENT_METHOD: Payments.CashOnDelivery
MOBILE_ORDER_ID: NULL
TMX_STATUS: READY
DST_STATUS: READY
CUSTOMER_MASTER_CODE: NULL
LOCATION_CODE: NULL
ITRANSACTION_NUM: NULL

I have listed down the column names and the respective values. Its just a single record as expected.

The following is a screenshot of my Oracle table

I have been trying to debug this with the help of ChatGpt and everything, but I still haven't found the issue. Help would be very much appreciated. Thank you.

Please do not use concatenation to build SQL queries it makes them vulnerable to injections, use parametrized queries — Guru Stron
– Guru Stron, Commented Dec 13, 2023 at 21:24

Alex Poole · Accepted Answer · 2023-12-13 23:55:08Z

If you were really going to embed the values in the SQL statement like that then you would need to put quotes around string values and dates, e.g. instead of:

$"{orderDatafromDynastyTables.Rows[0]["ORDER_NUMBER"]}, " +

you could do:

$"'{orderDatafromDynastyTables.Rows[0]["ORDER_NUMBER"]}', " +

And you shouldn't rely on implicit conversion of dates, so instead of:

$"{orderDatafromDynastyTables.Rows[0]["ORDER_DATE"]}, " +

or

$"'{orderDatafromDynastyTables.Rows[0]["ORDER_DATE"]}', " +

you could convert explicitly with a format mask, to a timestamp as you have fractional seconds:

$"to_timestamp('{orderDatafromDynastyTables.Rows[0]["ORDER_DATE"]}', 'YYYY-MM-DD HH24:MI:SS.FF'), " +

(or add a cast(... as date) to be even more explicit; if the fraction seconds are always zero you could also use to_date() and embed those in the format model as a fixed value.)

fiddle with just the first four columns, showing the error without the quotes and success with quotes and explicit conversion.

But don't do this at all. It would be much better to use bind variables (as @MT0 has shown) rather than embedding values in a string like this - better for performance, and simpler to maintain, but also to avoid SQL injection.

MT0 · Accepted Answer · 2023-12-13 22:50:08Z

NEVER build queries using template strings; that is how you introduce SQL injection vulnerabilities into your code.

Instead, use a parameterised query and bind variables:

var commandTextgpt = $"INSERT INTO tl_order " +
                  $"(ORDER_ID, ORDER_DATE, ORDER_GROSS_AMOUNT, ORDER_NUMBER, ORDER_DESCRIPTION, ORDER_TYPE_LKP_ID, REQUIRED_BY_DATE, CUSTOMER_MASTER_ID, " +
                  $"DISCOUNT_AMOUNT, ORDER_NET_AMOUNT, TAX_AMOUNT, USER_ID, EMAIL_ADDRESS, PAYMENT_TERMS, LOCATION_ID, CUSTOMER_SIGNATURE_IMAGE_URL, ACTIVE_FLAG, " +
                  $"PAYMENT_METHOD, MOBILE_ORDER_ID, TMX_STATUS, DST_STATUS, CUSTOMER_MASTER_CODE, LOCATION_CODE, ITRANSACTION_NUM) " +
                  $"VALUES " +
                  $"(:ORDER_ID, :ORDER_DATE, :ORDER_GROSS_AMOUNT, :ORDER_NUMBER, :ORDER_DESCRIPTION, NULL, :REQUIRED_BY_DATE, :CUSTOMER_MASTER_ID, " +
                  $":DISCOUNT_AMOUNT, :ORDER_NET_AMOUNT, :TAX_AMOUNT, :USER_ID, :EMAIL_ADDRESS, NULL, NULL, NULL, :ACTIVE_FLAG, " +
                  $":PAYMENT_METHOD, NULL, :TMX_STATUS, :DST_STATUS, NULL, NULL, NULL)";


OracleCommand oraCommand = new OracleCommand(commandTextgpt, db);
using(var cmd = new OracleCommand(sql,con)
{
   OracleParameter[] parameters = new OracleParameter[] {
      new OracleParameter("ORDER_ID", orderDatafromDynastyTables.Rows[0]["ORDER_ID"]),
      new OracleParameter("ORDER_DATE", orderDatafromDynastyTables.Rows[0]["ORDER_DATE"]),
      new OracleParameter("ORDER_GROSS_AMOUNT", orderDatafromDynastyTables.Rows[0]["ORDER_GROSS_AMOUNT"]),
      new OracleParameter("ORDER_NUMBER", orderDatafromDynastyTables.Rows[0]["ORDER_NUMBER"]),
      // etc...
   };

   cmd.Parameters.AddRange(parameters);
   cmd.ExecuteNonQuery();
}

Collectives™ on Stack Overflow

'ORA-00917: missing comma' In insert Query in Oracle From .Net Project

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related