Using Version 12.4.5.2 build 4770 system addins: build 5018
with sql server 2008
I Created a grid to upload from excel, with an Alternate view 'google map',
I import data from excel then validate the import and geocode all adress.
In live preview and locally in browser everything works as it should be.
when published to the server and I run it from the web I get this message.
Capture.PNG
Error expanding grid part template: result set row requested not found while getting result set data column "NO_BON_FICTIF"
My datasource is an SQL table.
here is an image of the grid running locally on Chrome:
Capture2.PNGCapture3.PNG
I have 3 function in xbasic but they dont get call until a button is press for each one.
This function to clean and validate the data when it is imported:
function Validate as v (e as p)
dim t as p
t = table.open(e.inputFilename)
'If you turn on batch begin, scripts that loop over records in a table are significantly faster
t.batch_begin()
dim i as n
dim count as n
Dim bonfictif as C = ""
Dim connection as c = "::name::sc360app"
Dim Mtable as c = "dbo.MM"
Dim Mfilter as c =""
Dim cn as SQL::Connection
Dim ErrTable as c ="dbo.importErroFile"
dim MlastName as c
Dim MfirstName as c
Dim GAddress as c
dim GAddress2 as c
dim typerue as c
dim typerue2 as c
DIM WC as n
Dim Location as P
dim rue as c
DIM BONFAIT AS d
if cn.open(connection) = .f. then
e.javascript="alert('Error, Could not connect to database. Error was: " + cn.CallResult.text + "');"
end
end if
cn.PortableSQLEnabled = .t.
dim sqlInsertStatement as c
dim args as sql::arguments
DIM MARUE AS C
DIM MARUE2 AS C
DIM RUED AS c
count = t.records_get()
for i = 1 to count
bonfictif = sql_lookup(connection,Mtable,Mfilter,"NO_BON_FICTIF")
'verify if record exist before import and save it to error table end delete.
if bonfictif <> null_value() .or. t.VISITE_NON_REQUISE = .t. then
t.change_begin()
t.NOM_CLIENT = MlastName + ", " + MfirstName
args.set("RowNumber",i+1)
args.set("ErrNumerodeBon",t.NO_BON_FICTIF)
args.set("ErrNumeroDeClient",""+ t.NUM_CLIENT)
args.set("ErrNomClient",alltrim(t.NOM_CLIENT))
args.set("ErrorAddClient",alltrim(t.ADRESSECOMPLETE))
args.set("ErrDate",Date())
if t.VISITE_NON_REQUISE = .t. then
args.set("ErroDesc","Visite Non Requise")
ELSE
args.set("ErroDesc","Bon Existe deja")
end if
sqlInsertStatement = <<%sql%
INSERT INTO dbo.importErrorFile (RowNumber,ErrNumerodeBon,ErrNumeroDeClient,ErrNomClient,ErrorAddClient, ErrDate,ErrorDesc)
VALUES (:RowNumber,:ErrNumerodeBon,:ErrNumeroDeClient, :ErrNomClient,:ErrorAddClient, :ErrDate,:ErroDesc)
%sql%
if cn.Execute(sqlInsertStatement, args)= .f. then
e.javascript = "alert('Error, Record was not inserted. Error was: " + cn.CallResult.text + "');"
end if
t.delete()
t.change_end(.t.)
else
The address doesn't exist so I begin to validate the record and get geolocation data
t.change_begin()
WC= w_count(t.NOM_CLIENT)
if WC >3 then
t.NOM_CLIENT = alltrim(t.NOM_CLIENT)
Else
MlastName = lastword(t.NOM_CLIENT)
MfirstName = rTrim(t.NOM_CLIENT,MlastName)
t.NOM_CLIENT = alltrim(MlastName) + ", " + alltrim(MfirstName)
end if
RUED = lastword(t.RUE)
MARUE = t.RUE
MARUE2 = t.RUE
typerue2 = t.TYPERUE
if RUED = "E" .OR. RUED = "O" .OR. RUED= "N" .OR. RUED= "S" THEN
MARUE = rTrim(MARUE,RUED)
MARUE2 = rTrim(MARUE2,RUED)
MARUE = allTrim(MARUE) + " " + RUED
ELSE
MARUE = rTrim(MARUE)
MARUE2 = rTrim(MARUE2)
end if
BONFAIT = t.DT_COM
If Year(BONFAIT) > 1900 then
t.CodedeCompletion = "Fait"
end if
t.RUE = MARUE
t.IMPDT = Year(Date())+"-"+Month(Date())+"-"+Day(Date())
if RUED = "O" then
MARUE2 = allTrim(MARUE2) + " Ouest"
end if
if RUED = "E" then
MARUE2 = allTrim(MARUE2) + " Est"
end if
if RUED = "N" then
MARUE2 = allTrim(MARUE2) + " Nord"
end if
if RUED = "S" then
MARUE2 = allTrim(MARUE2) + " Sud"
end if
if typerue2 = "AV" then
typerue2 = " "
MARUE2 = MARUE2 + " Avenue"
end if
if typerue2 = "BOUL" then
typerue2 = "BLVD"
end if
if typerue2 = "TSSE" then
typerue2 = "TERRACE"
end if
if typerue2 = "CAR" then
typerue2 = "CARRE"
end if
if typerue2 = "CROIS" then
typerue2 = "Croissant"
end if
GAddress2 = rTrim(t.NOCIVIQUE) + " " + rtrim(typerue2) + " " + Rtrim(MARUE2) + ", " + rTrim(t.VILLE)+ ", " + RTrim(t.CODEPOSTAL)+ ", Quebec"
Location = geocode_address(GAddress2, "google")
if Location.error_code = 0 then
t.ADDRLAT = Location.lat
t.ADDRLON = Location.lon
else
GAddress2 = Rtrim(MARUE2) + ", " + rTrim(t.VILLE)+ ", " + RTrim(t.CODEPOSTAL)+ ", Quebec"
Location = geocode_address(GAddress2, "google")
if Location.error_code = 0 then
t.ADDRLAT = Location.lat
t.ADDRLON = Location.lon
else
GAddress2 = RTrim(t.CODEPOSTAL)+ ", Quebec"
Location = geocode_address(GAddress2, "google")
if Location.error_code = 0 then
t.ADDRLAT = Location.lat
t.ADDRLON = Location.lon
else
end if
end if
end if
t.change_end(.t.)
t.fetch_next()
end if
next i
cn.close()
t.batch_end()
t.close()
end function
this one is to get geocoding on a single line by using an action button
function GetMyLATLONG as c (e as p)
dim GAddress AS c =RTrim(e._currentRowDataNew.NOCIVIQUE) + " " + RTrim(e._currentRowDataNew.TYPERUE)+" "+ RTrim(e._currentRowDataNew.RUE) +", " + RTrim(e._currentRowDataNew.VILLE)+ ", " + RTrim(e._currentRowDataNew.CODEPOSTAL)+ ", Quebec"
dim mp as p
mp = geocode_address(GAddress, "Google")
if mp.error_code = 0 then
e._set.ADDRLAT.value = mp.lat
e._set.ADDRLON.value = mp.lon
else
GAddress = RTrim(e._currentRowDataNew.RUE) +", " + RTrim(e._currentRowDataNew.VILLE)+ ", " + RTrim(e._currentRowDataNew.CODEPOSTAL)+ ", Quebec"
mp = geocode_address(GAddress, "Google")
if mp.error_code = 0 then
e._set.ADDRLAT.value = mp.lat
e._set.ADDRLON.value = mp.lon
else
GAddress = RTrim(e._currentRowDataNew.CODEPOSTAL)+ ", Quebec"
mp = geocode_address(GAddress, "Google")
if mp.error_code = 0 then
e._set.ADDRLAT.value = mp.lat
e._set.ADDRLON.value = mp.lon
end if
end if
end if
end function
and I use this one to do a batch geocoding on all address that did not return with Lat and Long.
Function CreateLocation as c (e as p)
dim cn as sql::Connection
flag = cn.open("::Name::sc360app")
dim sql as c
dim Mtable as c = "dbo.MM"
dim rs as sql::ResultSet
sql = "SELECT NO_BON_FICTIF FROM MM WHERE ADDRLAT = 0"
cn.Execute(sql)
rs = cn.ResultSet
dim flag as l
flag = rs.nextRow()
delete args
dim args as sql::Arguments
dim sql1 as c
Dim Location as P
DIM tempadd as c
sql1 = "select NOCIVIQUE, RUE, VILLE, CODEPOSTAL, ADDC, ADDRLAT, ADDRLON, CODEPOSTAL from dbo.MM where NO_BON_FICTIF = :primaryKey"
sql2 = "update dbo.MM set ADDRLAT = :lat, ADDRLON = :lon,IMPFLAG =0, ADDGEOGRAPHY = GEOGRAPHY::Point(:lon, :lat,4326) where NO_BON_FICTIF = :primaryKey"
dim pa as p
dim rs2 as sql::ResultSet
while flag
args.add("primaryKey",rs.data(1))
flag = cn.Execute(sql1,args)
rs2 = cn.ResultSet
tempadd = rs2.data(1) + " " + rs2.data(2) + ", " + rs2.data(3) + ", " + rs2.data(4)
Location = geocode_address(tempadd , "google")
if Location.error_code = 0 then
args.add("lat",Location.lat)
args.add("lon",Location.lon)
else
tempadd = rs2.data(2) + ", " + rs2.data(3) + ", " + rs2.data(4)
Location = geocode_address(tempadd , "google")
if Location.error_code = 0 then
args.add("lat",Location.lat)
args.add("lon",Location.lon)
else
tempadd = rs2.data(3) + ", " + rs2.data(4)
Location = geocode_address(tempadd , "google")
if Location.error_code = 0 then
args.add("lat",Location.lat)
args.add("lon",Location.lon)
else
args.add("lat",0)
args.add("lon",0)
end if
end if
end if
flag = cn.execute(sql2,args)
flag = rs.nextRow()
end while
end function
with sql server 2008
I Created a grid to upload from excel, with an Alternate view 'google map',
I import data from excel then validate the import and geocode all adress.
In live preview and locally in browser everything works as it should be.
when published to the server and I run it from the web I get this message.
Capture.PNG
Error expanding grid part template: result set row requested not found while getting result set data column "NO_BON_FICTIF"
My datasource is an SQL table.
here is an image of the grid running locally on Chrome:
Capture2.PNGCapture3.PNG
I have 3 function in xbasic but they dont get call until a button is press for each one.
This function to clean and validate the data when it is imported:
function Validate as v (e as p)
dim t as p
t = table.open(e.inputFilename)
'If you turn on batch begin, scripts that loop over records in a table are significantly faster
t.batch_begin()
dim i as n
dim count as n
Dim bonfictif as C = ""
Dim connection as c = "::name::sc360app"
Dim Mtable as c = "dbo.MM"
Dim Mfilter as c =""
Dim cn as SQL::Connection
Dim ErrTable as c ="dbo.importErroFile"
dim MlastName as c
Dim MfirstName as c
Dim GAddress as c
dim GAddress2 as c
dim typerue as c
dim typerue2 as c
DIM WC as n
Dim Location as P
dim rue as c
DIM BONFAIT AS d
if cn.open(connection) = .f. then
e.javascript="alert('Error, Could not connect to database. Error was: " + cn.CallResult.text + "');"
end
end if
cn.PortableSQLEnabled = .t.
dim sqlInsertStatement as c
dim args as sql::arguments
DIM MARUE AS C
DIM MARUE2 AS C
DIM RUED AS c
count = t.records_get()
for i = 1 to count
Mfilter = "NO_BON_FICTIF =" + "'" + t.NO_BON_FICTIF + "'"
bonfictif = sql_lookup(connection,Mtable,Mfilter,"NO_BON_FICTIF")
'verify if record exist before import and save it to error table end delete.
if bonfictif <> null_value() .or. t.VISITE_NON_REQUISE = .t. then
t.change_begin()
t.NOM_CLIENT = MlastName + ", " + MfirstName
args.set("RowNumber",i+1)
args.set("ErrNumerodeBon",t.NO_BON_FICTIF)
args.set("ErrNumeroDeClient",""+ t.NUM_CLIENT)
args.set("ErrNomClient",alltrim(t.NOM_CLIENT))
args.set("ErrorAddClient",alltrim(t.ADRESSECOMPLETE))
args.set("ErrDate",Date())
if t.VISITE_NON_REQUISE = .t. then
args.set("ErroDesc","Visite Non Requise")
ELSE
args.set("ErroDesc","Bon Existe deja")
end if
sqlInsertStatement = <<%sql%
INSERT INTO dbo.importErrorFile (RowNumber,ErrNumerodeBon,ErrNumeroDeClient,ErrNomClient,ErrorAddClient, ErrDate,ErrorDesc)
VALUES (:RowNumber,:ErrNumerodeBon,:ErrNumeroDeClient, :ErrNomClient,:ErrorAddClient, :ErrDate,:ErroDesc)
%sql%
if cn.Execute(sqlInsertStatement, args)= .f. then
e.javascript = "alert('Error, Record was not inserted. Error was: " + cn.CallResult.text + "');"
end if
t.delete()
t.change_end(.t.)
else
The address doesn't exist so I begin to validate the record and get geolocation data
t.change_begin()
WC= w_count(t.NOM_CLIENT)
if WC >3 then
t.NOM_CLIENT = alltrim(t.NOM_CLIENT)
Else
MlastName = lastword(t.NOM_CLIENT)
MfirstName = rTrim(t.NOM_CLIENT,MlastName)
t.NOM_CLIENT = alltrim(MlastName) + ", " + alltrim(MfirstName)
end if
RUED = lastword(t.RUE)
MARUE = t.RUE
MARUE2 = t.RUE
typerue2 = t.TYPERUE
if RUED = "E" .OR. RUED = "O" .OR. RUED= "N" .OR. RUED= "S" THEN
MARUE = rTrim(MARUE,RUED)
MARUE2 = rTrim(MARUE2,RUED)
MARUE = allTrim(MARUE) + " " + RUED
ELSE
MARUE = rTrim(MARUE)
MARUE2 = rTrim(MARUE2)
end if
GAddress = rTrim(t.NOCIVIQUE) + " " + rTrim(t.TYPERUE) + " " + MARUE + ", " + rTrim(t.VILLE) + ", " + rTrim(t.CODEPOSTAL) + ", Quebec"
BONFAIT = t.DT_COM
If Year(BONFAIT) > 1900 then
t.CodedeCompletion = "Fait"
end if
t.RUE = MARUE
t.IMPDT = Year(Date())+"-"+Month(Date())+"-"+Day(Date())
if RUED = "O" then
MARUE2 = allTrim(MARUE2) + " Ouest"
end if
if RUED = "E" then
MARUE2 = allTrim(MARUE2) + " Est"
end if
if RUED = "N" then
MARUE2 = allTrim(MARUE2) + " Nord"
end if
if RUED = "S" then
MARUE2 = allTrim(MARUE2) + " Sud"
end if
if typerue2 = "AV" then
typerue2 = " "
MARUE2 = MARUE2 + " Avenue"
end if
if typerue2 = "BOUL" then
typerue2 = "BLVD"
end if
if typerue2 = "TSSE" then
typerue2 = "TERRACE"
end if
if typerue2 = "CAR" then
typerue2 = "CARRE"
end if
if typerue2 = "CROIS" then
typerue2 = "Croissant"
end if
GAddress2 = rTrim(t.NOCIVIQUE) + " " + rtrim(typerue2) + " " + Rtrim(MARUE2) + ", " + rTrim(t.VILLE)+ ", " + RTrim(t.CODEPOSTAL)+ ", Quebec"
t.ADDC = GAddress2
Location = geocode_address(GAddress2, "google")
if Location.error_code = 0 then
t.ADDRLAT = Location.lat
t.ADDRLON = Location.lon
else
GAddress2 = Rtrim(MARUE2) + ", " + rTrim(t.VILLE)+ ", " + RTrim(t.CODEPOSTAL)+ ", Quebec"
Location = geocode_address(GAddress2, "google")
if Location.error_code = 0 then
t.ADDRLAT = Location.lat
t.ADDRLON = Location.lon
else
GAddress2 = RTrim(t.CODEPOSTAL)+ ", Quebec"
Location = geocode_address(GAddress2, "google")
if Location.error_code = 0 then
t.ADDRLAT = Location.lat
t.ADDRLON = Location.lon
else
end if
end if
end if
t.change_end(.t.)
t.fetch_next()
end if
next i
cn.close()
t.batch_end()
t.close()
end function
this one is to get geocoding on a single line by using an action button
function GetMyLATLONG as c (e as p)
dim GAddress AS c =RTrim(e._currentRowDataNew.NOCIVIQUE) + " " + RTrim(e._currentRowDataNew.TYPERUE)+" "+ RTrim(e._currentRowDataNew.RUE) +", " + RTrim(e._currentRowDataNew.VILLE)+ ", " + RTrim(e._currentRowDataNew.CODEPOSTAL)+ ", Quebec"
dim mp as p
mp = geocode_address(GAddress, "Google")
if mp.error_code = 0 then
e._set.ADDRLAT.value = mp.lat
e._set.ADDRLON.value = mp.lon
else
GAddress = RTrim(e._currentRowDataNew.RUE) +", " + RTrim(e._currentRowDataNew.VILLE)+ ", " + RTrim(e._currentRowDataNew.CODEPOSTAL)+ ", Quebec"
mp = geocode_address(GAddress, "Google")
if mp.error_code = 0 then
e._set.ADDRLAT.value = mp.lat
e._set.ADDRLON.value = mp.lon
else
GAddress = RTrim(e._currentRowDataNew.CODEPOSTAL)+ ", Quebec"
mp = geocode_address(GAddress, "Google")
if mp.error_code = 0 then
e._set.ADDRLAT.value = mp.lat
e._set.ADDRLON.value = mp.lon
end if
end if
end if
end function
and I use this one to do a batch geocoding on all address that did not return with Lat and Long.
Function CreateLocation as c (e as p)
dim cn as sql::Connection
flag = cn.open("::Name::sc360app")
dim sql as c
dim Mtable as c = "dbo.MM"
dim rs as sql::ResultSet
sql = "SELECT NO_BON_FICTIF FROM MM WHERE ADDRLAT = 0"
cn.Execute(sql)
rs = cn.ResultSet
dim flag as l
flag = rs.nextRow()
delete args
dim args as sql::Arguments
dim sql1 as c
Dim Location as P
DIM tempadd as c
sql1 = "select NOCIVIQUE, RUE, VILLE, CODEPOSTAL, ADDC, ADDRLAT, ADDRLON, CODEPOSTAL from dbo.MM where NO_BON_FICTIF = :primaryKey"
sql2 = "update dbo.MM set ADDRLAT = :lat, ADDRLON = :lon,IMPFLAG =0, ADDGEOGRAPHY = GEOGRAPHY::Point(:lon, :lat,4326) where NO_BON_FICTIF = :primaryKey"
dim pa as p
dim rs2 as sql::ResultSet
while flag
args.add("primaryKey",rs.data(1))
flag = cn.Execute(sql1,args)
rs2 = cn.ResultSet
tempadd = rs2.data(1) + " " + rs2.data(2) + ", " + rs2.data(3) + ", " + rs2.data(4)
Location = geocode_address(tempadd , "google")
if Location.error_code = 0 then
args.add("lat",Location.lat)
args.add("lon",Location.lon)
else
tempadd = rs2.data(2) + ", " + rs2.data(3) + ", " + rs2.data(4)
Location = geocode_address(tempadd , "google")
if Location.error_code = 0 then
args.add("lat",Location.lat)
args.add("lon",Location.lon)
else
tempadd = rs2.data(3) + ", " + rs2.data(4)
Location = geocode_address(tempadd , "google")
if Location.error_code = 0 then
args.add("lat",Location.lat)
args.add("lon",Location.lon)
else
args.add("lat",0)
args.add("lon",0)
end if
end if
end if
flag = cn.execute(sql2,args)
flag = rs.nextRow()
end while
end function
Comment