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
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