Pls help me.. with this .. pls.. vn.net n oracle..

[kaumudi]

hi,
plz help me quickly.....
i'm facing a tough situation with my project on vb.net + oracle...

i've created a table "edt". the data dictionary for it is as follows:

SQL> describe edt
Name Type
-------- ------
ENAME CHAR(20)
DT DATE

and i've written code as follows:

Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 

Handles SaveButton.Click
        con.Open()
        MsgBox("connection opened", MsgBoxStyle.OKOnly)

        str = "insert into edt values('a'," & CDate(Today()) & ")"
        MessageBox.Show("str:" & str)

        mycommand = New OleDbCommand(str, con)
        MsgBox("command set to the connection", MsgBoxStyle.OKOnly)

        no = mycommand.ExecuteNonQuery()
        MessageBox.Show("Records inserted: " & no)

        con.Close()
        MsgBox("connection closed", MsgBoxStyle.OKOnly)
    End Sub

where 
    Dim con As OleDbConnection
    Dim mycommand As OleDbCommand

when i run the abv code i see that the command is set to the connection.
But i get an error at the statement " no = mycommand.ExecuteNonQuery()"
the error is :

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

Unhandled Exception: System.Data.OleDb.OleDbException: One or more errors occurred during processing of command.
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

please help me... i don't understand the error coz the messagebox statement which prints the

string "str" gives me a correct reply..
ie. str: insert into edt values('a',9/10/2006)

waiting for a quick solution to this issue..
and also let me know what would be the default date format in vb.net.. pls..

[Craig Gemmill]

str = "insert into edt values('a','" & DateTime.Now.ToShortDateString() & "')"

In the future, look into using parameterized queries.

[kaumudi]

str = "insert into edt values('a','" & DateTime.Now.ToShortDateString() & "')"

In the future, look into using parameterized queries.

thanks a lot.. for ur reply senor... kaumudi

[cjard]

yuck...

dont ever rely on implicit parsing of a date from a string. your system might write

11/08/2006

meaning 11 aug 2006, but oracle might be on default us date format and treat it as 08 nov 2006


you MUST put your local date into a string, and then convert it back based on that same string:


"INSERT INTO evt VALUES('a', to_date('" & DateTime.Now.ToString("yyyyMMdd") &"','yyyymmdd') )"

for today 11 sep this query would look like:

INSERT INTO evt VALUES ('a', to_date('20060911', 'yyyymmdd') )





In blue we have the .NET date format.. it converts this date intoa string in yyyymmdd format. THEN in oracle we have to_date function that turns a string back into date. The format in pink is oracle date format, and oracle knows to parse this string we are putting in as yyyymmdd

You should ALWAYS be explicit when converting datatypes.. No room for the usual VB type conversion laziness here
#


The comment about using parameters is important.. even the process i describe here is not as good as using parameters. USE PARAMETERS.

[kaumudi]

thanks.. i'll surely try this out..
i m not too good at oracle .. thanks once again..
kaumudi