Hello,
I have following agent which works with out any error. But does not insert a record in to SQL table.
I have even checked the permissions on tables, it looks fine.
Any idea what is wrong in this code?
Thanks in advance.
MS
Code1 - This code does not give any error but does not insert data in table
Sub Initialize
’ Update PO Keys agent called
Dim session As New NotesSession
Dim doc As NotesDocument
Dim Item As NotesItem
Dim currentLog As NotesLog
Dim Poview, view2 As NotesView
Dim Postatus As String
Dim Apps As Variant
Dim AppsDate As Variant
Dim AppBy As String
Dim AppByDate As String
Dim key As String
Dim view As NotesView
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim msg As String
Dim user, as400ID As String
user = session.CommonUserName
Dim userview As NotesView
Dim userdoc As NotesDocument
Set db = session.CurrentDatabase
Set view = db.GetView (“byba” )
Set doc = view.GetDocumentByKey (user)
If Not (doc Is Nothing) Then
dbsource$ = doc.dbsource(0)
Else
Exit Sub
End If
Set con = New ODBCConnection
Set qry.Connection = con
Set result.Query = qry
temp = con.ConnectTo(dbsource$, “”,“”)
qry.SQL = “SELECT * FROM POSUM”
result.Execute
rows = result.NumRows
Set view2 = db.getview(“SQLInsert”)
Set doc2=view2.GetFirstDocument
While Not (doc2 Is Nothing)
result.NextRow
result.AddRow
a= doc2.PoByr(0)
b=doc2.PoAst(0)
c=doc2.PoPlan(0)
Call result.SetValue(“VPOBYR”,a)
Call result.SetValue(“VPOAST”,b)
Call result.SetValue(“VPOPLN”,c)
result.UpdateRow
Call doc2.ReplaceItemValue(“sqlrecupdated”, “Yes”)
Call doc2.Save(True,True)
Set doc2=view2.GetNextDocument(doc2)
On Error Resume Next
Wend
result.Close(DB_CLOSE)
con.Disconnect
End Sub
code2 - tried
for this Query1
QRY.SQL = " INSERT INTO DBO.POSUM (POBYR, POAST, POPLN) values (’ & a & ’ , ’ & b & ’ , ’ & c &’ ) "
Gives following error
OK
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
For this Query2
QRY.SQL = " INSERT INTO DBO.POSUM (POBYR, POAST, POPLN) values (" & a & “,” & b & “,” & c &") "
Gives following error
OK
[Microsoft][ODBC SQL Server Driver][SQL Server]The name “Test” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.