Sql Server - Linq - Limit List To 1 Row Per Unique Values Based On Value (minimum) Of Single Field
May 3, 2012
I have a stored procedure (I cannot edit) that I am calling via linq.
The stored procedure returns values (more complex but important data below):
Customer Stock Item Date Price Priority Qty
--------------------------------------------------------
CUST1 TAP 01-04-2012 £30 30 1 - 30
CUST1 TAP 05-04-2012 £33 30 1 - 30
CUST1 TAP 01-04-2012 £29 20 31 - 99
CUST1 TAP 01-04-2012 £28 10 1 - 30
I am trying to limit this list to rows which have unique Dates and unique quantities in LINQ. I want to remove items with the HIGHER priority leaving rows with unique dates and qty's.
I have tried several group by's using Max and order by's but have not been able to get a result.
Is there any way to do this via linq?
EDIT:
Managed to convert brad-rem's answer into VB.net.
Syntax below if anyone needs it:
returnlist = (From p In returnlist
Order By p.Qty Ascending, p.Priority
Group By AllGrp = p.Date, p.Qty Into g = Group
Select g.First).ToList
View 1 Replies
ADVERTISEMENT
Jul 3, 2011
I am having so trouble with the ToUpper() procedure with strings. I am using LINQ to find unique values in a list of objects. In that whole process I set the values to compare to all lowercase in order to get a proper list. My goal is to populate a dropdown list with the values. I'm attempting to set the first letter of of the string to uppercase and keep the rest lowercase however the toUpper() procedure is not working?
Private Function FixCase(ByVal strIn) As String
Dim strOutput As String
Dim intStringLength As Integer = strIn.Length - 1
strOutput = strIn.Substring(0, 1)
[code].....
View 5 Replies
Aug 6, 2010
I'm trying to check whether values ar between a maximum and a minimum limit. I do that 21 times, and it works 20 times. In case of the 21. time VB tells me that 15 is less than 8. Here the relevant lines of
[Code]...
View 5 Replies
Jun 21, 2011
I'm returning a list of database records;
Dim rsPublicChilds As System.Data.Linq.ISingleResult(Of spGetPublicObjectsResult) = Nothing
rsPublicChilds = dc.spGetPublicObject(slintLoginID, lintLanguageID, lintObjectID, lintObjectTypeID, lstrSEOURL, lstrValid)
I get an enumerable list of rsPublicChildObjects that I then convert to an array;
Dim larr_PublicChild As IEnumerable(Of spGetPublicObjectsResult) = rsPublicChilds.toArray()
That then gives me easy access to an array of the objects, so I can then do;
larr_publicchild(0).colMyValue
etc.etc
I'd like to get the minimum value of colMyValue (or any other property of the object that's been created for me) but I can't quite see how to get there.
View 1 Replies
Mar 3, 2010
I have been working on a web based report from an existing client/server apps mssql database. I have a working query, that pulls this information together from many tables, and can provide the query and a result set if necessary.
The results currently are similar to this.
ID | Name1 | Date1 | Veh | PO | Stops
_________________________________________
1 | Bob 1 | 12/1 | Car | 1234 | 4
2 | Sam | 12/3 | Car2 | 2245 | 3
2 | Joe | 12/4 | Van1 | 5568 | 2
[Code]....
View 2 Replies
Jun 15, 2011
Possible Duplicate: Retrieve identity value from inserted record's primary key.I have a field with a unique field (that I don't have to update automatically) called ID. I'm using this code to add a field to it:
DB.Snippets.AddSnippetsRow(10, 0, Leaves, Coins, Date.Now, Language, Username, Description)
What I need to be able to do is get the unique field it adds. It +1's to ID every time I add a field there, but how do I get that without hacky database looping?
View 2 Replies
Mar 27, 2012
I have the following Data Transfer Objects defined:
Public Class MemberWithAddressesDTO
Public Property Member_PK As Integer
Public Property Firstname As String
Public Property DefaultAddress As AddressDTO
Public Property Addresses As IQueryable(Of AddressDTO)
End Class
[Code]...
View 1 Replies
Jul 9, 2009
I have this working for a single field in a text but im trying to add more than one fields data .[code]...
View 7 Replies
Sep 15, 2010
I have a column in a table that contains a message and I want this message to be displayed in a label. Here is my code which currently doesn't populate the label.
Protected conString As String = ConfigurationManager.AppSettings("sqldirectory")
Dim cnn As New SqlConnection(conString)
Dim cmd As New SqlCommand("select message from [database].[dbo].[table]")
[Code]....
View 1 Replies
Feb 24, 2009
I have a List (Of Type) which contains many instances of a class with three properties, IPAddress, AlertDate and AlertTime.There can be many duplicates of IPAddress in this list :-
[code]...
I need to be able to pull out just the latest (by date and time) entry in the list for each unique IPAddress. Is there any simple way of doing this?
View 3 Replies
Aug 11, 2009
I have a query I'm using to identify a group of contracts that have expired. The query has a ContractID, Status, Start Date and End Date. For each contract, I need to go to a directory and search for a file to import. That part works just fine, but how can I put a qualifier in to check for contracts that have the same start and end date? Do I need to create a multi-dimensional array for this or can I use the query I have? The part in bold text is where I need to identify identical start/end dates. Comments identify pieces to be developed and message boxes to help me confirm what's happening as I run the code:
Private Sub cmdRefreshList_Click()
DoCmd.SetWarnings False
'Create an array based on the true-up contract IDs.
Dim db As Database, TUrst, PYMTrst As Recordset
Dim TUContracts() As Variant
Dim TUContractID As Variant
[code]....
View 2 Replies
Jul 6, 2010
This is my code which i used to try this but it inserts only the last value in the listbox but i need all the selected values seperated by comma [code]...
View 2 Replies
Aug 11, 2009
I have a query in Access 07 that identifies the following fields: ContractID, status, start date and end date. For each contract, I've been able to search a network share and upload its corresponding file; however I need to skip the import and write a record to a table for contracts with the same start and end date. I've included a placeholder (in bold below), but how do I make the date comparison work? Do I need to create a multi-dimensional array for that or can it be done with the code I have?
Private Sub cmdRefreshList_Click()
DoCmd.SetWarnings False
'Create an array based on the true-up contract IDs.
[code]....
View 2 Replies
Mar 8, 2010
Given the following XML, I would like to return all eventtitles where the eventtype id = 23. My current query only looks at the first eventtype, so returns the wrong result.
<event>
<eventtitle>Garrison Keillor</eventtitle>
<eventtypes>[code].....
View 1 Replies
Nov 30, 2011
I am trying the following to select a long date, but becase the value is stored in SQL database as DateTime I am unable to return any results unless minutes and seconds match perfectly...I then tried to use 2 calendars and have the same issue with the DateTime format..
CODE:
View 1 Replies
Oct 18, 2011
This is a 2 part question about list comparisons. I see lots of threads on comparing two separate lists, but I need to know how to compare values in a single list.
Part 1:I have a bunch of string values that I am first splitting and then adding to two separate lists. The strings look something like this... 3-ABC, 6-DEC, 11-RFG, 6-ABC, 12-ABC, etc... So, with the code below(vb) I am splitting the numerical portion of the string and putting it into myLeadQtys(saved for later). I then take the rest of the string and put it in another list (myLeadVals):
Dim myLeadVals As New List(Of String)
Dim myLeadQtys As New List(Of String)
Dim strMtextValue As String
[code]....
View 3 Replies
May 4, 2012
I'm using Visual Studio and table adapters using an Access DB as the backend. I need the minimum 4 digit value. I want to get next available 4 or 5 digit number. And this will change depending upon the users request. Currently I use a table adabpter query. But how would I write a query. I suppose I could do a long iterative loop through all values until I see a 4 digit.But I'm trying to think of something more efficient.
Function GetNextAvailableNumber(NumofDigits) as Long
'SQL Code Here ----
'Query Number Table
[code]....
View 1 Replies
Jan 10, 2009
I'm using the datagridview. This is the sample data:
[Code]....
How do I parse through the grid to rename the duplicate values in a particular column (in this example it's the l_name field) into such a format?
[Code]....
View 1 Replies
Feb 14, 2012
Public Structure testStruct
Dim blah as integer
Dim foo as string
[code].....
View 3 Replies
May 8, 2011
Unique constraint - these columns currently doesn't have unique values..?? I have created Unique Constraint with 3 columns. My code works perfectly but once - two users @ same time entered same data and somehow it saved in DB; after that incident. This UniqueConstraint gives me error - These columns currently doesn't have unique values. How can I check if user enters this kind of entry or how can I restrict the Entry?
View 2 Replies
Mar 21, 2012
I have a class (using a class rather than a structure only because I can set a default field value for the fields I declare in the class) and I'm using a web service that returns information (demographic information) in JSON. I am using LINQ to get the values of each "entry" from the returned list and in that, I have to parse through and pull out the actual data that I want.Here's how I have it doing right now (just a start, there are 44 of them total, thus my question which will follow the code):
Dim returnedStats = From ljo As Linq.JObject In returnedObject.SelectToken("results")
'
'
For Each stat As Linq.JObject In returnedStats
[code]....
View 2 Replies
Mar 10, 2012
I have a List(Of Hardware) - the List is called HWModels
Class Hardware has the following Properties:
ModelName
Status
CPUStatus
MemoryStatus
DiskStatus
The List is populated by reading a CSV file, once it's populated, I want to return the distinct records based on the ModelName
I've attempted by doing it as follows:
(From a In HWModels Select a.ModelName).Distinct
But this isn't right because I end up with a list of only the ModelName's and nothing else.
How do I get the Distinct function to return all of the other class members within the list?
View 1 Replies
Jul 8, 2010
I'm working with Windows Forms - VB.NET.Here's what I have:
A ListView with checkboxes set to True
A Button (triggers the update)
A database table with similar fields as the ListView
What I want to happen:when the user clicks the Button, all items on the ListView with checkbox checked will be updated.My progress:I've already collected the ID of the checked items and stored them in an array. I'll be using this to update the database table.I don't know how to put them in the SqlCommand.Parameters Also, I don't know the update command for such scenario (where in/exist (@parameters))
View 1 Replies
Jun 8, 2011
I'm working with Windows Forms - VB.NET.
Here's what I have:
A ListView with checkboxes set to True A Button (triggers the update) A database table with similar fields as the ListView
What I want to happen:
when the user clicks the Button, all items on the ListView with checkbox checked will be updated.
My progress: I've already collected the ID of the checked items and stored them in an array. I'll be using this to update the database table.
The problem: I don't know how to put them in the SqlCommand.Parameters Also, I don't know the update command for such scenario (where in/exist (@parameters))
View 2 Replies
Aug 18, 2011
I am trying to display value of the field ("UserID") for every row exists in datatable to checkboxlist(make the checkboxlist item selected).
I used for loop, but only the field value from last row of RoleUsers table is selected in the checkboxlist.
Here is my code
Private Sub DisplayRoleUser()
Dim conn As SqlConnection
Dim cmd As SqlCommand
[Code].....
View 3 Replies
Oct 1, 2010
I'm having issues with returning a list of strings of the .Value of a Linq query:
Dim details = <Details>
<Vector size="5">
<Item>Syntactic Structures</Item>
<Item>Introduction</Item>
<Item>The Independence of Grammar</Item>
[Code]...
Is correct in that it returns the list of XElements I want (items 1 - 4, base 0), but I really just need a list of strings for the .Value of those XElements. Maybe I'm just dense here, but anything I've tried in the chapterTitles query isn't working (appending with .ToList.ToString, etc.). details.<Vector>.<Item>.Skip(1).Take(4).Value just returns the first XElement's value.
View 1 Replies
Mar 18, 2008
How Do I show the values from the dataset to the text box based on the selection from the combobox list i already populated the combobox my code is. [code]
View 3 Replies
Feb 23, 2011
I have a query that I have to run that requires me to get rows from a table based on the value of a field in another table (which I have gotten earlier). I currently have it done like this (simplified).
[Code]...
View 1 Replies
Jan 6, 2010
I am writing a message system on my server, the xml is something like this
<xml>
<entry>
<sender>[code]....
my problem now i guess is 2 fold, i wish to combine the first 3 queries into a single query and place in a list or a collection or is there a way to do this with a single query that will give me my desired result?
View 1 Replies
Aug 29, 2011
I want to set minValue and maxValue of text boxes to define a range of acceptable values for validation purposes.In VB 6 this is straight forward and can be done under text box properties, however in visual studio vb .net 2010 this is not the case or it may be the case that i am missing something.
View 4 Replies