A Non-numeric character was found where a numeric was expected + ORACLE

[sonia.sardana]

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('" & dt.ToString() & "','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 the line cmd.ExecuteNonQUery() is Execueted, I get the ERROR ...A NON-NUMERIC CHARACTER WAS FOUND WHERE A NUMERIC WAS EXPECTED.


I have used the DATE DataType in ORACLE. I used the same query as I used in .Net, It is perfectly working with ORACLE..

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

[dunfiddlin]

It's a very specific error so step through the code checking values and it should become obvious where the problem lies.

[jmcilhinney]

The problem is here:

cmd.Parameters.Add(":DOB", "TO_DATE('" & dt.ToString() & "','dd/mm/yyyy')")

The whole point of a parameter is to provide a value. If you want to insert a date value then you provide a date value. You have provided some text. You think that that text is going to be interpreted as SQL code and produce a date value but the whole point of parameters is that they are values, not code. The correct VB code there would be:

cmd.Parameters.Add(":DOB", dt)

You set the parameter to the actual value you want to use. If you want to use SQL code then that goes in the CommandText of the command. An alternative could have been

query = "Insert into Practise2 values(TO_DATE(:DOB,'dd/mm/yyyy'))"
'...
cmd.Parameters.Add(":DOB", dt.ToString())

but it would be rather silly to start with a DateTime and convert that to a String, only to tell the database to convert it back to a date.

[sonia.sardana]

Uff Its TOO DIFFICULT TO INSERT DATE IN ORACLE USING TO_DATE. I Changed my code as suggested by jmcilhinney

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

Partial Class DATEOTHER_FORMAT
    Inherits System.Web.UI.Page

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




    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            query = "Insert into Practise2 values(TO_DATE(:DOB,'dd/mm/yyyy'))"
            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
                dt = DateTime.ParseExact(txtDateofBirth.Text, "dd/MM/yyyy", CultureInfo.InstalledUICulture)
                cmd.Parameters.Add(":DOB", dt.ToString())
            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

IF I ENTER 11/11/2009 in Textbox I am getting ERROR : -"ORA-01830: date format picture ends before converting entire input string"
IF I ENTER 18/07/2011 in Textbox I am getting ERROR : -"ORA-01843: not a valid month"