Single Record Form: Rows Counter [x of y] shows x > y

Scriptcase 8 Bugs
#1

This happens when a form has a WHERE clause set.
Can NetMake please review and fix this?

To reproduce the bug I created a table containing only the following records
(the “id” field is not shown below but it exists in the table and it is an auto increment integer):

Field1   Field2
---------------
man      walter
man      jim
woman    jane

I’ve created a single record form with all default settings, changing only these settings:
Application -> Navigation -> Return after Inserting: Yes
SQL -> Where Condition -> Field1 = “man”

and running the form shows correctly that it has loaded the first of 2 records having Field1 = “man”: [1 of 2].

After adding a a new record filling field1 with “man”
The record is added and the form returns with the total amount of records calculated correctly: 3 (correct taking into consideration the where clause),
But the current record position between square brackets is wrong [4 of 3].
4 is the the table total records: 3 man + 1 woman, so SC is ignoring the form where clause:

Note that when inserting the third “man” record, if the table had 105 records with fields1 <> “man”, the Row Counter would show: [108 of 3].

SQL debugging on

Enabling debug mode the following select statements are shown:

[B](pdo-mysql): select last_insert_id()  
(pdo-mysql): SELECT COUNT(*) FROM table1 WHERE ((id < 10)) [/B]

Is the record position (4) wrong because it is calculated by using “WHERE id < last_insert_id”? (see statements above).
If so, the error is caused by ignoring the form original where clasue.
Shouldn’t the correct statement be SELECT COUNT(*) FROM table1 WHERE ((id < 10) AND (field1 = “man”)) ?

Another select shown by debugging:

 
(pdo-mysql): SELECT count(*) from table1 where field1 = "man"

Note that the total amount of records (3) is correctly calculated by taking into consideration the form original where clause.

#2

hi roby
did you try to upgrade your version, perhaps it was a bug and fixed in the last 3 versions
also that is i assume the whole where clause it wrong then if calculating 105 instead of 3 or 4? or only the showing count of records is the problem!? did you manage to click next previous and see if you actually can see the other records?
also did you try transactional db, not pdo?

#3

Hi MikeDe,

I’m always using the latest version.

Usually only moving to first record works and reset the form to a usable state.

i assume the whole where clause it wrong then if calculating 105 instead of 3 or 4?

I was talkin about a table with 108 total records of which:

  • 105 with field1 = "woman"
  • 3 with field1 = "man"

SC calculated [108 of 3]

And this to me seems to be:
[Total-Records_WithoutWhere-Clause of Total-RecordsWith_Where-Clause]

The wrong one is the Total-Records_Without_Where-Clause: instead it should be the current record position (and of course it must always be <= Total-Records_With_Where-Clause)

#4

Got your point, and your explanation above seems more than enough to produce, hopefully guys (perhaps bartho) can reproduce and locate the issue ASAP because it seems critical!

I just wanted to highlight using transactional connection for a test, Carols many times informed that PDO should not be used but only in certain cases.

#5

MikeDe,

I just tried both Transactional and Non-Transactional but the error is still the same.

It is critical as it renders single forms not usable for record insertion if these settings are used:

  • Application -> Navigation -> Return after Inserting: Yes
  • SQL -> Where Condition -> [some-conditions]