Answered by:
Use UPPER function in CommandText in EntityDataSource

Question
-
User-1355132948 posted
Hi
I have a commandtext below within EntityDataSource I wish to use upper case function in the join clause in order to show all of my data,
CommandText="SELECT SES.SESSION_ID AS SESSION_ID, SES.FORM_ID, SES.AUDITOR, SES.AUDIT_DATE, SES.SERVICE_ID, SES.UNIT_ID, USR.USER_ID, USR.NAME AS AUDITOR_NAME, USR.NAME_UPPER AS AUDITOR_NAME_UPPER, FRM.FORM_ID, FRM.TITLE AS FORM_TITLE, FRM.TYPE AS FORM_TYPE, SRV.SERVICE_ID, SRV.TITLE AS SERVICE_TITLE, SRV.TITLE_UPPER AS SERVICE_TITLE_UPPER, UNT.UNIT_ID, UNT.TITLE AS UNIT_TITLE, UNT.TITLE_UPPER AS UNIT_TITLE_UPPER FROM AUDIT_SESSIONS AS SES JOIN USERS AS USR ON USR.USER_ID = SES.AUDITOR JOIN FORMS AS FRM ON FRM.FORM_ID = SES.FORM_ID LEFT OUTER JOIN SERVICES AS SRV ON SRV.SERVICE_ID = SES.SERVICE_ID LEFT OUTER JOIN UNITS AS UNT ON UNT.UNIT_ID = SES.UNIT_ID"
The problem is in USERS join condition need to be both upper or lower.
I have tested the query in my oracle database (Toad) and UPPER() function is accepted, however in Visual Studio it doesn't accept it (nor AS clause).
What is the CommandText Property ? is it normal SQL ? what can I use to fix my join query ?
Thank You
Wednesday, May 30, 2018 9:42 AM
Answers
-
User-330142929 posted
Hi Banan,
I have tested the query in my oracle database (Toad) and UPPER() function is accepted, however in Visual Studio it doesn't accept it (nor AS clause).
As far as I know, there is a difference between T-SQL and Entity-SQL. When we use Entity-SQL we usually use ToUpper, a ToLower string canonical functions.
I have made a demo. wish it could be useful to you.
Aspx.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataSourceID="EntityDataSource1"> <Columns> <asp:BoundField DataField="FirstName" HeaderText="FIRSTNAME" ReadOnly="True" SortExpression="FIRSTNAME" /> <asp:BoundField DataField="LastName" HeaderText="LASTNAME" SortExpression="LASTNAME" ReadOnly="True" /> <asp:BoundField DataField="OrderNo" HeaderText="OrderNo" ReadOnly="True" SortExpression="OrderNo" /> </Columns> </asp:GridView> <asp:EntityDataSource ID="EntityDataSource1" runat="server" CommandText="SELECT ToUpper(P.FIRSTNAME) as FirstName, ToLower(P.LASTNAME) as LastName ,O.ORDERNO FROM PERSONS AS P INNER JOIN ORDERS AS O ON P.ID_P=O.ID_P" ConnectionString="name=DataStoreEntities2" DefaultContainerName="DataStoreEntities2" EnableFlattening="False" Select=""></asp:EntityDataSource>
How it works.
The CommandText property of the EntityDataSource control enables you to specify a query that uses a custom Entity SQL expression. Like the SELECT statement, the Entity SQL expression in the CommandText property creates a projection of the original data that is not updatable.
In the other words, it is an Entity SQL command that defines the query not T-SQL statement.
Here are some official link about T-SQL.
Please feel free to let me know, if you have any question.
Best Regards,
Abraham
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, June 20, 2018 3:20 AM
All replies
-
User-492460945 posted
Hi banan,
The problem is in USERS join condition need to be both upper or lower.You want something like this?
CommandText="SELECT SES.SESSION_ID AS SESSION_ID, SES.FORM_ID, SES.AUDITOR, SES.AUDIT_DATE, SES.SERVICE_ID, SES.UNIT_ID, USR.USER_ID, USR.NAME AS AUDITOR_NAME, USR.NAME_UPPER AS AUDITOR_NAME_UPPER, FRM.FORM_ID, FRM.TITLE AS FORM_TITLE, FRM.TYPE AS FORM_TYPE, SRV.SERVICE_ID, SRV.TITLE AS SERVICE_TITLE, SRV.TITLE_UPPER AS SERVICE_TITLE_UPPER, UNT.UNIT_ID, UNT.TITLE AS UNIT_TITLE, UNT.TITLE_UPPER AS UNIT_TITLE_UPPER FROM AUDIT_SESSIONS AS SES JOIN USERS AS USR ON UPPER(USR.USER_ID) = UPPER(SES.AUDITOR) JOIN FORMS AS FRM ON FRM.FORM_ID = SES.FORM_ID LEFT OUTER JOIN SERVICES AS SRV ON SRV.SERVICE_ID = SES.SERVICE_ID LEFT OUTER JOIN UNITS AS UNT ON UNT.UNIT_ID = SES.UNIT_ID"
Thanks,
RajeshV.
Thursday, May 31, 2018 1:38 PM -
User-1355132948 posted
Yes similar to this, I actually tried it but my system showed an error, what would be the proper way to format the command text.
Sunday, June 3, 2018 12:31 PM -
User-492460945 posted
Hi banan,
What is the error you received? Can you post it?
Wednesday, June 6, 2018 9:32 AM -
User-1355132948 posted
'UPPER' cannot be resolved into a valid type or function. Near simple identifier, line 9, column 71.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.Data.EntitySqlException: 'UPPER' cannot be resolved into a valid type or function. Near simple identifier, line 9, column 71.
Source Error:
Line 235: etsAuditSessions.AutoGenerateWhereClause = false;
Line 236: etsAuditSessions.Where = whereClause;
Line 237: gvAuditSessions.DataBind();
Line 238: }
Line 239: }Source File: d:\In-House Applications\QMIC_Dev\AuditForms\AuditFormList.aspx.cs Line: 237
Stack Trace:
[EntitySqlException: 'UPPER' cannot be resolved into a valid type or function. Near simple identifier, line 9, column 71.]
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertMethodExpr(MethodExpr methodExpr, Boolean includeInlineFunctions, SemanticResolver sr) +2710590
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertMethodExpr(Node expr, SemanticResolver sr) +47
System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionsWithUntypedNulls(Node leftAst, Node rightAst, ErrorContext errCtx, Func`1 formatMessage, SemanticResolver sr) +26
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertEqualCompArgs(BuiltInExpr astBuiltInExpr, SemanticResolver sr) +161
System.Data.Common.EntitySql.<>c.<CreateBuiltInExprConverter>b__97_10(BuiltInExpr bltInExpr, SemanticResolver sr) +16
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertBuiltIn(Node astExpr, SemanticResolver sr) +110
System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpression(Node astExpr, SemanticResolver sr) +13
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessJoinClauseItem(JoinClauseItem joinClause, SemanticResolver sr, List`1& scopeEntries) +585
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +104
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessJoinClauseItem(JoinClauseItem joinClause, SemanticResolver sr, List`1& scopeEntries) +73
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +104
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessJoinClauseItem(JoinClauseItem joinClause, SemanticResolver sr, List`1& scopeEntries) +73
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +104
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessJoinClauseItem(JoinClauseItem joinClause, SemanticResolver sr, List`1& scopeEntries) +73
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +104
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClause(FromClause fromClause, SemanticResolver sr) +145
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQueryExpr(Node expr, SemanticResolver sr) +150
System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertParenExpr(Node astExpr, SemanticResolver sr) +58
System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpression(Node astExpr, SemanticResolver sr) +13
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessAliasedFromClauseItem(AliasedExpr aliasedExpr, SemanticResolver sr, List`1& scopeEntries) +26
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr, List`1& scopeEntries) +61
System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClause(FromClause fromClause, SemanticResolver sr) +145
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQueryExpr(Node expr, SemanticResolver sr) +150
System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Node astExpr, SemanticResolver sr) +72
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertValueExpressionAllowUntypedNulls(Node astExpr, SemanticResolver sr) +20
System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQueryStatementToDbExpression(Statement astStatement, SemanticResolver sr, List`1& functionDefs) +71
System.Data.Common.EntitySql.SemanticAnalyzer.AnalyzeQueryCommand(Node astExpr) +51
System.Data.Common.EntitySql.<>c.<AnalyzeQueryExpressionSemantics>b__5_0(SemanticAnalyzer analyzer, Node astExpr) +17
System.Data.Common.EntitySql.CqlQuery.AnalyzeSemanticsCommon(Node astExpr, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables, Func`3 analysisFunction) +162
System.Data.Common.EntitySql.CqlQuery.AnalyzeQueryExpressionSemantics(Node astQueryCommand, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables) +180
System.Data.Common.EntitySql.<>c__DisplayClass1_0.<CompileQueryCommandLambda>b__0(Node astCommand, ParserOptions validatedParserOptions) +26
System.Data.Common.EntitySql.CqlQuery.CompileCommon(String commandText, Perspective perspective, ParserOptions parserOptions, Func`3 compilationFunction) +93
System.Data.Common.EntitySql.CqlQuery.CompileQueryCommandLambda(String queryCommandText, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables) +100
System.Data.Objects.EntitySqlQueryState.Parse() +463
System.Data.Objects.EntitySqlQueryState.GetResultType() +9
System.Data.Objects.ObjectQuery.GetResultType() +58
System.Web.UI.WebControls.EntityDataSourceQueryBuilder`1.BuildBasicQuery(ObjectContext context, Boolean computeCount) +107
System.Web.UI.WebControls.EntityDataSourceView.ExecuteSelectTyped(DataSourceSelectArguments arguments, Creator qbConstructor) +353
System.Web.UI.WebControls.EntityDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +605
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +22
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
System.Web.UI.WebControls.GridView.DataBind() +9
QMIC.AuditForms.AuditFormList.Filter() in d:\In-House Applications\QMIC_Dev\AuditForms\AuditFormList.aspx.cs:237
QMIC.AuditForms.AuditFormList.Page_Load(Object sender, EventArgs e) in d:\In-House Applications\QMIC_Dev\AuditForms\AuditFormList.aspx.cs:132
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
System.Web.UI.Control.OnLoad(EventArgs e) +95
System.Web.UI.Control.LoadRecursive() +59
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2952
Thursday, June 7, 2018 7:28 AM -
User-330142929 posted
Hi Banan,
I have tested the query in my oracle database (Toad) and UPPER() function is accepted, however in Visual Studio it doesn't accept it (nor AS clause).
As far as I know, there is a difference between T-SQL and Entity-SQL. When we use Entity-SQL we usually use ToUpper, a ToLower string canonical functions.
I have made a demo. wish it could be useful to you.
Aspx.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataSourceID="EntityDataSource1"> <Columns> <asp:BoundField DataField="FirstName" HeaderText="FIRSTNAME" ReadOnly="True" SortExpression="FIRSTNAME" /> <asp:BoundField DataField="LastName" HeaderText="LASTNAME" SortExpression="LASTNAME" ReadOnly="True" /> <asp:BoundField DataField="OrderNo" HeaderText="OrderNo" ReadOnly="True" SortExpression="OrderNo" /> </Columns> </asp:GridView> <asp:EntityDataSource ID="EntityDataSource1" runat="server" CommandText="SELECT ToUpper(P.FIRSTNAME) as FirstName, ToLower(P.LASTNAME) as LastName ,O.ORDERNO FROM PERSONS AS P INNER JOIN ORDERS AS O ON P.ID_P=O.ID_P" ConnectionString="name=DataStoreEntities2" DefaultContainerName="DataStoreEntities2" EnableFlattening="False" Select=""></asp:EntityDataSource>
How it works.
The CommandText property of the EntityDataSource control enables you to specify a query that uses a custom Entity SQL expression. Like the SELECT statement, the Entity SQL expression in the CommandText property creates a projection of the original data that is not updatable.
In the other words, it is an Entity SQL command that defines the query not T-SQL statement.
Here are some official link about T-SQL.
Please feel free to let me know, if you have any question.
Best Regards,
Abraham
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, June 20, 2018 3:20 AM -
User-1355132948 posted
Many thanks very good explanation
Tuesday, June 26, 2018 12:06 PM