How to fix invalid 'Invalid column name' after adding a column in the table

How to fix invalid 'Invalid column name' after adding a column in the table

  

Hi,


I have added a column in to my existing table. But, it is giving me an error.


I don't know what is going on.


        Invalid column name 'COMPANYDESIGNATION'.

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

       at System.Data.SqlClient.SqlDataReader.get_MetaData()

       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

       at OutSystems.HubEdition.DatabaseProvider.SqlServer.ExecutionService.ExecutionService.ExecuteReader(IDbCommand cmd)

       at #gCb.#BCb.ExecuteReader(String description, Boolean isApplication, Boolean transformParameters, Boolean skipLog)

       at OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd, GenericRecordList`1 rl, String description, Boolean transformParameters, Boolean skipLog)

       at OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd, GenericRecordList`1 rl, String description)

 

Why is it giving me an error.



Thanks in advance.

Steps taken:


1.Change the name

2. Set the column to not required.

3. Try to add another column and see if it works

4. Try to Republish again

5. Check Database itself

    > The columns weren't created. This is a bit weird as attribute has been added in the Entity


6. Refreshed all dependencies.

7. Check Service Center Error Log every time you do fixes

    > an error under 'System' espace...it says that "Public elements not found for eSpace ...."

Stack:

ssServiceCenter.Actions.WsPlatformServices_v8_0_0.WebSrvcPlatformServices_v8_0_0Module_GetPublicElements(HeContext heContext, String inParamUsername, String inParamPassword, String inParamModuleVersionKey, RLCS_PublicElementVersionRecordList& outParamElements)
   at ssServiceCenter.WebServices.PlatformServices_v8_0_0.Module_GetPublicElements(String inWSUsername, String inWSPassword, String inWSModuleVersionKey, 

WORCCS_PublicElementVersionRecord[]& outWSElements)


I believe this error is another error message for another issue. This is unrelated to the entity problem.


Hi Melody ,

When are you getting this error , compile time or during any page event?


Regards

-PJ-

Pramod Jain wrote:

Hi Melody ,

When are you getting this error , compile time or during any page event?


Regards

-PJ-


Hi PJ,

I get the error during a page event.


I have done the steps above. And, it seems like the database didn't create the columns in the table.

It's a bit weird as the attributes were added in the entity. But how come it's not reflected in the database?


Thanks,

Melody


what are you doing in the page?

have you refreshed everything?


J. wrote:

what are you doing in the page?

have you refreshed everything?


Hi J,


I have refreshed all dependencies as well.


But, no luck.



Hi, 

As of today, the issue still persists.

When I added a new attribute 'TestField' to the entity 'Contact', compile and publish -- I get this error:


When I checked the database, the column is still not created.


I hope someone can enlighten me with regards to this issue.


Thanks,

Melody

Solution

Hi Everyone,

The issue has been fixed.


The problem was I disregarded the error of my 'Address' table. 

It has a problem with its foreign key. 

Every time there's an error in the compilation, I just republish.

And the application gets publish successfully.


But, this isn't true. I believe the cycle of the entire compilation is interrupted with this error.


So, now every time I will see an error in the compilation -- I should fix (lesson learned)..


I updated my 'Address' table which has the error. Now, I can successfully add attributes to my 'Contact' table.


I hope this will shed some light to someone who's having the same problem as I had.


Happy coding everyone!


Thanks,

Melody



Solution

Thanks for updating the thread with your solution, Melody! Glad to hear you figured it out.


Cheers,

Alexandra

I am experiencing this issue as well however the solution above does not seem to work for me.

Platform Version: 10.0.705.0
Development Environment: 10.0.810.0

I have an aggregate "GetRPUserById" which is very simple and just returns a "RP_User" entity by its ID. This same aggregate exists elsewhere and it randomly throws the following error throughout the application:

Invalid column name 'PASSWORD_EXPIRATION'.
Invalid column name 'FAILED_COUNT'.
Invalid column name 'DATE_CREATED'.
Invalid column name 'LAST_LOGIN'.

These columns do not exist on this entity, however they do exist on a "t_rp_user" entity that is exposed to a core service module via an extension. This entity is NOT however used in the consumer espace where the error is always thrown. Again, this occurs randomly in some parts however this page "User_Edit" it occurs consistently.


Here is the complete stack trace:

Invalid column name 'PASSWORD_EXPIRATION'.
Invalid column name 'FAILED_COUNT'.
Invalid column name 'DATE_CREATED'.
Invalid column name 'LAST_LOGIN'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at OutSystems.HubEdition.DatabaseProvider.SqlServer.ExecutionService.ExecutionService.ExecuteReader(IDbCommand cmd)
   at #Hmb.#Qmb.ExecuteReader(String description, Boolean isApplication, Boolean transformParameters, Boolean skipLog)
   at OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd, GenericRecordList`1 rl, String description, Boolean transformParameters, Boolean skipLog)
   at OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd, GenericRecordList`1 rl, String description)
   at ssgemRetailerCore.Flows.FlowAdmin.ScrnUser_Edit.FuncssPreparation.datasetGetRPUserById(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, Int64 qploId)
   at ssgemRetailerCore.Flows.FlowAdmin.ScrnUser_Edit.Preparation(HeContext heContext)
   at ssgemRetailerCore.Flows.FlowAdmin.ScrnUser_Edit.Page_Load(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Brian -

I have some free time tomorrow, want to jump on a quick screenshare and take a look? Usually it comes from the DB not being updated properly for one reason or another.

J.Ja

Hey Justin!

Yes that would be great thank you. The last thing I tried was a complete re-create of the entity by copying the attributes, updating references, copying data over. This is still resulting in the same error.

I will be on Skype throughout the day if you have a few minutes to take a look.

Thanks!

Just in case anyone else runs into the situation I did here is the solution that worked for me:

  1. Remove columns from the entity that the platform is mistaking for different names (ex: "Password_Expiration" was really "PasswordExpiration" for my table)
  2. Update references to the entity
  3. See that in aggregate(s) where the error occurs, the error is gone and the executed SQL does not include invalid column names
  4. Publish and update references
  5. Re-create any columns needed