Hi,
I have a problem with some LSX code.
Dim session As New notessession
Dim db As notesdatabase
Dim vandaag As New Notesdatetime("TODAY")
Call vandaag.adjustday(-1)
Dim xdate As Notesdatetime
Dim xpdagint As Integer
Dim xpdagstr As String
Dim errorval As Integer
Dim db2src As New LCConnection ("db2")
Dim db2fields As New LCFieldList
Dim fetchList As New LCFieldList
Dim strQueryString As String
Dim tqry As String
Dim x As Integer
Dim dt As String
Dim msg As String
Dim t_Database As String
Dim t_Userid As String
Dim t_Password As String
Dim t_Lib As String
Dim t_File As String
Dim pnr As String
Dim vrnr As String
Dim pf As Long
Dim totgp As Long
Dim vtot As Long
Dim btot As Long
Dim totbe As Long
Dim tyear As String
Dim tmonth As String
Dim tday As String
Dim pdagint As Integer
Dim pdagstr As String
Dim strWeeknr As String
Dim Weeknr As Integer
Dim key(1) As String
Dim datevar As Notesdatetime
Call setglobals
'SET DE DRIE WEEKNUMMERS
strWeeknr = GetCalendarWeek( vandaag )
Weeknr = Cint(strWeeknr)
Set vandaag = New Notesdatetime("Today")
Call vandaag.adjustday(-1)
xpdagint = Weekday(vandaag.lslocaltime)
xpdagstr = converttodagstring(xpdagint)
On Error Goto errorHandler
Set db = session.CurrentDatabase
Dim cdoc As notesdocument
Dim cview As notesview
Dim allview As notesview
Set allview = db.getview("ALLPROG")
Set cview = db.getview("CONPROGVR")
Set cdoc = cview.getfirstdocument
If cdoc Is Nothing Then
Call GlbLogDoc.SetNormalMessage(7, "FOUT", "Connectiedocument is niet aanwezig","","","")
End If
Print "Setup connection strings ..."
t_database = cdoc.conPROGDB(0)
t_Userid = cdoc.conPROGUserid(0)
t_password = cdoc.conPROGPassword(0)
t_lib = cdoc.conPROGLibLSX(0)
t_file = cdoc.conPROGFile(0)
'Opstarten connectie
Print "Setup connection strings ..."
db2src.Database =t_database
db2src.UserID = t_Userid
db2src.Password = t_password
db2src.Metadata = t_lib & "." & t_file ' bijv. "kdcprddta.pfor"
Print "Ready for connecting ..."
db2src.Connect
Print "Connected ..."
strQueryString = "Select OGKCA#, SUM(OGBAC@) sbesteld, Sum(OGGPK@) sgepickt From KDCPRDDTA.PFOG Where OGAFW# =" + strweeknr + " AND OGADDD = " + Day(vandaag.lslocaltime) + " group by OGKCA# "
Call GlbLogDoc.SetNormalMessage(1, "BEZIG", "inlezen gepickt gisteren","in database ",db.title,"")
Set db2fields = New LCFieldList 'initialize fieldlist
'Execute query
Print "Query = " + strQueryString
Call db2src.Execute (strQueryString, db2fields) 'execute the sql statement on the source
Print "Executed query"
Dim doc As notesdocument
Dim LSXOGKCA As LCField 'Artikel verkoop
Dim LSXOGBAC As LCField 'Aantal besteld
Dim LSXOGGPK As LCField 'Aantal gepickt
Dim OGKCA As Variant 'Artikel verkoop
Dim OGBAC As Variant 'Aantal besteld
Dim OGGPK As Variant 'Aantal gepickt
Dim varbesteld As Integer
Dim vargepickt As Integer
Set db = session.CurrentDatabase
Call fetchList.Map(db2fields,"OGKCA#, sbesteld, sgepickt")
Set LSXOGKCA = fetchList.GetField(1)''ARTIKELNUMMER verkoop
Set LSXOGBAC = fetchList.GetField(2) 'Besteld
Set LSXOGGPK= fetchList.GetField(3)'Gepickt
db2src.MapByName = True ' because the fields in the result set are not in this order.
While (db2src.Fetch(fetchList,1,1) > 0)
OGKCA = LSXOGKCA.Text(0) 'ARTIKELNUMMER verkoop
OGBAC = LSXOGBAC.Text(0) 'Besteld
OGGPK = LSXOGGPK.Text(0) 'Gepickt
vrnr = Cstr(Clng(OGKCA))
key(0) = vrnr
key(1) = strweeknr
'tyear = 2000 + Cint(OGAJJD)
tyear = Year(vandaag.lslocaltime)
tmonth = Month(vandaag.lslocaltime)
tday = Day(vandaag.lslocaltime)
'Set xdate = New Notesdatetime(Datenumber (tyear, Cint(OGAMMD) , Cint(OGADDD) ))
Set xdate = New Notesdatetime(Datenumber (tyear, Cint(tmonth) , Cint(tday) ))
pdagint = Weekday(xdate.lslocaltime)
pdagstr = converttodagstring(pdagint)
'Week document ophalen
Set doc = allview.getdocumentbykey(key, True) ' True geeft aan dat het een exact match moet zijn. 04-11-02
'BEGIN INLEZEN GEPICKTE GEGEVENS
If Not doc Is Nothing Then
Select Case pdagstr
Case "Maandag"
vargepickt = doc.werkGPMA_1(0) + Clng(OGGPK)
btot = doc.werkVPMA(0) + Clng(OGGAB)
totbe = doc.werkGPMA(0) + Clng(OGBAC)
doc.werkGPMA = totbe
doc.werkGPMA_1 = vargepickt
doc.werkVPMA = btot
Case "Dinsdag"
vargepickt = doc.werkGPDI_1(0) + Clng(OGGPK)
btot = doc.werkVPDI(0) + Clng(OGGAB)
totbe = doc.werkGPDI(0) + Clng(OGBAC)
doc.werkGPDI = totbe
doc.werkGPDI_1 = vargepickt
doc.werkVPDI = btot
Case "Woensdag"
vargepickt = doc.werkGPWOE_1(0) + Clng(OGGPK)
btot = doc.werkVPWOE(0) + Clng(OGGAB)
totbe = doc.werkGPWOE(0) + Clng(OGBAC)
doc.werkGPWOE = totbe
doc.werkGPWOE_1 = vargepickt
doc.werkVPWOE = btot
Case "Donderdag"
vargepickt = doc.werkGPDO_1(0) + Clng(OGGPK)
btot = doc.werkVPDO(0) + Clng(OGGAB)
totbe = doc.werkGPDO(0) + Clng(OGBAC)
doc.werkGPDO = totbe
doc.werkGPDO_1= vargepickt
doc.werkVPDO = btot
Case "Vrijdag"
vargepickt = doc.werkGPVRIJ_1(0) + Clng(OGGPK)
btot = doc.werkVPVRIJ(0) + Clng(OGGAB)
totbe = doc.werkGPVRIJ(0) + Clng(OGBAC)
doc.werkGPVRIJ = totbe
doc.werkGPVRIJ_1 = vargepickt
doc.werkVPVRIJ = btot
Case "Zaterdag"
vargepickt = doc.werkGPZA_1(0) + Clng(OGGPK)
btot = doc.werkVPZA(0) + Clng(OGGAB)
totbe = doc.werkGPZA(0) + Clng(OGBAC)
doc.werkGPZA = totbe
doc.werkGPZA_1 = vargepickt
doc.werkVPZA = btot
Case "Zondag"
vargepickt = doc.werkGPZO_1(0) + Clng(OGGPK)
btot = doc.werkVPZO(0) + Clng(OGGAB)
totbe = doc.werkGPZO(0) + Clng(OGBAC)
doc.werkGPZO = totbe
doc.werkGPZO_1 = vargepickt
doc.werkVPZO = btot
End Select
Call doc.computewithform(False,False)
Call doc.save(True,False)
End If
Wend
Print "Ready for disconnecting ..."
db2src.Disconnect
The code chrashes in this line:
Call fetchList.Map(db2fields,“OGKCA#, sbesteld, sgepickt”)
The reason is probably that the fields “sbesteld” and “sgepickt” are created in the query and are not avalible in the file on the iSeries.
But how do I solve this? I have searched the helpfile but I can´t find it.
I would appreciate any help.