Simple grid wont load error expanding grid part template.

[samyot]

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

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

[samyot]

Re: Simple grid wont load error expanding grid part template.

I retested my grid by removing 1 field at a time to find out that as soon as i add the geography type field i get an error: EXCEPTION CAUGHT CREATING SQLGEOGRAPHY OBJECT
Is alpha incompatible with the SQL type Geography?

[lvasic]

Re: Simple grid wont load error expanding grid part template.

Are you using the same SQL database in live preview?

[samyot]

Re: Simple grid wont load error expanding grid part template.

Yes i am ,
just did a lot of test it works as long as i dont add the Geography type field in my grid, but if i dont add the geography field i cant update the geography field when i do the import!

[lvasic]

Re: Simple grid wont load error expanding grid part template.

There is a sql geo type file that needs to be installed on your server. It's probably installed on your developer computer.

[samyot]

Re: Simple grid wont load error expanding grid part template.

you know how and where I can get this file?

[lvasic]

Re: Simple grid wont load error expanding grid part template.

This posted in 2013 by Islip:
Application Server - Geography/Geometry Fields - Unhandled Exception - Some users have reported that when using a Grid that had a Geography or Geometry field that they were getting an unhandled exception. We believe that this was because the SQL Server data types were not installed on the machine. The SQL Server data types are needed by Alpha Five regardless of whether you are using SQL Server, or some other SQL backend. The App Server install program was installing the .msi installer file, but was not executing it. To correct the problem you can go to the folder where the App Server is installed, and you can execute this file (which comes from Microsoft):

SQLSysClrTypesx86.msi

[lvasic]

Re: Simple grid wont load error expanding grid part template.

Here's the link: https://www.alphasoftware.com/alphaf...QL+Server+2008

[samyot]

Re: Simple grid wont load error expanding grid part template.

I just checked, did an install and it was already installed it has to be something else.

I wish it would a been just that.

[samyot]

Re: Simple grid wont load error expanding grid part template.

I did a small grid with just adresse, lat, long and geography location field. the grid loaded on server but when I tried to save the data in the
grid I got this message:
ERR.PNG
ERR1.PNG

[jacktheripper]

Re: Simple grid wont load error expanding grid part template.

Sylvain, were you able to find a fix for this? Just recently updated to the latest build and now have this issue with my geometry field. Have also run SQLSysClrTypesx86.msi which did not work. Thanks!