Programming SafGrid - Any row but first cannot be edited and saved - Msg 6900

(0) Share

Report

Posted on by Mary Letourneau

100

I have a "simple" grid I want to create to edit a table that has four key fields. This grid should work similar to the Chart of Accounts Maintenance. I can insert and delete as many rows as I want. I can edit and save the first row. Any other row I can edit, but when I save I get the message : System Message 6900 - Another process has updated the Rate Table table. The system will automatically Cancel your changes.

Here's the Form1 code:

Call ApplInit()

Call SetAddr(LEVEL0, "bxPTIRate", bxPTIRate, nxPTIRate)

Call SqlCursorEx(CSR_xPTIRate, LEVEL0, "CSR_xPTIRate", "xPTIRate", "xPTIRate")

Call ScreenInit()

mh_xPTIRate = DetailSetup(CSR_xPTIRate, SafGrid1, PNULL, bxPTIRate, PNULL, PNULL, PNULL)

------

The sql that created the table:

Create Table xPTIRate

(

CpnyId

char (10) not null,

InsBalShtAcct

char (10) not null,

InsExpAcct

char (10) not null,

InsRate

float not null,

LaborAcct

char (10) not null,

NoteId

int not null,

OtherBalShtAcct

char (10) not null,

OtherExpAcct

char (10) not null,

OtherRate

float not null,

State

char (10) not null,

TaxBalShtAcct

char (10) not null,

TaxExpAcct

char (10) not null,

TaxRate

float not null,

TaxTable

char (10) not null,

User1

char (30) not null,

User2

char (30) not null,

User3

float not null,

User4

float not null,

User5

char (10) not null,

User6

char (10) not null,

User7

smalldatetime not null,

User8

smalldatetime not null,

VacBalShtAcct

char (10) not null,

VacExpAcct

char (10) not null,

VacRate

float not null,

WCBalShtAcct

char (10) not null,

WCExpAcct

char (10) not null,

WCRate

float not null,

tstamp

Timestamp not null,

constraint

xPTIRate0 primary key (CpnyId, State, TaxTable, LaborAcct))

-----

The Update1 Levels is set to : RateTable;D

-----

All the fields on Form1 are set to Level 0, including the primary key fields. Setting the four key fields to 0,k does not help.

-----

DBNav on SafGrid1: "xPTIRate_p1",

-----

xPTIRate_p1 Procedure:

ALTER

proc [dbo].[xPTIRate_p1] as

select

* from xPTIRate

order

by CpnyId, State, TaxTable, LaborAcct

-----

I have successfully created detail grids using autonumber for the last primary key. I thought this would be easier ....

*This post is locked for comments

All responses (6)

Answers (1)

Mary Letourneau 100 on at

Like (0)

Report

Re: Programming SafGrid - Any row but first cannot be edited and saved - Msg 6900

Barry --

Thank you!

That, along with using "LIKE" in the procedure for all parameter comparisons (instead of "=" for all but the last), gets the job done.
Verified answer

Barry Flynn 3,090 on at

Like (0)

Report

Re: Programming SafGrid - Any row but first cannot be edited and saved - Msg 6900

Mary

If I can add a comment.

I have a screen something like yours.

It is in the same style as the chart of accounts screen - there is only a grid.

The table involved has two key fields.

In the grid's DBNav property, there are two parameters (one for each of the two key fields), and both have the wildcard box checked.

Similarly, the controls for both the key fields have the Level parameter speciofied as 0,k.

Hope that helps.

Barry
Mary Letourneau 100 on at

Like (0)

Report

Re: Programming SafGrid - Any row but first cannot be edited and saved - Msg 6900

Paul --

If I am following what you are saying correctly, this means it is not possible to do what I'm trying to do. My table has four keys -- your solution allows for only one key to be a wildcard parameter in the DBNAV. With your solution, the closest I could get is to have three of the four as fields to fill in at the top of the screen (a header) and then the grid below (detail) populated based on the three keyed values with a wildcard for the fourth to populate the grid. I would need two levels, a Normal and a Detail level.

If this is true, at least I can stop trying to bang my head against a door that won't open. I'll just have to come up with a different approach to maintaining this table.

Thank you for your response,

Mary
Paul Phillips 590 on at

Like (0)

Report

Re: Programming SafGrid - Any row but first cannot be edited and saved - Msg 6900

Hi Mary,

The structure of the Grid's DBNav is very important. The statement serves two purposes:

1. It loads the whole grid. Sometimes parameters are passed into it from other controls on the screen if a user can apply a filter in the user interface as in your example. Sometimes there aren't any filters - it is essentially showing the entire underlying table.

2. It also provides the SL kernel with the information to 'know' how to update or delete a single row in the database during save.

Number two is the important piece, and why you always have to have at least one parameter on the DBNav statement. The kernel is going to read the SQL text out of your DBNav when it loops through the grid during save (Update event). It gets the SQL either from the text entered into the property window, or from the stored procedure text in the database. Using a stored procedure is 'best practice' and the standard way of doing it.

While looping through the grid, it executes an UPDATE statement for any row in the grid whose line status has been set to UPDATED. In order to do that, it must be able to read the same statement used to load the entire grid, re-write it to make it an INSERT, and narrow the UPDATE operation to a single row scope by using the last parameter.

For rows with a status of INSERTED, it will execute an INSERT. Deletes are held in another array which is also looped through, but the SL kernel still has to know how to build a delete statement using the same process as UPDATE.

If the field that can uniquely narrow the SQL statement to a single row is a character type, the SQL WHERE clause must be something like 'WHERE fieldname LIKE @parm1'. In the case of an integer field, you actually need two int parameters e.g. 'WHERE intfieldname BETWEEN @parm1 AND @parm2'.

When the grid loads, a wildcard ('%') is passed as the last parameter (or a min and max value in the case of an int), to load all the rows in the grid from the database. When it is time to update a row in the database, SL uses the same statement but passes the actual field value(s) so that the UPDATE or DELETE statement is narrowed to a single row.

Here are a couple of examples:

Select * from TableA
where TableA.StringField LIKE @parm1
Order By StringField

Select * from TableA
where TableA.StringFieldA = @parm1
and TableA.IntegerFieldB BETWEEN @parm2 and @parm3
Order By StringFieldA, IntegerFieldB

The DBNav property in the SL_SDK.pdf guide talks about these points in more detail. In my V7 copy it is page 75 and 76.

One thing you'll find is that SL is extremely particular about details, and those details are often not documented. The best thing to do sometimes is to copy an existing screen precisely as a starting point; of course, you can't see the grid in customize mode so it is a challenge. Sometimes it just takes many hours of trying things to finally figure out a very minor issue. Those three possibilities I added were the result of that type of troubleshooting on separate occasions in the past. I suggest keeping notes for every error/issue and how you solved it - they come in handy.

For the other question, when you see a screen jump down to a row when you are adding a new record in a grid, this is usually because that field is marked as a key field and the grid's array has been sorted by it, such as on the Chart of Accounts Maintenance screen.
Mary Letourneau 100 on at

Like (0)

Report

Re: Programming SafGrid - Any row but first cannot be edited and saved - Msg 6900

Thank you for your response.

1. Since I don't have parameters in the procedure, none of them can be too small

2. I'm having no problems with inserting.

3. How would I check for this problem?

I understand what you're saying about the DBNAV, but this grid is supposed to populate from the entire table -- this grid is supposed to work more like a Chart of Accounts screen - the entire table is loaded into the grid, as opposed to most others where you start off filling in some header information and then the detail populates such as in the Journal Transactions screen. If I used parameters, I would need all of them to be wildcards for the grid to load the whole table, and this does not seem to work well ... unless I was doing it wrong.

One of the things I've noticed about detail screens that do not use autonumber for primary key: when a row is inserted (at the bottom) and saved, the screen reloads and places the inserted line in order. How do these screens do that?

I have since noticed that I am having problems with deleting ... it is deleting the first row, regardless of the row I am on. So I am thinking that the problem has to do with the system tracking what row in the table I am on and then using that row (and not the first row) to delete or edit. I have had some message screens pop up for debugging letting me know what the system thinks my key values are, and while I'm on the row it is displaying the correct data. It just isn't deleting or trying to update that row - it is trying to delete or update the first row.

Thank you,

Mary
Paul Phillips 590 on at

Like (0)

Report

Re: Programming SafGrid - Any row but first cannot be edited and saved - Msg 6900

Hi Mary,

I have seen this error message in a few circumstances, which may or may not apply to your scenario:

1. A varchar parameter for a DBNav stored procedure was too small for the value being passed

2. The system is attempting to insert a row into a table with a blank column in the primary key

3. Some cursor flags (SqlSingleRow) cause an immediate select / dummy update, which in some cases can cause this error

Also, the DBNAV on a grid typically has at least one parameter, which the system uses as part of the update routine when a given grid row is being saved. The grid's DBNAV statement serves two purposes - one is to load the grid, but the other is to allow the system to use the same statement to update a single row in the database. This could be causing your problem.

Regards,

Paul

Programming SafGrid - Any row but first cannot be edited and saved - Msg 6900

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

Programming SafGrid - Any row but first cannot be edited and saved - Msg 6900

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates