Hi,
I was having some problem using DbFunctionCall with the Replace() function in MS Access, but I kept getting "Undefined function 'Replace' in expression." error. I wasn't sure what I did wrong. Thanks in advance for all of your help~!
LLBLGen version: 2.0.0.0 Final
Database: Access 2003
Query trying to achieve:
SELECT Code, SupplierName
FROM [_Product] INNER JOIN [Supplier] ON [_Product].[Supplier] = [Supplier].[Id]
WHERE REPLACE([_Product].Code, "-", "") LIKE "*somecode*"
LLBLGen code that I used:
DbFunctionCall fnReplace = new DbFunctionCall("Replace", new object[] {ProductFields.Code, "-", string.Empty});
IPredicateExpression mainCriteria = new PredicateExpression();
mainCriteria.Add(
ProductFields.Code.SetExpression(fnReplace) %
string.Format("%{0}%", somecode.Replace("-", "")));
ResultsetFields rs = new ResultsetFields(2);
rs.DefineField(ProductFields.Code, 0);
rs.DefineField(SupplierFields.SupplierName, 1);
IRelationCollection relation = new RelationCollection();
relation.Add(ProductEntity.Relations.SupplierEntityUsingSupplier, JoinHint.Inner);
TypedListDao list = new TypedListDAO();
DataTable result = new DataTable();
list.GetMultiAsDataTable(rs, result, 0, null, mainCriteria, relations, true, null, null, 0, 0);
Code generated in the visualizer. I tried copy-and-paste them into Access with usual modification, (adding quotes, changing Entity name into table name, change % to *, etc) and it work fine, so I'm not sure how come it's still giving me error..=(
Visualizer result:
PredicateExpression mainCriteria:
(
(
REPLACE
(
ProductEntity.Code, @LOcdcab7832, @LOcdcab7bf3
)
LIKE @Code1
)
)
@LOcdcab7832, -
@LOcdcab7bf3,
@Code1, somecode
RelationCollection relation:
(
Suppliers INNER JOIN _Product ON Supplier.Id=_Product.Supplier
)
Thanks again for your help!