locked
Insert Today into an oracle table RRS feed

  • Question

  • User-394806339 posted

    This is driving me crazy!  I keep on getting an error at the date part of the insert statement

    ORA-00932: inconsistent datatypes: expected DATE got NUMBER

    The data type in the oracle table is Date(7)

    how do I fix this?

    Thanks

            
    oConnection = New OracleConnection("Data Source= ;User ID=;Password=;") oConnection.Open() oCommand.Connection = oConnection oCommand.CommandText = "INSERT INTO MAPS.GIS(ENTRYDATE,USERID,JOB_NUMBER,ENTRY_TYPE,CHANGE_DESCRIPTION) VALUES(" & Today & ",'" & "USERID', " & txtJO.Text & ",'" & txtType.Text & "','" & txtDesc.Text & "')" oReader = oCommand.ExecuteReader()
    Tuesday, March 13, 2012 5:58 PM

Answers

  • User-394806339 posted

    Thank you guys for your posts

    I tried the oracle parameters but i got a whole bunch of errs ... so I tried the below instead and it worked!!

    String.Format("{0:dd-MMM-yyyy}", System.DateTime.Now)

    Thanks all
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2012 1:01 PM

All replies

  • User2101710649 posted

    In Your query  use  SYSDATE instead of Today .SYSDATE is used today dateand time in oracle

    Thursday, March 15, 2012 12:58 AM
  • User665793701 posted

    Use SYSDATE instead of Today


    "INSERT INTO MAPS.GIS(ENTRYDATE,USERID,JOB_NUMBER,ENTRY_TYPE,CHANGE_DESCRIPTION) VALUES("SYSDATE ",'" & "USERID', " & txtJO.Text & ",'" & txtType.Text & "','" & txtDesc.Text & "')"

    Regards,


    Thursday, March 15, 2012 1:09 AM
  • User-394806339 posted

    SYSDATE is not working ... I get an err

    <title>Compilation Error</title>

    Compilation Error

    Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

    Compiler Error Message: BC30451: Name 'Sysdate' is not declared.

    Friday, March 16, 2012 1:16 PM
  • User269602965 posted

    You should use Oracle Parameters and BIND variable to pass values into your INSERT statement

    SYSDATE error is likely indirect effect of other failures

    example

      Imports System.Xml.Linq.XElement
      
        Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal)
          ' Insert Quantity into new row Units table'
          Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString
          Try
            Dim SQL =
            <SQL>
            INSERT INTO {YOURSCHEMANAME}.UNITS
              (UNITS_SEQ, QUANTITY, DATE_CLOSED, DATE_TODAY) 
            VALUES 
              (UNIT_SEQ.NextVal, :BindVarQuantity, :DateClosed, SYSDATE)
            </SQL>
            Using conn As New OracleConnection(OraConnStr)
              Using cmd As New OracleCommand(SQL.Value, conn)
                cmd.Parameters.Clear()
                cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input)
                cmd.Parameters.Add("DateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input)
                conn.Open()
                cmd.ExecuteNonQuery()
              End Using
            End Using
          Catch ex As Exception
            'you exception handling code
          End Try
      End Sub

     

    Sunday, March 18, 2012 9:31 PM
  • User-1407477457 posted

    SYSDATE is not working ... I get an err

    Compilation Error

    Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

    Compiler Error Message: BC30451: Name 'Sysdate' is not declared.

    Sysdate is an oracle function that returns the current date and time.  It is not a dot net variable that you pass to oracle.

    Sunday, March 18, 2012 9:34 PM
  • User-394806339 posted

    Thank you guys for your posts

    I tried the oracle parameters but i got a whole bunch of errs ... so I tried the below instead and it worked!!

    String.Format("{0:dd-MMM-yyyy}", System.DateTime.Now)

    Thanks all
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2012 1:01 PM