ADO.NET :: Linq To Sql Using Distinct On Individual Values?
Jul 29, 2010
I have a database that is filled with documents. In the database there is the documents id, name and version. Two documents can have the same name and id but are seperated by different versions.For instance there can be two entries, id: A44, name: Dilbert was here. But they are version A and B.When retrieving these to a listview I wish to limit the documents shown to only one version. Doing so means that I am only to show one document with the same id. I only get the id and name to show in the listview so I thought that distinct might do the trick. But since the name can change throughout different versions this was no good.Basically I wish to retrieve all documents but with distinct id. How do I achieve this?
View 10 Replies
Similar Messages:
Oct 19, 2010
I have a datatable which has been dynamically generated from FoxPro tables using a UNION Select statement. e.g.
SELECT * FROM x UNION SELECT * FROM y UNION SELECT * FROM Z ORDER By v_alue1
This produces a datatable with about 100 rows, each containing many fields, one of which is c_olor. From this datatable, I would like to select the distinct colors and then output in a dropdown.
I have a public class Color which just has one property which I can then use as the DataTextField and DataValueField for the dropdownlist
[code]...
However this never results in the distinct colors.
I have searched and searched for what I am looking for, and this seems to be one of the methods to produce a distinct set of results, but this and the others do not work.
My reasoning behind getting the colors this way, is that I need to get various other distinct values from the same UNION SELECT datasource, so would just do one DB call, cache the results, and then just used this cached datasource to retrieve all my distinct values.
View 2 Replies
Feb 4, 2011
I have one table(tableName is getDetails and having three column ID, Name, City)
View 6 Replies
Oct 27, 2010
I would like to get the distinct value form DataTabel using LINQ.
For example my sample table,
[code]....
1. How to get the distinct Country from Country you like column and the return records as Array or DataTable? (I want only India,USA,UK,Germany )
Is there any way to filter the record values using LINQ?
View 3 Replies
May 4, 2010
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]....
View 5 Replies
Sep 9, 2010
I have this group-join syntax but I couldn't get the distinct value from the relational tables below.
Table_1
key_1 t1_value
1 Food
2 Sports
3 Leisure
4 Trip
5 Zoo
Table_2
key_2 key_1 t2_boolean
15 1 True
16 1 True
17 1 True
18 2 True
19 2 True
20 2 False
desire result:
[code]...
View 3 Replies
Jan 17, 2011
Here's my current table data.
AccountNumber Product
00505871 Product1
00505871 Product2
00503297 Product3
00900004 Product4
00505871 Product3
00514884 Product3
00503297 Product2
00505871 Product1
How can I achieve following result.
AccountNumber ProductString
00505871 Product1,Product2,Product3,
00503297 Product2,Product3
00900004 Product4
00514884 Product3
View 13 Replies
Sep 21, 2010
I have a datatable dt with 4 columns. I want to retrieve only distinct values from column1 of datatable.
How can I achieve this using c# code?
View 3 Replies
Oct 31, 2010
This is my query
Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus On Person.Id Equals ProspectStatus.Prospect_ID Where (Person.Organization_ID = s_Organization_ID)Select Person.
I only want to return DISTINCT values of Person.ID. Right now, it returns the same person multiple times because there are several entries in the status table.
Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus
View 1 Replies
Jul 15, 2010
i'm binding data to ddl from database.i'm selecting one column from database table to bind.In that column there may be items with same name.but when selecting in ddl there must be no same values.
View 4 Replies
Mar 30, 2011
I have a table 'tbl' and a column 'col'. The elements are like 1,1,2,2,2,3,4,4. Now I want to show two columns one is the one will contain the distinct values from 'col' like 1,2,3,4.... and second will contain the count for each of them like for above case it will be 2,3,1,2.
View 1 Replies
Dec 17, 2010
I have distinct value combinations of two values.i cannot achive in sql why beacause ,here crystal report having grouping options and output will going to print in a foote..
Sample format crystale report
SNo Name TranID
1 Guru 18
2 Inbaa 19
3 Guru 20
4 Inbaa 19
------------------------------------
Total Name : 3 Tran ID :3
------------------------------------
Name inbaa with Tran ID 19 is duplicate ..
View 2 Replies
May 3, 2010
I am using a simple Login function and need to record individual field values from MySQL database to compare with inputs.
My connection code:
Dim myConnection As MySqlConnection
Dim myDataAdapter As MySqlDataAdapter
Dim strSQL As String
myConnection = New MySqlConnection("Database=mydatabase;Data Source=xx.xxx.xx.x;port=3362;UId=myID;Password=1234;")
strSQL = "SELECT username,password FROM Login WHERE ID = '" & 1 & "'"
myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
myDataSet = New Dataset()
myDataAdapter.Fill(myDataSet,"Login")
I have confirmed it is connecting by adding the following GridView.
GridView1.DataSource = myDataSet
GridView1.DataBind()
My problem is isolating the individual field values.
View 2 Replies
Oct 6, 2010
I'm trying to bind a dataset (where the data is coming from a webservice) to a dropdownlist; the problem is that I'm getting duplicates in the list... how can I display distinct values only:
[Code]....
View 5 Replies
Sep 30, 2010
I have the following table structure:
[code]....
The following query returns the default recordset:
[code]....
View 4 Replies
Nov 19, 2010
As I kept my fetched datatables into session and accessing it on another page there I am
wroking on datatable to fetch only distinct values of particular column to assign to dropdown list.
View 1 Replies
Mar 18, 2010
To start with - I'm using VS 2008/.NET 3.5 and working in a company-provided template that leaves me restricted to some/many settings that go beyond the body of the page itself. That said, I need to create a page that will display data from individual cells in a database table that, when lined up would look like a normal sentence. Based on certain criteria I would have certain sections of the sentences hide (ex. if it contains a key phrase or if it's empty).
I've seen a method in which Labels could be used to achieve this effect, where I would assign it the grid coordinates of the cell in a table, but I could never get it to work. I've been successfully connected to the table (LINQDataSource) but still have a blank canvas without this fundamental display technique as that will be all that's in it.
View 2 Replies
Apr 20, 2010
I 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.
View 15 Replies
Mar 24, 2011
I'm trying to build a linq2Entity/gridview function for a forum, that draws data from multible tables.The result should be: Get the latest 10 threads that user(xx) has replyed in, that is not disabled or has a subject that is not allowed.Include information on the last post, thread owner user, and last post user, in each thread.
ere is my code... is returns the correct threads and some that should not be there.
[Code]....
View 1 Replies
Jan 8, 2011
Prior to using a ViewModel, I could easily pass the "soon to be edited" object directly to the view without needing to fuss over setting individual properties etc as the View conveniently accepted the Employee type directly..
[HttpGet]
public ActionResult EditEmployee(int? id)
{
EmployeeRepository ER = new EmployeeRepository();
Employee SomeEmployee = ER.GetEmployee(id.Value);
if(SomeEmployee!=null)
return View(SomeEmployee);
But now I'm using a ViewModel with DataAnnotations attributes applied over the top of various properties for validation purposes. Which creates a problem.. After fetching the "soon to be edited" object from the db, setting the ViewModel's values is suddenly a whole lot more complicated. I can't simply pass the retrieved object straight to the view, as the View now expects the VMEmployee type instead. I would like to be able to do something like:
[HttpGet]
public ActionResult EditEmployee(int? id)
{
EmployeeRepository ER = new EmployeeRepository();
Employee SomeEmployee = ER.GetEmployee(id.Value);
if(SomeEmployee!=null)
return View(new VMEmployee(SomeEmployee));
All paths seem to lead to a huge constructor which manually sets the values of each individual property. But I never had to do that before when I wasn't using a ViewModel. Model binding was a blessing! My objects also have complex child objects, which my form is also collecting values for, so this would be a huge/verbose task against DRY principals. I don't even really want to use a ViewModel, but am forced to because I need two different DataAnnotations rule sets for different validation scenarios applied to the same object.
All I want to do is be able to have two different DataAnnotations rule sets for different scenarios. I.e. public-facing www site vs internal-facing admin site. DataAnnotations doesn't seem to be flexible enough to easily cater for this common need. I've tried AutoMapper, but it throws an error saying it can't map my object types, I suspect because Employee was auto-generated by LINQ to SQL. What is the most elegant way to achieve this while sticking to DRY principals?
View 1 Replies
Dec 17, 2010
I have the following class:
public class MySeat
{
public string Section { get; set; }
public string Row { get; set; }
public string Seat {get; set; }
}
I want to group a list of seats by the Section and Row so that I can make a comma seperated list of the seats.
var groupedSeats = seats.GroupBy(x => new { x.Section, x.Row });
foreach (var group in groupedSeats)
{
htmlWriter.Write(group.Key.Section);
List<string> seatNumbers = new List<string>();
foreach (var seat in group)
seatNumbers.Add(seat.Seat);
htmlWriter.Write(string.Join(@", ", seatNumbers.ToArray()));
}
Is there a better way to do this with LINQ? This is just a small portion of the code I'm trying to clean up, I'm curious if there is a better way to grab the seat numbers for display rather than creating a list, looping through the groups and then doing a string.Join on it.
View 2 Replies
Jan 30, 2010
I have a requirement to convert LINQ to DataTable.
I stole the following Extension Method from StackOverflow:
[code]....
View 2 Replies
Jan 21, 2011
I have a table in the database for users with the columns FirstName, LastName, DateUpdated, UserUpdated. Now, I display the fields FirstName and LastName but the last to fields are just for internal record keeping. I don't want the user to see or be able to edit them. However, I do want the DateUpdated and UserUpdated field to be populated upon every insert and update. The user id is in the session and I just take the current date for the DateUpdated field.
My question is: How can I write the UserUpdated and DateUpdated fields without the user providing any values?I had a couple of ideas:
- Modify the DataContext class to automatically compute the values. Don't know if that's a good idea. Also, my changes will be overwritten when I re-generate the DataContext class.
- Create hidden fields that hold these vlaues. For instance: <asp:HiddenField ID="user_createdHiddenField" Value='<%# Eval("UserUpdated") %>' runat="server" />. But how do I actually assign a value to the field?
View 2 Replies
Aug 31, 2010
I am running a LINQ query which populates a list used by a DropDownList, I need to insert an "Unassigned" value to the list. The function below queries the values correctly from the db but does not insert the "Unaasinged" value to the list.
[Code]....
View 2 Replies
Apr 2, 2011
I have a small issue binding the values into a dropdown using LINQ in ASP.NET code-behind.
var clientquer = from i in Entity.New_Bank select i;
//var q = (from s in names
// select s).Distinct();
// var getlendername = (from db in mortgageentity.New_Lender group db by db.Bank_Name into t select t.Key).ToList();
if (clientquer.Count() > 0)
{
ddlbankname.DataSource = clientquer.ToList();
ddlbankname.DataValueField = "Bank_ID2";
ddlbankname.DataTextField = "Bank_Name";
ddlbankname.DataBind();
}
It is binding with duplicate values, but I don't want bind duplicate values. I'm trying to solve this by using a group by clause, but it is not working.
View 2 Replies
