Pls help me.. with this .. pls.. vn.net n oracle..
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:
Code:
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..
Re: Pls help me.. with this .. pls.. vn.net n oracle..
str = "insert into edt values('a','" & DateTime.Now.ToShortDateString() & "')"
In the future, look into using parameterized queries.
Re: Pls help me.. with this .. pls.. vn.net n oracle..
Quote:
Originally Posted by Craig Gemmill
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
Re: Pls help me.. with this .. pls.. vn.net n oracle..
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. :)
Re: Pls help me.. with this .. pls.. vn.net n oracle..
thanks.. i'll surely try this out..
i m not too good at oracle .. thanks once again..
kaumudi