Answered by:
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
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