Could not refresh detail view after inserting record. No records were found when...

[aburningflame]

Could not refresh detail view after inserting record. No records were found when executing the refresh row query.


I get this error when i try to insert a record.
I have code in my OnBeforeSQLCommandExecute event.

I DO set the e.lastInsertedIdentity field - so Im not sure why the detail view is not refreshing.

Can anyone lend a hand?

Thanks,
Scott

[aburningflame]

Re: Could not refresh detail view after inserting record. No records were found when.

The following code produces the error when I insert the record:
What is wrong here?

e.SQLCommandWasHandled = .t.
e.flagError=.f.
e.errorText=""

dim args as SQL::Arguments
args.XML=e.argsXML
IF e.cn.begintransaction() then
  	IF e.cn.Open("::name::conn") then
    	IF e.cn.execute(e.sql,args) then
       e.lastInsertedIdentityValue=e.cn.CallResult.LastInsertedIdentity
    	else 
    	'error occurred
    		e.flagError=.t.
    		e.errorText="Error Occurred: Could not execute primary statement" 
    	end if
	else
	'error occurred
		e.flagError=.t.
    	e.errorText="Error Occurred: Could not open database connection" 
	end if	 
else
	 e.flagError=.t.
    e.errorText="Error Occurred: Could not begin transaction" 
end if

'commit or rollback based on error flag
IF e.flagError==.f. then
    IF e.cn.committransaction() ==.f. then
        e.cn.rollbacktransaction();
        e.flagError=.t.
        e.errorText="Error Occurred: Could not commit transaction" 
    end if
else
	e.cn.rollbacktransaction();
end if

[jin_Bat]

Re: Could not refresh detail view after inserting record. No records were found when.

had the same issue. my problem was that i had a grid that as a data source had 2 tables joined. on the edit detail screen, i was saving a record where the joined table's primary id was left as blank. by me filling that in, it fixed the issue and my grid refreshed properly...

[frankbicknell]

Re: Could not refresh detail view after inserting record. No records were found when.

IF e.flagError==.f. then
    IF e.cn.committransaction() ==.f. then
        e.cn.rollbacktransaction();
        e.flagError=.t.
        e.errorText="Error Occurred: Could not commit transaction" 
    end if
else
	e.cn.rollbacktransaction();
end if

The above code does not make sense since the e.cn.rollbacktransaction() would execute either way. The event you are using is an Alpha server side event in xbasic you use only one equals sign.

I am not sure of what you are trying to accomplish. Why do you need code in that event?

[aburningflame]

Re: Could not refresh detail view after inserting record. No records were found when.

the above code makes perfect sense

alpha treats == perfectly (even tho it can use 1 equal - i program in stricter languages which take = as assignment and == as comparison - so its a habit)

if e.flagerror is false and e.cn.committransaction returns true - rollback is never called.

if e.flagError==.f. then
'try to commit
if e.cn.committransaction()==.f. then
'if we cant commit then rollback
end if

'if the commit was succesful - there is no rollback and the commit() call goes through
else

'an error occurred so rollback
end if

If the commit is succesful the rollback wont run - weve been using this method forever.

the whole point is a transaction.
for example

Insert Order
->Insert Order Audit Record

if the audit record fails - we want to rollback everything.

we have used this method in later versions of alpha - but i guess the bug is version related - we wanted to code around it because upgrading the server is NOT an option - i guess we have to get rid of transaction logic (big no no :/)

i guess we just cant do this in previous versions of alpha - code works fine in later versions

[frankbicknell]

Re: Could not refresh detail view after inserting record. No records were found when.

Thank you. Right there it is the help file. == (Exactly Matching Operator) I have never used that event and I was wondering why someone would. Another case of fingers moving while brain is in stand by.

[JH10]

Re: Could not refresh detail view after inserting record. No records were found when.

Hi Scott
Not sure if you have solved this but I had the same thing today. After some tests, I realized I was trying to use a Child Field (a field from the sub table) in the Detail View Fields.
Hope this is clear.
Regards
John

[aburningflame]

Re: Could not refresh detail view after inserting record. No records were found when.

Thank you. Right there it is the help file. == (Exactly Matching Operator) I have never used that event and I was wondering why someone would. Another case of fingers moving while brain is in stand by.

No problem :)
That event can be used to modify the grids sql, or arguments.
Can also be used to start transactions.

Say you had a table with a field named quarterEntered (1,2,3,4). You can use the OnBeforeSQLCommand to look at the value of date() determine what quarter its in and use

dim args as SQL::Arguments
args.XML=e.argsXML
args.set("quarterEntered", quarterCalculatedAbove)
e.modifiedSQL=e.sql
e.modifiedArgsXML=args.XML

this would let you override values

[aburningflame]

Re: Could not refresh detail view after inserting record. No records were found when.

Hi Scott
Not sure if you have solved this but I had the same thing today. After some tests, I realized I was trying to use a Child Field (a field from the sub table) in the Detail View Fields.
Hope this is clear.
Regards
John

Hi John,
Thanks for your advice - I will keep this in mind.
However - we decided to code around it.
We use this method in the new version of alpha - maybe this was a bug in the old version..not sure.

anyhow thanks fo ryour input

[BenRo]

Re: Could not refresh detail view after inserting record. No records were found when.

:DGood News on this ... Problem Solved.:D

This reads long, but the solution is really easy.

Okay, I ran across this myself today when adding new records via a detail grid. And, I think I completely understand what is causing this. At least for me ...

The problem identified: Alpha 5 expects to see the new record in the grid when the users clicks the "Save" button (in the detail view). I expect it is going to place it on the grid for you to review after you click the "Save" button. If new record does not meet the criteria defined for the main grid, then Voila! It cannot find the records and you get this message "Could not refresh Detail View after inserting record. No records were found when executing the 'Refresh row' query."

The Solution: Rewrite the query so the new record meets the query criteria of the main grid. If you are experienceing the same issue, then your SQL probaly is written as a SQL "INNER" join and needs to be changed to "OUTER" joins. If you use the Query builder it seems to use "INNER" joins by default. You SQL gurus experiencing this issue are likley going ... oh yeah at this point. For the newbies ...read up in INNER and OUTER joins.

In my case, I had joined two tables to a main table using inner joins. The newly created record did not meet the query using the "INNER" join when I changed to outer join, reran the same steps on the same grid with the new "OUTER" join verion of the query, then Succes no message and problem was solved.

This is related to comments by "Jin Bat 11-01-2010, 04:03 PM" as part of this thread. However this provides an explanation as to why this occurs and allows, a good all around solution that avoids requiring users to fill in all fields when the data should be blnk or NULL.

Possible Wish List Request to A5: In the Details view> Properties provide programmers an option to ignore this error/feature and provide a popup to users.

Also, I am ssure there is a way advanced A5 programmers can trap this situation, provide their own message, and all users to move forward. If they really wanted to.

Hope this helps someone.

Regards,
Ben

[achung]

Re: Could not refresh detail view after inserting record. No records were found when.

:DGood News on this ... Problem Solved.:D

This reads long, but the solution is really easy.

Okay, I ran across this myself today when adding new records via a detail grid. And, I think I completely understand what is causing this. At least for me ...

The problem identified: Alpha 5 expects to see the new record in the grid when the users clicks the "Save" button (in the detail view). I expect it is going to place it on the grid for you to review after you click the "Save" button. If new record does not meet the criteria defined for the main grid, then Voila! It cannot find the records and you get this message "Could not refresh Detail View after inserting record. No records were found when executing the 'Refresh row' query."

The Solution: Rewrite the query so the new record meets the query criteria of the main grid. If you are experienceing the same issue, then your SQL probaly is written as a SQL "INNER" join and needs to be changed to "OUTER" joins. If you use the Query builder it seems to use "INNER" joins by default. You SQL gurus experiencing this issue are likley going ... oh yeah at this point. For the newbies ...read up in INNER and OUTER joins.

In my case, I had joined two tables to a main table using inner joins. The newly created record did not meet the query using the "INNER" join when I changed to outer join, reran the same steps on the same grid with the new "OUTER" join verion of the query, then Succes no message and problem was solved.


Regards,
Ben

Hello --

In my case, I only have one main table with 3 fields: ID, FIRSTNAME, LASTNAME. I experience the same problem as others have when adding NEW records only via a detail grid. Issue appears to be the ID, which I have set as an autoincrement field in Firebird SQL.

Is there a way to correct this using XBASIC or on the SQL backend so that the value of the autoincrement field is retrieved before the Detail View fires?

Thanks.
Andrew

[andychung]

Re: Could not refresh detail view after inserting record. No records were found when.

Hi --

I have a very simple grid based on one table with no child tables. I have inserted the following code (based on burnings example) into onBeforeSQLExecute event but this results in the error shown in the attached image. The ID field is autoincremented in Firebird SQL:
--
e.SQLCommandWasHandled = .t.
e.flagError=.f.
e.errorText=""

dim args as SQL::Arguments
args.XML=e.argsXML
IF e.cn.begintransaction() then
IF e.cn.Open("{A5API='ODBC',DataSource='AlphaSQLTest',A5Syntax='ODBC',UserName='SYSDBA',Password='masterkey'}") then
IF e.cn.execute(e.sql,args) then
e.lastInsertedIdentityValue=e.cn.CallResult.LastInsertedIdentity
else
'error occurred
e.flagError=.t.
e.errorText="Error Occurred: Could not execute primary statement"
end if
else
'error occurred
e.flagError=.t.
e.errorText="Error Occurred: Could not open database connection"
end if
else
e.flagError=.t.
e.errorText="Error Occurred: Could not begin transaction"
end if

'commit or rollback based on error flag
IF e.flagError==.f. then
IF e.cn.committransaction() ==.f. then
e.cn.rollbacktransaction();
e.flagError=.t.
e.errorText="Error Occurred: Could not commit transaction"
end if
else
e.cn.rollbacktransaction();
end if
end function
--
The DB is connecting properly but what am I missing here?
Thx.
Andy

[OfficePro]

Re: Could not refresh detail view after inserting record. No records were found when.

Hello
we use firebirdSQL, you need to get ID from server before to
post your record, we use generators in firebirdSQL and we
populate the ID field getting a new ID from server before to post record to database.

Regards.
Marco

[andychung]

Re: Could not refresh detail view after inserting record. No records were found when.

Hi Marco --
Do you have any sample code that you would be able to share?
Thanks.
Andy