Invalid Column Name Error in Script Task

I'm running into some trouble when trying to populate a lookup column from a script task.  I invariably get the error "System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'UsrVMProductCode'."

This is the column in question:

And here is the relevant part of the script task:

var esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "UsrWaterplayVMProductCode");
 
var ColId = esq.AddColumn("Id");
var ColName = esq.AddColumn("Name");
 
var entities = esq.GetEntityCollection(UserConnection);
var productCodes = new Dictionary<string,string>();
foreach (var entity in entities) {
	productCodes[entity.GetColumnValue(ColName.Name).ToString()] = entity.GetColumnValue(ColId.Name).ToString();
}
 
foreach (var pd in resultObj.UpdatedParts) {
	if (pd.PartId != "") {
		var code = "";
		if (pd.VMProductCode != "") {
			code = productCodes[pd.VMProductCode];
		}
		var update = new Update(UserConnection, "Product")
			.Set("Name", Column.Parameter(pd.Description))
			.Set("Price", Column.Parameter(pd.Price))
			.Set("UsrProductionCost", Column.Parameter(pd.Cost))
			.Set("IsArchive", Column.Parameter(pd.Inactive));
		if (code != "") {
			update.Set("UsrVMProductCode", Column.Parameter(code));
		}
		update.Where("Code").IsEqual(Column.Parameter(pd.PartId));
		if (update.Execute() > 0) {
			resultUpdate += "Product " + pd.PartId + " updated.\\r\\n";
		}
	}
}

I have confirmed through debug logging that the code variable contains a GUID.  The script also worked fine before I tried to set UsrVMProductCode, while attempting to set another lookup column in this way (Currency) gave a similar error.  Is there something special I need to do to set a lookup value with the Insert/Update objects?

Like 0

Like

2 comments

The use of Update just creates SQL statements that execute on the database directly (instead of using the object model,, like ESQ does). For a lookup column, the actual column name would have "Id" at the end of it. For example, use "UsrVMProductCodeId" instead of just "UsrVMProductCode".

Ryan

Ryan Farley,

Thanks much, that did the trick.

Show all comments