A Non-numeric character was found where a numeric was expected + ORACLE
Code:
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..
Code:
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
Re: A Non-numeric character was found where a numeric was expected + ORACLE
It's a very specific error so step through the code checking values and it should become obvious where the problem lies.
Re: A Non-numeric character was found where a numeric was expected + ORACLE
The problem is here:
Code:
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:
Code:
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
Code:
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.
Re: A Non-numeric character was found where a numeric was expected + ORACLE
Uff Its TOO DIFFICULT TO INSERT DATE IN ORACLE USING TO_DATE. I Changed my code as suggested by jmcilhinney
Code:
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"