Problems displaying the table's primary key in the DataTable for an Editor

ahanssens@cps247.com

I'm using Editor 1.5.0 with the .NET libraries.

The primary key in my table is named RowID, and I'd like to display it in the table. When the Editor object is created I tell it that the primary key is "RowID" and I create a Field object with the name "RowID". (There is another column named "HomePort" in the table which also has a Field object.)

If server-side processing is turned off, the following SQL statement is generated by Editor._Get:

SELECT  RowID as 'RowID', RowID as 'RowID', HomePort as 'HomePort' FROM  HomePortList 

This statement includes the RowID column twice, because the code in Editor._Get adds both the primary key and the name in the Field object to the query. Although this makes the statement look at bit odd, it doesn't cause an error.

However, if server-side processing is turned on, the following SQL statement is generated by Editor._Get:

SELECT  RowID as 'RowID', RowID as 'RowID', HomePort as 'HomePort' FROM  HomePortList  ORDER BY RowID  asc  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

This causes SQL Server to return an error complaining that the column name "RowID" is ambiguous.

I can't see any way to get around this without changing the code in Editor_Get. So far, this change seems to make things work properly:

            // Add all fields that we need to get from the database
            foreach (var field in _field)
            {
                if (field.Apply("get") && field.GetValue() == null && field.DbField() != _pkey)
                {
                    query.Get(field.DbField());
                }
            }

However, it seems like a better solution would be that whenever the Editor needs to insert a column into the query for its own reasons, it uses "as" to give the column a unique name. If it was doing that, you would end up with a query like

SELECT  RowID as 'ZZZ_ORDERING_VALUE', RowID as 'RowID', HomePort as 'HomePort' FROM  HomePortList  ORDER BY ZZZ_ORDERING_VALUE  asc  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

which would not cause any problems.

allan

HI,

Thanks for your post. Agreed! The alias approach is what should be used here and I will include that in a future version of Editor - probably not 1.5.1, but the next release.

Allan