<
asp:LinqDataSource
ID="LinqDataSourceSM"
runat="server"
ContextTypeName="SalesIntranetSolution.DataClasses.salesIntranetDataClassesDataContext"
OrderBy="reportTo"
Select="new (reportTo)"
TableName="cubeSalesRepRollups">
</asp:LinqDataSource>
Does somebody know how I can modify this LinqDataSource to select distinct records only?
I'm using asp.net login on a SQL Database.
I have 12 users in my User tabel - one of the user I don't want to have in my select (the user is an admin).
I tried this:
[Code]....
I also tried:
WHERE (dbo.aspnet_Roles.RoleName <> 'Administrators')
Both time it took the user into the table....
If I instead try this
WHERE (dbo.aspnet_Roles.RoleName = 'Administrators')
I get only one row (as expected).
What is wrong with the above sql statement - why can't I get 11 users and not them all (12 users)?
Where can I find documentation on the syntax that is expected for LinqDataSource.Select? It seems to be different from what I would do with Linq inline in C# code. For example, concatenateing something in Linq I would normally just do this in the projection:
new { FullName = (person.FirstName + " " + person.LastName ) }
However, with a LinqDataSource the only way I've found to do this is like this:
new ( String.Concat(person.FirstName, " ", person.LastName) as FullName)
I literally spent hours trying to figure this out, and only succeeded when I found a blog where someone gave this verbatim as an example. So I still don't understand why the syntax is different, the braces versus parens, aliasing, Concat operators. Next time I do this I want a better understanding so I am not playing this guessing game. It's like being handed a compiler, and trying to write a program when you don't know what language the compiler compiles.1. Are there different "flavors" of Linq? Like a VB Linq, and C# Linq?2. If so, is the LinqDataSource using a VB flavor of Linq?3. Really, what is really important to me, where can I find documentation on this particular falvor of Linq that the LinqDataSource expects? The documentation for the LinqDataSource itself gives nothing more than extremely simplistic examples.
there are multiple rows with same SubContact_No and with same value of some columns. for a single column I am firing query like
Select distinct (Financial_Status) from mtblSub_Contract where SubContract_No=@SubContract_No";
it's working fine.... (it's ok with a Single column)
But Now I need multiple columns value so how to query for this.
Select distinct (Financial_Status, colums2, column3) from mtblSub_Contract where SubContract_No=@SubContract_No"; is not working
So how to do that.
I have set up Linq to sql. I now need to fill a formview with a select that does a join across a few tables. Have not been able to find any example or tuts that go beyond the simple select. Any suggestions? Can this even be done?
View 1 RepliesI would like to select a distinct query based on three fields and display the rest of the fields. For example,
I have firstname, lastname, address, city, state and zip, but I only want to use the distinct on these fields first and last name. However when i use the distinct function I get a distinct on all the selected rows. I just want to distinct firstname and lastname and display the other fields. for example,
Mary Smith New York
Mary Smithy New York
Mary Smith Maine
I would like the result to be:
Mary Smith New York
Mary Smithy New York
I don't care about Mary Smith Maine, because i am only using the distinct for "Mary Smith" firstname and last name and showing the city.
I have a table "Table1" which has mani 3 fields ID: int,
Date1: datetime,
Date2: datetime
All fields are filled except Date2, so I want to copy the records in Date1 to Date2I tried this query, but it didn't work
[Code]....
I need to select a field value from a database table. If the value read is '1' then I need a 'Y' to display as output in the select statement. I haven't figured out how to do this yet and queries I tried that did not work.
select IsIata from Table_1 CASE WHEN (IsIata = '1') THEN 'Y'
select IsIata CASE WHEN (IsIata = '1') THEN 'Y' from Table_1
how to write a select distinct column1 from another select ?
i mean SELECT distinct column1 from (SELECT column1,column2,column3 where column1 =1)
I'm trying the pull out a result that shows me how many visitors I have had each day.
My database looks like this.
[code]....
Having some troubles selecting distinct rows.
[Code]....
This is not giving me the wanted results.
Two models:
[code]....
Each ContentVersion is linked to Content through the ContentID.
I want to select all "Content" where UserID = X, then for each selected "Content" I want the latest "ContentVersion".
The return type should be of type <IQueryable>ContentVersion.
Im trying to build a where clause in c# for a LinqDataSource, but I cant get it to work, heres my code
[Code]....
when I set category to 'Concerts' tyhe where clause is
'Category = Concerts'
this generates an error telling me there is no column called 'Concerts'. I assume I somehow have to enclose the search term in quotes, Ive tried single and double quotes, but nothing works.
So I need to take the literal "SELECT....FROM Bay....WHERE Warehouse=1 and ((BAY LIKE '1.0%' or BAY LIKE '2.0%'))I have set up a linqdatasource on my asp page and I want to bind that to a gridview. Here is the start of the HTML:
<asp:LinqDataSource
ID="dsBays"
runat="server"
ContextTypeName="CartonInventory.CartonInventoryDataContext"
EnableUpdate="True"
[code]...
How do I add a parameter so that it includes the LIKE statement?
1.Category:
CatID(Pkey),
CatName,
ParentCatId(Fkey)=> CatID is used as a ParentCatId
2.Comp_Cat:
CCID(Pkey),
CatID,(fkey),
CompID
In my stored Procedure I am passing PageNo,CompID.. My requirement is that One parent CatName is displayed Per page but only one time.
This is something I can easily do in SQL, but I'm pretty new to LINQ and haven't been able to find an answer online.
I need a LINQ query that returns two values; the first a distinct value and the second the count of the first value. This is filtered by a parameter. Here is my table:
[code]....
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?
View 1 Repliesi am an asp.net beginner, i am a windows application programmer and currently i am working on big project " Human Resources" and i need to allow the users to apply for jobs using a web page.in windows application, i can use the bindingsource to bind textboxes and comboboxes to the fields of an object from L2S.and to bind the datagrid also to the detailed data and the page will be for inserting issue only,i.e i don't need to bring the data of applicants and certificates from the database, i just need some lookup data like maritalstatusCombo and availableJobsList things to allow the user to choose from.how can i do this using linqdatasource.
View 3 RepliesI use LinqDataSource and GridView.
I need an old value of field "PictureBig" in RowUpdating handler like this:
e.OldValues["PictureBig"]
To achieve this I use bound unvisible literal in edit item template...
<asp:Literal ID="ltBig" runat="server" Text='<%# Bind("PictureBig") %>' Visible="false" />
Is there any better ways of doing this?
how can i use Distinct in my sql statement like that
SELECT
Distinct * from my_table ?? ? ? ?? ? ? ?
I am trying to program the where prameters for this in code behind in C#, and is there really no way to do more than one parameter? Or am I not understanding this correctly? It seems only the 2nd parameter gets executed, no matter which one is at begining. I debug and seems they both get executed fine, but my listview doesn't get filtered and instead only the 2nd paramter works. Look at my code:
Also, if I do myLinq.Where = "division ==1 and someotherInt = 2", it works fine, but if I do two different data types, it will throw error. Since I want to filter with two different data types, hwo do I do this?
Parameter pram1 = new Parameter();
pram1.Name = "division";
pram1.DbType = DbType.Int32;
[code]...
I'm having a question about the LinqDataSource. Does anyone know if it's possible to perform multiple inserts with the LinqDataSource? Let's say for example that I have 2 tables.
Table A
AID = PK autoincrement Name AddressA FK AddressB FK
Table B
BID = PK autoincrementStreetNumberPostalCodeCountry
These tables are just examples to clarify my question. Let's say that I have a FormView with a LinqDataSource where the user can enter his name en a billing address (A) en delivery address (B). Now I would like to know if the LinqDataSource is able to insert 2 records in table B and use these ID's to insert them in table A together with the name?
I need to retrieve data using a date as an input parameter. I created a WhereParameter and passed in a date value that I know to exist in the database, but no data is returned. Heres my code
[Code]....
Ive passed in '02/06/2010' as the date, in the database, the Startdate column has several values of '2010-06-02 13:08:50.503' so I guess its not being found because of the time part. Whats the best way to make sure that I retrieve the correct data ? Im not concerned about the time at all, I just need all rows for 02/06/2010.
If I have a Linqdatasource that isn't connected to a a bound control on my page how do I use the data source to read data in the code behind. ie
var query = (from x in linqdatasource select x)
What I actually want to do is create a data array (myArray) within the code, read all of the data from the linqdatasource (selecting 1 field to form tableDataArray), then compare the two arrays and then write (insert) those into in myArray that are not already in the datasource to the datasource (ie execute an insert back on the datasource).
Here is the query I have now which returns over 2 million records:
[Code]....
This works, but the result is littered with many rows that contain the same SSN. So how do I return only one row for each ssn? I cant use distinct because all of the other columns are already always different, this would return the same as above.
I have an already created DataTable object which i am using for my girdview (asp.net) i also need to bind a column of that object to a DropDownList. The datatable has the correct details in the column but the column contains more that 1 of the same name in the column - hence I would love to just do some kind of SELECT DISTINCT on the datatable and copy it to a new datatable for use with binding the dropdown.
This would allow me to save resources by making another trip to the database.
Here is an example, the current datatable has a column called items and in this column has the following entries
1
1
1
1
5
5
6
And of course i need only unique items for binding to my dropdown, hence i need the following data
1
5
6
Of course i don't want to change the original datatable object but rather make a copy of it will the new details.