1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Error in Inserting the Date + ORACLE

Discussion in 'SQL PL/SQL' started by sonia_1985, Aug 30, 2012.

  1. sonia_1985

    sonia_1985 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    I want to insert the date in ORACLE database but getting the ERROR missing expression when the command cmd.ExecuteNonQuery() is EXECUTED

    Code (Text):


    Imports System.Globalization
    Imports System.Data
    Imports Oracle.DataAccess.Client

    Partial Class _Default
        Inherits System.Web.UI.Page
        Dim con As New OracleConnection("Data Source=XE;User Id=user_test;Password=sarvagya;")
        Dim cmd As New OracleCommand
        Dim da As OracleDataAdapter
        Dim ds As DataSet
        Dim query As String

        Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            Try
                query = "Insert into Practise2 values(@DOB)"
                cmd = New OracleCommand(query, con)


                Dim formats As String() = {"dd/MM/yyyy", "d/M/yyyy"}


                Dim dt As DateTime
                If DateTime.TryParseExact(txtDateofBirth.Text, formats, CultureInfo.InstalledUICulture, DateTimeStyles.None, dt) Then
                    cmd.Parameters.Add("DOB", dt.ToString("yyyy-MM-dd"))
                End If
                If con.State = ConnectionState.Closed Then
                    con.Open()
                End If
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
            End Try
        End Sub
    End Class
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,409
    Likes Received:
    350
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post the table definition and the value of @DOB.
     
  3. sonia_1985

    sonia_1985 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    I searched on the Net....& changed my code as follows : -
    Code (Text):


    Imports System.Globalization
    Imports System.Data
    Imports Oracle.DataAccess.Client

    Partial Class _Default
        Inherits System.Web.UI.Page

        Dim con As New OracleConnection("Data Source=XE;User Id=user_test;Password=sarvagya;")
        Dim cmd As New OracleCommand
        Dim da As OracleDataAdapter
        Dim ds As DataSet
        Dim query As String

        Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            Try
                query = "Insert into Practise2 values(:DOB)"
                cmd = New OracleCommand(query, con)
                Dim formats As String() = {"dd/MM/yyyy", "d/M/yyyy"}
                Dim dt As DateTime


                    cmd.Parameters.Add(":DOB", "TO_DATE('18/07/2011','dd/mm/yyyy')")

                If con.State = ConnectionState.Closed Then
                    con.Open()
                End If
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
            End Try
        End Sub
    End Class
     
    When I run the Code I get the ERROR message
    a non-numeric character was found where a numeric character was expected.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,409
    Likes Received:
    350
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That error message is displayed when the date format string to the TO_DATE call doesn't match the string being converted:

    Code (SQL):
    SQL> INSERT INTO date_test
      2  VALUES(to_date('18/MAR/2011 08:21:03 AM','DD/MM/RR HH24:MI:SS'));
    VALUES(to_date('18/MAR/2011 08:21:03 AM','DD/MM/RR HH24:MI:SS'))
                   *
    ERROR at line 2:
    ORA-01858: a non-NUMERIC CHARACTER was found WHERE a NUMERIC was expected
     
    You need to see what is actually in the DOB variable as it's likely not what you expect it to be.
     
  5. sonia_1985

    sonia_1985 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    I have used the DATE DataType in ORACLE. I used the same query as I used in .Net, It is perfectly working with ORACLE..
    Code (Text):

    Create table practise2(dob date)

    INSERT Into Practise2 values(to_date('18/07/2011','dd/mm/yyyy'))
    INSERT Into Practise2 values(to_date('18/12/2011','dd/mm/yyyy'))
    select * from practise2
    DOB    
    ---------
    18-JUL-11
    18-DEC-11

     
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,409
    Likes Received:
    350
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which doesn't answer my question, exactly. Running a similar statement in SQL*Plus is not the same as submitting such a statement through .NET. The settings for the client on the .NET machine may not match those on the database server -- please check the NLS_DATE_FORMAT set on the client machine.