udg Expert Licensed User Longtime User Mar 29, 2016 #1 Hi all, I'd like to dynamically generate my WHERE statement to use in a query with DBUtils.ExecuteTableView. Problem is I can't figure out how to properly write the LIKE clause. Here it is my failing code: Code: Copy Public Sub getWhereClause As String Dim sb As StringBuilder sb.Initialize sb.Append(" where t1.datamv >=? AND t1.datamv <=? ") If fltDMov <> "" Then sb.Append(" AND t1.descmv like '%?%' ") Return sb.ToString End Sub What's get wrong is the third parameter ("?") which is evidently read as a question mark char, causing an ArrayIndexOutOfBound error since I pass in 3 parameters but the Where statement appears as having just 2 of them. I know I could substitute that third ? with proper data (the same that goes in the arguments array, in my case fltDMov), but I'd like to kow which syntax (if any) to use in order to keep things as they are. Thank you.
Hi all, I'd like to dynamically generate my WHERE statement to use in a query with DBUtils.ExecuteTableView. Problem is I can't figure out how to properly write the LIKE clause. Here it is my failing code: Code: Copy Public Sub getWhereClause As String Dim sb As StringBuilder sb.Initialize sb.Append(" where t1.datamv >=? AND t1.datamv <=? ") If fltDMov <> "" Then sb.Append(" AND t1.descmv like '%?%' ") Return sb.ToString End Sub What's get wrong is the third parameter ("?") which is evidently read as a question mark char, causing an ArrayIndexOutOfBound error since I pass in 3 parameters but the Where statement appears as having just 2 of them. I know I could substitute that third ? with proper data (the same that goes in the arguments array, in my case fltDMov), but I'd like to kow which syntax (if any) to use in order to keep things as they are. Thank you.
udg Expert Licensed User Longtime User Mar 29, 2016 #2 Well, I had my dinner and a couple of neurons setted their synapsis! ehehe My solution: Code: Copy If fltDMov <> "" Then sb.Append(" AND t1.descmv like ? ") .... 'while the StringArgs array got its value as myfilter(i) ="%" & fltDMov & "%" So the "trick" was just to move the % wildcard to the argument instead of having it in the LIKE clause! udg Upvote 0
Well, I had my dinner and a couple of neurons setted their synapsis! ehehe My solution: Code: Copy If fltDMov <> "" Then sb.Append(" AND t1.descmv like ? ") .... 'while the StringArgs array got its value as myfilter(i) ="%" & fltDMov & "%" So the "trick" was just to move the % wildcard to the argument instead of having it in the LIKE clause! udg
JakeBullet70 Well-Known Member Licensed User Longtime User Mar 30, 2016 #3 I still don't get why the 1st one did not work... Upvote 0
udg Expert Licensed User Longtime User Mar 30, 2016 #4 I still don't get why the 1st one did not work... Click to expand... I think that's because the sequence %?% (single-quoted or not) fools the parser in considering the question mark as one of the chars in the sequence and not as the placeholder for a parameter. In a sequence like >=? evidently the parser has knowledge of operators so it can evaluate the ? for what it's intended to be, a parameter. At least, this is how I understand it, but I could be wrong. udg Upvote 0
I still don't get why the 1st one did not work... Click to expand... I think that's because the sequence %?% (single-quoted or not) fools the parser in considering the question mark as one of the chars in the sequence and not as the placeholder for a parameter. In a sequence like >=? evidently the parser has knowledge of operators so it can evaluate the ? for what it's intended to be, a parameter. At least, this is how I understand it, but I could be wrong. udg
