Hi,
I want to push data from Lotus to Oracle 10g using DSN.I have used the following code:
Option Public
Uselsx “nlsxodbc.dll”
Sub Initialize
Dim db As NotesDatabase
Dim usr As String
Dim pswd As String
Dim dtb As String
Dim view As NotesView
Dim doc As NotesDocument
Dim a,b,c,d,e,f,g As Double
Dim X As String
Dim totalhrs As Double
Dim session As New NotesSession
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Set db = session.CurrentDatabase
Set view = db.GetView("x")
Set doc= view.GetFirstDocument
usr = "Y"
pswd = "y"
dtb = "Y"
If Not con.ConnectTo(dtb, usr, pswd) Then
Messagebox " Connection Failed",MB_OK,"Connection to Database"
Exit Sub
End If
Set qry.Connection = con
Set result.Query = qry
qry.SQL = "SLECT * FROM Z"
result.execute
While Not ( doc Is Nothing )
' &&&&&&&&&&& Actual Hrs clac. &&&&&&&&&&&&&&&&&77
If Cstr(doc.i(0)) ="" Then
a = 0
Else
a = Cdbl(doc.i(0))
End If
If Cstr(doc.j(0)) ="" Then
b = 0
Else
b = Cdbl(doc.j(0))
End If
If Cstr(doc.k(0)) ="" Then
c = 0
Else
c = Cdbl(doc.k(0))
End If
If Cstr(doc.l(0)) ="" Then
d = 0
Else
d = Cdbl(doc.l(0))
End If
If Cstr(doc.m(0)) ="" Then
e = 0
Else
e = Cdbl(doc.m(0))
End If
If Cstr(doc.n(0)) ="" Then
f = 0
Else
f = Cdbl(doc.n(0))
End If
If Cstr(doc.o(0)) ="" Then
g = 0
Else
g = Cdbl(doc.o(0))
End If
totalhrs = a + b + c + d+ e + f + g
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
result.AddRow
Call result.SetValue ("PRJ_NO",Cstr(doc.ProjectNumber(0)))
' Call result.Setvalue("PRJ_NO",Cstr(doc.ProjectNumber(0)))
Call result.Setvalue("GROUP_NAME",Cstr(doc.Group(0)))
Call result.Setvalue("SUB_GROUP",Cstr(doc.SubGroup(0)))
Call result.Setvalue("REQ_DATE",Cdat(doc.RequestDate(0)))
If Cstr(doc.CompletedDate(0)) <> "" Then
Call result.Setvalue("COMPL_DAT",Cdat(doc.CompletedDate(0)))
Else
Call result.Setvalue("COMPL_DAT","")
End If
Call result.Setvalue("ACTUAL_HRS",totalhrs)
Call result.Setvalue("PHASE",Cstr(doc.ProjectPhase(0)))
If Cstr(doc.Priority(0)) <> "" Then
Call result.Setvalue("PRIORITY",Cstr(doc.Priority(0)))
Else
Call result.Setvalue("PRIORITY","N/A")
End If
result.UpdateRow
Call doc.Save(True, False)
Set doc = view.GetNextDocument( doc )
Wend
con.CommitTransactions
result.Close(DB_CLOSE)
con.Disconnect
Exit Sub
End Sub
The code does not throw any error while connecting, but does not update the table. It even connects with the table.
Can any one help me?