Insert Today into an oracle table

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()

Answer 1

User2101710649 posted

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

Answer 2

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,

Answer 3

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.

Answer 4

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

 

Answer 5

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.

Answer 6

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