Inserting date values into oracle table

[bnour]

Gurus

I've been struggling with my code since yesterday trying to find out a way to pass date values from an excel sheet into an oracle table field with date datatype.
Can you help me?


Thanks
bnour

[DonkeyOte]

Might help if you post your code (be sure to encase within code tags of course)

In the meantime - check out: http://www.daniweb.com/forums/thread58023.html

[bnour]

Here is my code:

[vb]
Sub LoadData_Click()

Dim sqlstring As String
Dim WS As Worksheet
Dim HS As Worksheet
Dim Options As String
Dim Values As String
Dim executeSQL As Boolean

Dim uValue As String
Dim sValue As String
Dim dValue As String
Dim datValue As Date
Dim tuValue As Date
Dim mValue As String
Dim tValue As String
Dim bValue As String
Dim rvValue As String
Dim rValue As String
Dim btValue As String
Dim bfValue As String
Dim wValue As String
Dim hlValue As String
Dim bhValue As String
Dim srValue As String
Dim mbrValue As String
Dim trValue As String
Dim tfValue As String
Dim taValue As String
Dim ppValue As String
Dim mfiValue As String
Dim mfoValue As String
Dim miValue As String
Dim moValue As String
Dim eValue As String
Dim ptValue As String
Dim mValue As String

Dim RowNo As Integer
Dim ColNo As Integer
Dim lastRow As Integer
Dim lastCol As Integer

Application.ScreenUpdating = False
initEnv

Sheets("Data").Select

RowNo = 5
lastRow = Cells(RowNo, 1).End(xlDown).Row

dbConnect

'If dataQC = True Then
For RowNo = 5 To lastRow

uValue = Cells(RowNo, 1).Value
sValue = Cells(RowNo, 2).Value
dValue = Cells(RowNo, 3).Value
datValue = Cells(RowNo, 4).Value
tuValue = Cells(RowNo, 5).Value
mValue = Cells(RowNo, 6).Value
tValue = Cells(RowNo, 7).Value
bdValue = Cells(RowNo, 8).Value
rfValue = Cells(RowNo, 9).Value
rmValue = Cells(RowNo, 10).Value
btValue = Cells(RowNo, 11).Value
bfValue = Cells(RowNo, 12).Value
wValue = Cells(RowNo, 13).Value
hlValue = Cells(RowNo, 14).Value
bhValue = Cells(RowNo, 15).Value
srValue = Cells(RowNo, 16).Value
mbrValue = Cells(RowNo, 17).Value
trValue = Cells(RowNo, 18).Value
tfValue = Cells(RowNo, 19).Value
taValue = Cells(RowNo, 20).Value
ppValue = Cells(RowNo, 21).Value
mfiValue = Cells(RowNo, 22).Value
mfoValue = Cells(RowNo, 23).Value
miValue = Cells(RowNo, 24).Value
moValue = Cells(RowNo, 25).Value
eValue = Cells(RowNo, 26).Value
ptValue = Cells(RowNo, 27).Value
mValue = Cells(RowNo, 28).Value

Options = " (U , Source, TYPE, DDATE, TIME, MD, TV, DEPTH, FTHR, MNFT, TIME, BI, WB, HK_, BLK_, SURFACE_, RPM, TOTAL_RPM, TRQ_FTLB, TRQ_AMP, PRESS, M_IN, M_OUT, MU_IN, MU_OUT, ED, TOTAL, ME )"
Values = ("'" & uwiValue & "', '" & sValue & "', '" & dtValue & "', to_date( '" & datValue & "', 'mm/dd/yyyy' ) , to_date( '" & tValue & "', 'hh:mi:ss' ) , '" & mdValue & "', '" & tvdValue & "', '" & bdValue & "', '" & rfValue & "', '" & rmValue & "', '" & btValue & "', '" & bfValue & "', '" & wValue & "', '" & hlValue & "', '" & bhValue & "', '" & srValue & "', '" & mbrValue & "', '" & trValue & "', '" & tfValue & "', '" & taValue & "', '" & ppValue & "', '" & mfiValue & "', '" & mfoValue & "', '" & miValue & "', '" & moValue & "', '" & eValue & "', '" & ptValue & "', '" & mValue & "'")
'Values = ("'" & uwiValue & "', '" & sValue & "', '" & dtValue & "', '" & Format(datValue, "hh:mm:ss") & "' , '" & mdValue & "', '" & tvdValue & "', '" & bdValue & "', '" & rfValue & "', '" & rmValue & "', '" & btValue & "', '" & bfValue & "', '" & wValue & "', '" & hlValue & "', '" & bhValue & "', '" & srValue & "', '" & mbrValue & "', '" & trValue & "', '" & tfValue & "', '" & taValue & "', '" & ppValue & "', '" & mfiValue & "', '" & mfoValue & "', '" & miValue & "', '" & moValue & "', '" & eValue & "', '" & ptValue & "', '" & mValue & "'")

sqlstring = "Insert into " & oraUser & "." & tblname & Options & " VALUES (" & Values & " )"
cnnAdo.executeSQL sqlstring

Next RowNo
MsgBox "Loading data is completed"
'End If

Set cnnAdo = Nothing
Application.ScreenUpdating = True

End Sub

[/vb]

I can enter now date values using to_date function, but I cannot enter time format values.

Any clue?

Thanks
Bassem