DataSource Controls :: EntityDataSource Filtering / ESQL Where Clause?
Feb 28, 2010
I have a GridView that's currently bound to an ObjectDataSource. I would like to see if it's possible to instead bind it to an EntityDataSource. However, I need to be able to apply custom filters to it.
One of the filters corresponds to a bit / boolean property in the database / EntityDataModel. It has 3 possible selections:
[Code]....
[code]....
How can I accomplish the same thing in an eSQL Where clause? There is no COALESCE function, is there an equivalent?
I am using EF 4 and EntityDataSource in a Page (WebForms).
This page contains a DropDownList that display UserName. Data from this DropDownList it is from "aspnet_Users". At the moment I am using a single EntityDataSource code belove: <asp:EntityDataSource ID="EntityDataSourceListUsers" runat="server" ConnectionString="name=CmsConnectionStringEntityDataModel" DefaultContainerName="CmsConnectionStringEntityDataModel" EnableFlattening="False" EntitySetName="aspnet_Users" Select="it.[UserId], it.[UserName]"> </asp:EntityDataSource>
I need to FILTER the UserName associated to a specific ROLE and displaying it in the DropDownList therefore using others tables"aspnet_UsersInRoles" and "aspnet_Roles".
How to do it? Would be great have a simple solution since I am a beginner.
I'm using EntityDataSource to retrieve my items, and i want to get the items for a particular region. (items and regions has a many to many relationship, thus item has a regions navigation property). and I'm using "IN" to filter the items. tried several combinations and it kept on throwing various errors. how can i get this sorted out:
I have a standard gridview, with the standard paging and sorting interface enabled.The application holds appointments for three organisational units, with a maximum of 3000 appointment records being returned across all three units. The appointment records are quite small.
The choice I am faced with is to:
1. Have an Org Unit dropdown which returns to the datasource on change of selection and at inital databind, and returns appointment records for one org unit at a time i.e. approx 1000 records.
2. Have the objectdatasource return all 3000 appointment records for all org units at initial databind, and filter the objectdatasource by org unit on change of selection, thereby saving a trip to the datasource.
I guess the question boils down to identifying the point at which querying the datasource by org unit is more efficient than filtering the records returned by org unit.
Is 3000 records a lot for a gridview to be paging and sorting etc.
I'v been using SqlDataSource and ObjectDataSource for a while.I would like to know about LinqDataSource and EntityDataSource. Therefore I have 2 questions to ask.1) Why do we need to use those twos? 2) In which cirmustances (Requirements) developers are using LinqDataSource and EntityDataSource?
I have this code, and I need the "it.MembershipOption.Id" value in the "where" clause to be the <%# Eval("Id") %> from ListViewEventsOuter. I tried to add the Eval in the "where" clause but it throws an error.
What would be the best way to extend the EntityDataSource control? I have a site that implements Entity Framework and I current create my model container and add it to the HTTPCurrent context. When I create EntityDataSource controls on the page I have to go into the code behind and set the OnContextCreating to add MY context to the EntityDataSource instead of the one that EntityDataSource creates. This is because in my model I have extended the Partial Classes to support Validation and custom Save/Delete methods.I would ideally like to create a custom control that I can drop onto a page that has its context already set to the context that I created.
I have a formview with an entityDataSource. I need to include columns from related fields. I have seen many demos, with AdventureWorksDB, that imply that you can do this by using INCLUDE in the entitydatasource declaration. I have tried made many attempts and it just does not seem to work as demonstrated. I have attached the errors below and the EDS. I have also attached a JPEG of the model I am working in. Three errors demonstrating 3 different attempts to include a related table:
DataBinding: 'TmModel.tbICObjectBase' does not contain a property with the name 'Group' the code declaration for error 1: <asp:Label ID="Label1" runat="server" Text='<%# Bind("Group") %>' /> DataBinding: 'TmModel.tbICObjectBase' does not contain a property with the name 'tbICTagBase'. the code declaration for error 2: <asp:Label ID="Label1" runat="server" Text='<%# Bind("tbICTagBase.Group") %>' /> DataBinding: 'System.Data.Objects.DataClasses.EntityCollection`1[[TmModel.tbICTagBase, TmModel, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]' does not contain a property with the name 'Group'. the code declaration for error 3: <asp:Label ID="tbICTagBasesLabel" runat="server" Text='<%# Bind("tbICTagBases.Group") %>' /> EntityDataSource <asp:EntityDataSource ID="entDSObjectBase" runat="server" AutoGenerateWhereClause="True" ConnectionString="name=IntouchDBContainer" Include="tbICTagBases" DefaultContainerName="IntouchDBContainer" EnableFlattening="False" EntitySetName="tbICObjectBases" EnableDelete="True" EnableInsert="True" EnableUpdate="True" EntityTypeFilter="tbICObjectBase"> <WhereParameters> <asp:SessionParameter SessionField="Tag" Name="RecID" Type="Int64" /> </WhereParameters> </asp:EntityDataSource>
I'm playing with an EntityDataSource and specifying a simple order by clause containing one column name, for instance "it.[DNS_NAME]".
When I run the following:
[Code]....
I get an EntitySqlException saying that @sort's type is incorrect. It is expecting a Transient.rowtype that matches one of the columns. What is going on here? Order by parameters don't really have a meaningful type other than a string in my opinion. When I try specifying DbType.String instead of TypeCode.String, the same error is thrown.
The exception:
'@sort' is not a member of type 'Transient.rowtype[(SOFTWARE_DSPL_ID,Edm.Int32(Nullable=True,DefaultValue=)),(COMPUTER_NAME,Edm.String(Nullable=True,DefaultValue=,MaxLength=64,Unicode=True,FixedLength=False)),(DNS_NAME,Edm.String(Nullable=True,DefaultValue=,MaxLength=64,Unicode=True,FixedLength=False)),(DIRECTORATE_NAME,Edm.String(Nullable=True,DefaultValue=,MaxLength=24,Unicode=True,FixedLength=False)),(OS_BROAD_CAT_NAM,Edm.String(Nullable=True,DefaultValue=,MaxLength=25,Unicode=True,FixedLength=False)),(OS,Edm.String(Nullable=True,DefaultValue=,MaxLength=64,Unicode=True,FixedLength=False)),(EQP_TYPE,Edm.String(Nullable=True,DefaultValue=,MaxLength=64,Unicode=True,FixedLength=False)),(EQP_PROP_NO,Edm.String(Nullable=True,DefaultValue=,MaxLength=10,Unicode=True,FixedLength=False))]' in the currently loaded schemas.
How can I dynamically provide the column name to order by?
I am having difficulties using a variable in the "where" clause:
declare @shift_started as datetime set @shift_started = (SELECT * FROM OPENQUERY(ADS_RGWP_SERVER, 'SELECT MAX(shift_started) as shift FROM salestkt AS STK WHERE NOT EXISTS(SELECT * FROM SHFTDATE AS SD WHERE SD.shift_started = STK.shift_started)')) SELECT * FROM OPENQUERY(ADS_RGWP_SERVER, ' sum(case when customer_type = ''Inter-Company'' then qty_shipped_today else 0 end) as intercompany_qty, sum(case when customer_type = ''Charge'' then qty_shipped_today else 0 end) as outside_qty FROM salestkt WHERE shift_started ='" & @shift_started & "' ''')
i am developing an application using asp.net and backend server is SQL SERVER 2005.
in my application i have 12 regions. from that 12 regions user will select any of them.
if user select 1,2,3 then i have to pass this string to IN clause
for that i have storedproc like
create procedure sp_updateDistributorContractforAllRegions ( @unitprice float, @region varchar(50) ) update employee set UnitPrice=@unitprice where regionid in (@region) end GO
here regionid datatype in employee table is INT.
from code behind i am adding the parameter as 1,2,3.when i run the query it gives the error as
Conversion failed when converting the varchar value '1,2,3' to data type int.
A :{ id, selfId, name,} where selfId is a foreign key refered to Table A itself.
I generated the Entity automatically like this:
A :{ string id; A self; string name,} when i update the entity in gridview Edit/Update CommandField
i got the exception:
Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. Source Error:
[Code]....
Stack Trace:
[Code]....
I guess maybe this is caused by:
A is to be updated, and when the EntityDataSource construct the new A, the reference "self" is invalid at that moment. I do not want to modify my DB structure and do not want to lose the self reference, The event EntityDataSource_Updating is not triggered while the exception hit. so i can not access my entity at any time. Maybe the worst idea is deal the whole Update Procedure myself. However, is there any better workaround?
I have an SqlDataSource query that should use an input parameter for multiple integer values, like:
select * from table1 where TableID IN (1,2,3,4,5)
->
select * from table1 where TableID IN (@IDValues)
How do I do that ? Default parameter settings won't work, returning "invalid integer value" . I suppose that parameter should be passed as some kind of array that SQL recognizes as multiple integer values , but what's the correct way of setting this ?
SELECT DISTINCT TblSections.SectionID, TblSections.Description FROM TblSections INNER JOIN UMG.Users ON TblSections.SectionID = UMG.Users.SectionID WHERE (TblSections.Description IS NOT NULL) AND (dbo.Users.LoginName = [@Session(UserName)]) ORDER BY TblSections.Description
I have problem in using Session name in the above BOLD WHERE clause.
I've already spent hours in searching through the forum to be able to do the following:I have a SQL datasource defined with a checkbox in it. Purpose is to update one column (userid) behind the table for only that row when the checkbox is selected.This is the code I'm currently having:
I am using Linq to SQL. Tables have timestamp columns and I am using regular Linq methodology in ASP.NET MVC.I do use detached entities and always try to do updates without first querying the database for the original entity. To update an entity I use context.Attach(entity, true) followed by context.SubmitChanges() and all is fine. The sql statement that gets executed looks like:
Ive created a new LINQ To SQL class and dragged several tables across from my server, in my code I access them like this
[Code]....this is all ok, however, I have several stored procedures that I want to use as well, but when I drag the stored procedures across I get a compilatiopn error
Error 60 'DB_BL.DB_BLDataContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'
how do I use stored procedures from LINQ with a using clause ?
I have some duplication that I need to clean up. I wrote a SQL query that is supposed to return duplicate customers who are located in the same city and zipcode.
I have 2 questions regarding it:
1. Is the query syntax correct to find duplicate records by same city and zipcode, data is being returned but it just returns the same customer a few times?
2. I only need to do the GROUP BY clause for Fullname and City however it gives an error when the other columns are left out. The error is Address, State and Zip not being in the aggregate function or in the group by clause.
Adding all the remaining columns to the GROUP BY clause works.
I know I'm missing something here but I can't figure out what it is. I've got a query joining three tables....accounts, payments, and a table linking the two (there is a M:M relationship).I'm trying to pull a list of all accounts in the account table that have a payment that needs to be resequenced, and also the maximum payment priority if there are any payments that haven't been fully paid. If all payments HAVE been fully paid, I want to return a 0.
It's that last bit of logic that I can't get right. If I include that in the where clause, I get only the accounts that have a payment that hasn't been fully paid. If I take it out, I get all the accounts I get, but I get the highest payment priority whether or not the payment has been fully met.
Here is the query....how do I include the where clause criteria but still include all accounts?
I currently have a common file that's responsible for exporting the users GridView to a CSV file which works fine, mostly. The problem is that it only exports the current gridview display, and being that I have it paged, it can be cumbersome to get all the data to an excel sheet when there can be upwards of 20 pages of data. The GridView has a LINQ datasource as follows
[Code]....
How do I get the results of the preceeding datasource? Or is there a better way to get ahold of that data before it is paged?
