hour must be between 1 and 12 error

Question

User-1790113996 posted

Good Day to all ,

kindly ask what problem with my code please refer my code below

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Oracle.ManagedDataAccess.Client;

namespace WarehouseRtoRSystem.Models
{
    public class InventoryContext
    {
        private readonly OracleCommand cmd = new OracleCommand();
        private OracleConnection Conn = new OracleConnection();
        private readonly OracleConnModel ORCONN = new OracleConnModel();

        public string insertItem(ItemModel item)
        {
            Conn = ORCONN.con;
            if (Conn.State != ConnectionState.Open)
            {
                Conn.Open();
            }


            try
            {
                cmd.Connection = Conn;


                var query = "INSERT into OWNER.INVENTORY(";

                query += "INVNO,";
                query += "INVORG,";
                query += "ITEM,";
                query += "ITEMTYPE,";
                query += "SUBINVCODE,";
                query += "LOCATORS,";
                query += "LOTNO,";
                query += "COUNTRYOFORIGINCODE,";
                query += "EXPIRATIONDATE,";
                query += "ONHANDQTY,";
                query += "ISCONSIGNED,";
                query += "OWNINGORGANIZATION,";
                query += "ITEMCOST,";
                query += "INVCOST,";
                query += "PLANNERCODE,";
                query += "COMMODITYCODE,";
                query += "COMMCODEDESC,";
                query += "PRIMARYUOMCODE,";
                query += "MINMINMAXQTY,";
                query += "MAXMINMAXQTY,";
                query += "HAZARDOUZITEMFLG,";
                query += "DESCRIPTION,";
                query += "ITEMDESCRIPTION,";
                query += "INVENTORYITEMSTATUSCODE,";
                query += "ORIGINALDATERECIEVE,";
                query += "NBROFDAYSSINCERECPT,";
                query += "DATERCVDINSUBINV";
                query += ")";

                query += " VALUES(";

                query += "'" + item.INVNO + "',"; 
                query += "'" + item.INVORG + "',"; 
                query += "'" + item.ITEM + "',"; 
                query += "'" + item.ITEMTYPE + "',"; 
                query += "'" + item.SUBINVCODE + "',"; 
                query += "'" + item.LOCATORS + "',";
                query += "'" + item.LOTNO + "',"; 
                query += "'" + item.COUNTRYOFORIGINCODE + "',"; 
                
                query += "TO_DATE('" +
                         (item.EXPIRATIONDATE != null
                             ? item.EXPIRATIONDATE.GetValueOrDefault().ToString("MM/dd/yyyy HH:mm:ss")
                             : "") + "','MM/DD/YYYY HH24:MI:SS'),";
                
                query += item.ONHANDQTY + ","; 
                query += "'" + item.ISCONSIGNED + "',"; 
                query += "'" + item.OWNINGORGANIZATION + "',";
                query += item.ITEMCOST + ","; 
                query += item.INVCOST + ",";
                query += "'" + item.PLANNERCODE + "',";
                query += "'" + item.COMMODITYCODE + "',"; 
                query += "'" + item.COMMCODEDESC + "',"; 
                query += "'" + item.PRIMARYUOMCODE + "',"; 
                query += item.MINMINMAXQTY + ",";
                query += item.MAXMINMAXQTY + ",";
                query += "'" + item.HAZARDOUZITEMFLG + "',"; 

                query += "q'[" + item.DESCRIPTION.Replace("'", "''") + "]',"; 
                query += "'" + item.ITEMDESCRIPTION + "',";
                query += "'" + item.INVENTORYITEMSTATUSCODE + "',";
                
                query += "TO_DATE('" +
                         (item.ORIGINALDATERECIEVE != null
                             ? item.ORIGINALDATERECIEVE.GetValueOrDefault().ToString("MM/dd/yyyy HH:mm:ss")
                             : "") + "','MM/DD/YYYY HH24:MI:SS'),";
                   
                query += item.NBROFDAYSSINCERECPT + ",";
                
                query += "TO_DATE('" +
                         (item.ORIGINALDATERECIEVE != null
                             ? item.DATERCVDINSUBINV.GetValueOrDefault().ToString("MM/dd/yyyy HH:mm:ss")
                             : "") + "','MM/DD/YYYY HH24:MI:SS')";
                   
                query += ")";


                var DESC = new OracleParameter();
                DESC.Value = item.DESCRIPTION;


                cmd.CommandText = query;

                cmd.CommandType = CommandType.Text;
                DESC.ParameterName = "DESCRIPTION";
                cmd.Parameters.Add(DESC);
                cmd.ExecuteNonQuery();  //ERROR
            }
            finally
            {
                Conn.Close();
            }


            return "Seccessfully inserted";
        }
    }
}

Here is my manually input in  oracle database

INSERT into OSPI_WR2R_OWNER.INVENTORY(INVNO,INVORG,ITEM,ITEMTYPE,SUBINVCODE,LOCATORS,LOTNO,COUNTRYOFORIGINCODE,EXPIRATIONDATE,ONHANDQTY,ISCONSIGNED,OWNINGORGANIZATION,ITEMCOST,INVCOST,PLANNERCODE,COMMODITYCODE,COMMCODEDESC,PRIMARYUOMCODE,MINMINMAXQTY,MAXMINMAXQTY,HAZARDOUZITEMFLG,DESCRIPTION,ITEMDESCRIPTION,INVENTORYITEMSTATUSCODE,ORIGINALDATERECIEVE,NBROFDAYSSINCERECPT,DATERCVDINSUBINV) 

VALUES('09081700001','PH2','002-002-0','EXP_STK','ONGSCAR','00.00.00','','',TO_DATE('','MM/DD/YYYY HH:MI:SS'),18,'NO','PH2 561 CARMONA GENERAL STORES',154,2772,'PH2 CON','138007','PAPER OFFICE SUPPLIES','EA',20,31,'',q'[COPY PAPER LONG]','','GS-Active',TO_DATE('08/07/2017 20:38:16','MM/DD/YYYY HH:MI:SS'),31,TO_DATE('08/07/2017 20:38:16','MM/DD/YYYY HH:MI:SS'));

Error shown

Error starting at line 1 in command:
INSERT into OSPI_WR2R_OWNER.INVENTORY(INVNO,INVORG,ITEM,ITEMTYPE,SUBINVCODE,LOCATORS,LOTNO,COUNTRYOFORIGINCODE,EXPIRATIONDATE,ONHANDQTY,ISCONSIGNED,OWNINGORGANIZATION,ITEMCOST,INVCOST,PLANNERCODE,COMMODITYCODE,COMMCODEDESC,PRIMARYUOMCODE,MINMINMAXQTY,MAXMINMAXQTY,HAZARDOUZITEMFLG,DESCRIPTION,ITEMDESCRIPTION,INVENTORYITEMSTATUSCODE,ORIGINALDATERECIEVE,NBROFDAYSSINCERECPT,DATERCVDINSUBINV) 
VALUES('09081700001','PH2','002-002-0','EXP_STK','ONGSCAR','00.00.00','','',TO_DATE('','MM/DD/YYYY HH:MI:SS'),18,'NO','PH2 561 CARMONA GENERAL STORES',154,2772,'PH2 CON','138007','PAPER OFFICE SUPPLIES','EA',20,31,'',q'[COPY PAPER LONG]','','GS-Active',TO_DATE('08/07/2017 20:38:16','MM/DD/YYYY HH:MI:SS'),31,TO_DATE('08/07/2017 20:38:16','MM/DD/YYYY HH:MI:SS'))
Error report:
SQL Error: ORA-01849: hour must be between 1 and 12
01849. 00000 -  "hour must be between 1 and 12"
*Cause:    
*Action:

thank you all

Answer 1

User269602965 posted

Note 1:

In Oracle 'HH:MI:SS' date layout will not accept '000:00:00'

you must use 24 hour layout 'HH24:MI:SS'

Note 2:

save yourself some typing and enclose all your SQL into an XML tag <SQL>code</SQL> then pass SQL.Value.  Do not have to do all that STRING BUILDING.  Example below.

Note 3:

I am not so sure your insert will work.  You must use Oracle Parameters Collection and BIND Variables in the VALUES section.  Example below as well.

using System;
using System.Xml.Linq;

  public static void updateUnitsActiveFlag(decimal decQuantity, string strDescription, DateTime dateDateClosed)
  {
	// Insert Quantity into new row Units table
	string OraConnStr = ConfigurationManager.ConnectionStrings["OraConnStr"].ConnectionString;
	try
	{
	  var SQL = System.Xml.Linq.XElement.Parse("<SQL> INSERT INTO {YOURSCHEMANAME}.UNITS(UNITS_SEQ, QUANTITY, DESCRIPTION, DATE_CLOSED, DATE_TODAY) VALUES(UNIT_SEQ.NextVal, :BindVarQuantity, :BindVarDescription, :BindVarDateClosed, SYSDATE)</SQL>");
	  using (OracleConnection conn = new OracleConnection(OraConnStr))
	  {
		using (OracleCommand cmd = new OracleCommand(SQL.Value, conn))
		{
		  cmd.Parameters.Clear();
		  cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input);
		  cmd.Parameters.Add("BindVarDescription", OracleDbType.CLOB, strDescription, ParameterDirection.Input);
		  cmd.Parameters.Add("BindVarDateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input);
		  conn.Open();
		  cmd.ExecuteNonQuery();
		}
	  }
	}
	catch (Exception ex)
	{
	  AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb");
	}
} 

Answer 2

User-1790113996 posted

thank you for your idea sir and  also from all mvc developer thanks