Could not refresh Detail View after inserting record. No records were found ...

[rnoguera]

I have a grid with Updateable Detail view (SQL).
Datasource is a SQL View.

When I Insert a New Record and press Save I get this message:

"Could not refresh Detail View after inserting record. No records were found when executing the 'Refresh row' query."

The insertion itseld is succesful, but Alpha5 is not able to refresh the detail view.

I guess it is related with the Identity field. It shows "0" before the record is saved, then SQL gives it a value and Alpha5 is not able to retrieve this new value.

If I change the Datasource to a SQL Table, the problem does not happen, but I miss a lot of fields that I need and are not in the table.

Preventing the Detail View from refreshing would be an acceptable solution, since I close the Detail View after Saving anyway.

Thanks in advance for your help.

[aburningflame]

same issue.
However, having the detail view close after is not acceptable - so i need to figure out this refresh. anyone?

[tOlorun]

please can anyone help with this error ... occurring after inserting into a grid ... a big thank you in advance

[tOlorun]

Please can anyone help in resolving this issue ... thank you in advance ...

I have a grid with Updateable Detail view (SQL).
Datasource is a SQL View.

When I Insert a New Record and press Save I get this message:

"Could not refresh Detail View after inserting record. No records were found when executing the 'Refresh row' query."

The insertion itseld is succesful, but Alpha5 is not able to refresh the detail view.

I guess it is related with the Identity field. It shows "0" before the record is saved, then SQL gives it a value and Alpha5 is not able to retrieve this new value.

If I change the Datasource to a SQL Table, the problem does not happen, but I miss a lot of fields that I need and are not in the table.

Preventing the Detail View from refreshing would be an acceptable solution, since I close the Detail View after Saving anyway.

Thanks in advance for your help.

Please can anyone help in resolving this issue ... thank you in advance ...

[CarlMPearson]

I'm having the same issue with a critical data table, albeit a simple one.

I created a SIMPLE test table. Nothing exotic. I'm closing the detail after submit. Still get the error. I get it with both AlphaADO and SQL Statements.

I'm having it with several versions of A5, both AppServer and Developer.

SQL Server 2008 in full 2008 mode.

This is only happening with a SQL R/O grid with updatable detail view.

See attached image.

[CarlMPearson]

I'm not using a view, just a simple table, and still get the issue. However, I am using an Identity as the primary key, so the clue below of ID being zero until submitted looks relevant.

If I don't use an Identity Primary Key it does not have the issue. Too bad, using an Identity PK is VERY useful in many situations. There has to be a way.

[RaEd.]

Just came across this thread, having the issue myself. Was a solution ever found?

[bjacobson]

Ah, my fading memory ... I know I've seen this issue too, and gotten around it, but can't remember how. It may have been by explicitly turning off Update for the fields that did not come from the primary table.

[aburningflame]

This is usually caused by:
Your grid uses some sort of INNER JOIN instead of a LEFT JOIN.

Example
SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.field=tbl2.field

When you insert a record - if no child record is put into table 2 - the select above will not bring back the newly added record. Hence the 'No Records found'.

Another reason for this is if you are not properly setting the e.lastInsertedIdentity in the OnBeforeSQLCommandExecute (this assumes you have coded your own OBSQLCE event).

Its mostly likely an inner join problem. Change the INNER join to a LEFT join.

[rnoguera]

For updatable grids, do NEVER, EVER use SQL Server defined Views. Use Tables instead.
If you need to use several tables, use the A5 SQL Query Builder to build the views inside A5.

Set all Ready-Only tables to Read-Only.

Since I do this, I never meet the problem again.

[lvasic]

This is from an older post I put out there.

Row Refresh Problem Solved with stored procedure!

sql2 = "Insert into vLocationRef (Patients_ID, Facility_ID)
(:Patients_ID, :Facility_ID)"
flag = e.cn.execute(sql2,args)
If flag = .t. then
e.flagError = .f.
e.errorText = ""
e.rowsAffected = e.cn.affectedRows()
e.SQLToDisplay = sql2
e.ArgsToDisplay = ""
Sql2 = "exec dbo.spGetLocationRef_ID :Patients_ID, :UserIdAdd"
flag = e.cn.Execute(sql2,args)
if flag then
rs = e.cn.ResultSet
e.lastInsertedIdentityValue = rs.data(1)
e.rv.V.R1.LOCATIONREF_ID = e.lastInsertedIdentityValue
End If
else
e.flagError = .t.
e.errorText = e.cn.callresult.text
e.rowsAffected = 0
end if

[aburningflame]

You didnt have to use a stored procedure.
You are using a workaround/hackish method to get the grid to work.
The grid should work by itself and if it doesn't then something else is wrong.

E.g the inner/left join I said before.
Basically this error occurs like this:

You add data to your grid.
You or the grid run insert code.
The grid tries to refresh the data for the row by using hte grids SQL statement - it appends a WHERE clause using the e.lastInsertedIdentityValue.
If the above statement does not find records - this error will be produced.

Examples:
Grid statement SELECT * FROM client INNER JOIN client_information ON client.client_id=client_information.client_id
Grid: Allows for insertion of client but does not insert any corresponding record into client_information
Add data and hit Save - The error will be produced.

Example:
Client table
cliID 1 First Name: Joe Last Name: Bob

Client Information
No corresponding records

After insert lets say we add Jane Doe.
The statement will be:

SELECT * FROM client INNER JOIN client_information ON client.client_Id=client_information.client_id WHERE client.client_id=2 <<This statement will not return a record because the client information does not contain a corresponding record for client ID 2 and then INNER join will omit the records because of this.

The solution:
Grid statement SELECT * FROM client LEFT JOIN client_information ON client.client_id=client_information.client_id

This will return the client records even if there are no client_information records.

Another Problem:
In your grid events: make sure the e.lastInsertedIdentityValue is being set to e.cn.CallResult.lastInsertedIdentity (you may have to look these up - i forgot the exact name of these).


These are the 2 major problems that result in this record (from my experience): I have trouble shooted this error many many times and it 95% one of the above 2 problems.

[lvasic]

Also, you can use a view with multiple tables, however, on the view, you need to code the instead of trigger. The stored procedure will bring back the last identity and load it into e.lastInsertedIdentityValue. I wouldn't consider this a workaround. This view has all left joins. Some views are just more complex than others.

[StephenA]

I had the same problem. If fixed it in "Grid > Query > Select Primary Key". Select the primary key (if you haven't already) and check the "Primary Key is auto-increment?" checkbox (if your id is auto-increment like mine).

[irsandi]

The problem seem to be that you had populate a computed column in the select statement, and at the field property in grid it was set to updateable.

In my case, I just remove this computed field.
I changed the logic, instead of using a select expression, I used a client expression in a grid property.
and it worked fine