topic Re: Getting error "No column was added. No data was replaced. at row 1 in access or evaluation of 'Glue'" When trying to run Update in Discussions

Getting error "No column was added. No data was replaced. at row 1 in access or evaluation of 'Glue'" When trying to run Update

chappe — Wed, 06 Nov 2024 00:25:23 GMT

I'm at a complete loss, my code is querying a sql database and returning a one line result or a blank table, I am then trying to update the ID column of my jmp datatable with that information. It now won't match on any column between these tables, doesn't matter if I make one that's identical and try to run the update. I am on JMP18.

dt100 << Update(
			With( Data Table( dt200 ) ),
			Match Columns(
				:Read Date = :ReadDate, :Experiment Type = :ExperimentType,
				:Plate Size = :PlateSize, :Plate Type = :PlateType,
				:Dispense Mode = :DispenseMode, :Probook Number = :ProbookNumber,
				:Probook Page Number = :ProbookPageNumber
			),
			Add Columns from Update Table( None ),
			Replace Columns in Main Table( :ExperimentID )
		);

Any suggestions?

Re: Getting error "No column was added. No data was replaced. at row 1 in access or evaluation of 'Glue'" When trying to run Update

Jed_Campbell — Wed, 06 Nov 2024 15:26:47 GMT

Do the columns in both tables have the same Data Type and Modeling Type? If not, this might be the issue.

Re: Getting error "No column was added. No data was replaced. at row 1 in access or evaluation of 'Glue'" When trying to run Update

chappe — Wed, 06 Nov 2024 16:30:31 GMT

Yup, I am actually changing them specifically to match before I run this update and they do match when I check them with the columns manager.

Re: Getting error "No column was added. No data was replaced. at row 1 in access or evaluation of 'Glue'" When trying to run Update

Thierry_S — Wed, 06 Nov 2024 16:41:46 GMT

Hi,

It looks like you are missing the curly brackets ({}) in the Replace Columns in the Main Table statement:

Replace Columns in Main Table({ :ExperimentID })

Also, you may need to list all the columns you want to update in that Replace Statement:

Replace Columns in Main Table( {:ExperimentID, Read Date, Exeperiment Type} )

Just a thought.

Best

Re: Getting error "No column was added. No data was replaced. at row 1 in access or evaluation of 'Glue'" When trying to run Update

chappe — Wed, 06 Nov 2024 16:47:15 GMT

I added the {} to the replace columns, but that didn't solve my problem. I am looking to only update the experiment ID, I want to match the others so it updates to the correct row.

	dt100 << Update(
			With( Data Table( dt200 ) ),
			Match Columns(
				:Read Date = :ReadDate, :Experiment Type = :ExperimentType,
				:Plate Size = :PlateSize, :Plate Type = :PlateType,
				:Dispense Mode = :DispenseMode, :Probook Number = :ProbookNumber,
				:Probook Page Number = :ProbookPageNumber
			),
			Add Columns from Update Table( None ),
			Replace Columns in Main Table( {:ExperimentID} )
		);

Re: Getting error "No column was added. No data was replaced. at row 1 in access or evaluation of 'Glue'" When trying to run Update

Thierry_S — Wed, 06 Nov 2024 16:56:04 GMT

I'm sorry to hear that it did not solve the issue.

Would you be able to share the SQL output and the table to be updated?

If not, have you tried updating the table "manually"? If it works, then the issue might be with the script (nothing obvious from my point of view). There might be subtle differences in the data format between the SQL and destination tables (hidden characters?).

Best,

Re: Getting error "No column was added. No data was replaced. at row 1 in access or evaluation of 'Glue'" When trying to run Update

chappe — Wed, 06 Nov 2024 17:05:26 GMT

Solution found. It was unable to update Experiment ID as my sql return column was "ExperimentID" where the original data was "Experiment ID" (added space) so I'll add code into match those first and then I should be good