How To insert Date From VB6.0 to Oracle

[Pankaj]

Hi There,

I tried to insert date in oracle data using VB..but
gives me error 'Inconsistance data type'...

Can u suggest how i can do tihs?

Thanks,
Pankaj

[John Blessing]

Post by Pankaj
Hi There,
I tried to insert date in oracle data using VB..but
gives me error 'Inconsistance data type'...
Can u suggest how i can do tihs?
Thanks,
Pankaj

Please show us your code.

[Pankaj]

Hi John,

This is code i am using.

Set db = New ADODB.Connection

Conn = "UID= FPOWER_PAN" & ";PWD=FPOWER_ PAN " & ";DRIVER={Microsoft
ODBC For Oracle};" _
& "SERVER= PAN.WORLD" & ";"


With db
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With

Set cFund = db.Execute("Insert into FZTempTbl(EntryDate) Values(" &
ADate & ")")

Where aDate is system Date.

Thanks,
Pankaj

[John Blessing]

Post by Pankaj
Hi John,
This is code i am using.
Set db = New ADODB.Connection
Conn = "UID= FPOWER_PAN" & ";PWD=FPOWER_ PAN " & ";DRIVER={Microsoft
ODBC For Oracle};" _
& "SERVER= PAN.WORLD" & ";"
With db
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
Set cFund = db.Execute("Insert into FZTempTbl(EntryDate) Values(" &
ADate & ")")
Where aDate is system Date.
Thanks,
Pankaj

First problem, don't use ODBC, use an OLEDB provider. , typically:


Provider=MSDAORA.1;Data Source=PAN.WORLD;User ID=FPOWER_PAN;Password=FPOWER_
PAN


Second, you haven't shown what aDate is. Do you mean it is a date variable?
If so, you are relying on Oracle being able to interpret it correctly,
instead you should tell Oracle what format it is in:

db.Execute("Insert into FZTempTbl(EntryDate) Values(" & _
"TO_DATE('" & Format(ADate , "DD-MMM-YYYY") & "','DD-MON-YYYY'))")

[Pankaj]

Hi John,

Thanks for ur help,
code is working now..

Pankaj

[Fz]

If you are using the oracle client to connect to the database then, there is
really nothing extra you need to do.

If IsDate(txtDte2Prod.Text) Then
dyUpdate!dte2prod = txtDte2Prod.Text
Else
MsgLog = MsgLog & "'Date to Prod' is not a date; "
End If

and txtDte2Prod.Text is almost any VB supported date format. the Oracle
client will do the conversion to the proper oracle format for you.

If you are using ODBC I believe (it has been a long time since I used odbc)
you need to provide the date in oracle system format; DD-MON-YYYY

Dan...

Post by Pankaj
Hi There,
I tried to insert date in oracle data using VB..but
gives me error 'Inconsistance data type'...
Can u suggest how i can do tihs?
Thanks,
Pankaj

[tonisukmana]

how to format the date plus hours minutes seconds ? I got the error
vardate = Format ( ADate , "dd-mmm-yyyy hh:mm:ss" )

db.Execute("Insert into FZTempTbl(EntryDate) Values(" & _
"TO_DATE('" & vardate & "')

any one help me??

[GS]

Post by tonisukmana
how to format the date plus hours minutes seconds ? I got the error
vardate = Format ( ADate , "dd-mmm-yyyy hh:mm:ss" )
db.Execute("Insert into FZTempTbl(EntryDate) Values(" & _
"TO_DATE('" & vardate & "')
any one help me??

Does your date include time? This does...

?format(now(), "dd-mmm-yyyy hh:mm:ss")

..and returns...

"19-Jan-2015 16:28:07"

..when typed in the Immediate Window!

[Deanna Earley]

Post by tonisukmana
how to format the date plus hours minutes seconds ? I got the error
vardate = Format ( ADate , "dd-mmm-yyyy hh:mm:ss" )
db.Execute("Insert into FZTempTbl(EntryDate) Values(" & _
"TO_DATE('" & vardate & "')

It must be in a specific format:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ
(First result for "oracle sql date literal")

db.Execute "Insert into FZTempTbl(EntryDate) Values(TIMESTAMP '" &
Format(vardate, "yyyy-mm-dd hh:mm:ss") & "')"