locked
hour must be between 1 and 12 error RRS feed

  • 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

    Friday, September 8, 2017 9:26 AM

Answers

  • 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");
    	}
    } 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 8, 2017 4:55 PM

All replies

  • 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");
    	}
    } 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 8, 2017 4:55 PM
  • User-1790113996 posted

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

    Monday, September 11, 2017 5:24 AM