Get A SUM Of The Items In My Grouped Query?

Dec 16, 2011

I have a list of postcodes in a query (ThisInstructorsPostcodes) and another query that pulls back sales referrals (LastWeeksReferrals)I am using the below syntax to perform the linq equivalent of LEFT OUTER JOIN so I always get a postcode back, even if there were no referrals for it.

dim final = from tip in ThisInstructorsPostcodes _
group join lwr in LastWeeksReferrals on tip.PostcodeID equals lwr.PostcodeID Into lwrgrp = group _
from lwrgrpq in lwrgrp.DefaultIfEmpty _[code].....

The results I am getting are so close to what I need, but I am getting a bunch of InstructorReferrals objects in the lwrgrpq column and what I want to do is simple sum of the all the 'Referrals' integers in those InstructorReferrals objects.I thought this would work:

dim final = from tip in ThisInstructorsPostcodes _
group join lwr in LastWeeksReferrals on tip.PostcodeID equals lwr.PostcodeID Into lwrgrp = group _
from lwrgrpq in lwrgrp.DefaultIfEmpty _
select new with {tip.AreaDistrict, lwrgrpq.Sum(function(x) x.Referrals) }

...but it doesn't - fails with the error: Anonymous type member name can be inferred only from a simple or qualified name with no arguments.I have only used this linq query structure in order to mimic the outer join behaviour of sql, does anyone know how I can fix this so that my grouped items are not anonymous types?

View 1 Replies


ADVERTISEMENT

Variable Not Found When Looping Through Grouped Query?

Mar 29, 2010

I'm trying to do the following LINQ grouping, which works in the debugger (the results are populated in the GroupedOrders object. But VS 2008 gives me the following error at design time...

Name 'x' is not declared
Dim GroupedOrders = (From m In thisConsultant.orders _
Group m By Key = m.commCode Into Group _

[code]....

View 3 Replies

Create A Report That Prints Grouped Items?

Nov 13, 2010

I have 2 tables Markers and Clients. Within the Marketers table i have Team Leaders as well. I want to print a report that will display a Team Leader and the Marketer with all the Clients for that particular Marketer, and then do that for all the Marketers under that Team Leader.

[Code]...

View 2 Replies

Page Through Grouped Data In DataGridView?

Sep 23, 2009

I have a simple VB.NET 2008 app that users to edit fields in the database. Simple enough as a first "real" project in .NET, right?For one table, I am currently using a DataGridView so it can be edited straight up. However, instead of offering the user the entire table, I'd like to group the data by the 'CompanyNumber' column and use a navigator to page through. In other words, I'd like the DataGridView to show me all the lines related to one company, then click the "next" arrow to show the next company, etc.

View 2 Replies

Sql Server :: LINQ To SQL Get Grouped MIN With JOIN?

Apr 19, 2010

I'm having trouble with a LINQ to SQL query getting the min value using Visual Basic. Here's the SQL:

SELECT RC.AssetID, MIN(RC.RecCode) AS RecCode, JA.EngineerNote from JobAssetRecCode RC
JOIN JobAssets JA ON JA.AssetID = RC.AssetID AND JA.JobID = RC.JobID
WHERE RC.InspState = 2 AND RC.RecCode > 0

[code]....

View 1 Replies

Using And Statements With Grouped Radio Buttons

Feb 23, 2012

I'm trying to create a program that displays a calculation based on which radio buttons a user clicks on. I'm not getting any errors during writing or run-time, however only one particular calculation ever gets displayed. I'm not sure why, but it's as if I've gotten the program stuck on the section below. The Calories Label only displays the number 12 regardless of anything else being enabled.

weight = Val(WeightTextBox.Text)
activemale = weight * 15
inactivemale = weight * 13

[Code].....

View 2 Replies

Count - Dictionary In .NET Obtain Keys Grouped?

Aug 3, 2009

I have this dictionary..

Dim Rooms As New Dictionary(Of Integer, Of Integer)
Rooms(1) = 101, 102, 109, 110
Rooms(2) = 103, 104, 105
Rooms(3) = 106, 107

I want to know if i can obtain how many keys are in the dictionary For example, in this dictionary i have 3 keys, and if i use Rooms.Count it returns me 9 thats each pair of keys-values, but i want to obtain 3, each diferent value as key.

EDIT: Sintax error P.D: I cant use now because i have to use in a webservice filtered by IP, but VS2010 if i use count on the dictionary, tells me that will contain the number of the keys/value pairs.

View 1 Replies

Count - Dictionary Obtain Keys Grouped

Jun 9, 2012

I have this dictionary..
Dim Rooms As New Dictionary(Of Integer, Of Integer)
Rooms(1) = 101, 102, 109, 110
Rooms(2) = 103, 104, 105
Rooms(3) = 106, 107

I want to know if I can obtain how many keys are in the dictionary. For example, in this dictionary i have 3 keys, and if I use Rooms.Count it returns me 9 that's each pair of keys-values, but I want to obtain 3, each different value as key. Syntax error. I cant use now because I have to use in a webservice filtered by IP, but VS2010 if I use count on the dictionary, tells me that will contain the number of the keys/value pairs.

View 3 Replies

Displaying Records Grouped By Month And Year In Asp.net?

Oct 11, 2011

I need to sort my records by date (month & year) as displayed on my asp.net page; This is the code I currently have

<table width="40%" border="0" style="margin-left:auto; margin-right:auto;">
<tr><td><asp:Label ID="lblGridHeader" CssClass="TextFont" Text="" runat="server"></asp:Label></td></tr>
<tr>

[code]....

View 2 Replies

Using LINQ To Return First Item From Each Set Of Grouped Data

Apr 16, 2012

Given a simple DTO (AccountExpiry) with properties of 'AccountNo' and 'ExpiryDate' and a List of same created thus:
New AccountExpiry(123, New Date(2012, 4, 1))
New AccountExpiry(123, New Date(2012, 4, 2))
New AccountExpiry(123, New Date(2012, 4, 3))
New AccountExpiry(124, New Date(2012, 4, 2))
New AccountExpiry(124, New Date(2012, 4, 3))
New AccountExpiry(124, New Date(2012, 4, 4))

How do I use LINQ to retrieve the most recent entry per account. I have a feeling this will involve GroupBy and FirstOrDefault. It seems like...
From Item In Source Group By Item.AccountNo Into Group
Should return all of the data grouped appropriately, but It is unclear how I might apply .FirstOrDefault to each group.

View 1 Replies

C# - Linq To XML Query Creating Empty Items From XML

Feb 15, 2010

why is this xml

<?xml version="1.0" encoding="utf-8"?>
<items>
<item id="77" cityID="EE12345" cityDatum="15.2.2010. 11:28:35" />
</items>

[Code]....

If you, as some can't replicate these results, here's a screenshot:

View 1 Replies

Use LINQ To Query The Items Collection Of A ComboBox?

Jul 14, 2011

Is it possible to use LINQ to query the items collection of a ComboBox & get the index of the item that matches what I'm looking for?

View 4 Replies

GridControl - How To Get Total Number Of Master And Grouped Rows

Jan 25, 2012

I found out how to keep track of the currently expanded grouped rows by using 2 events:
- gridview.GroupRowExpanded
- gridview.GroupRowCollapsed

Where I increase or decrement an integer that keeps track of how many group rows are currently expanded. I am now tackling the problem of what to do if the user expands or collapses all of the group rows. I currently know when this is done by checking the e.RowHandle. Any way to find the total number of groups rows currently in the GridView (something like rowcount for normal rows) so I know how many to set the tracking integer to.

For Example: If my current count is 2 and the total number of groups are 15 then when the Expand All is fired the current count is set to 15 rather than 3.

View 1 Replies

Setting A Value In Parent Row For A Column That Is Grouped Sets Child Rows To Nothing

Apr 13, 2012

i have a Janus grid, which has two columns and the rows are organized in a hierarchical order i.e. parent and child rows. One of the columns in the grid is grouped (by dragging the column in the group by box above the grid). I have a cell-changed event that is fired when a cell content is changed. I do this in the cell-changed event:[code]If i change the value, in a parent row, of the grouped column then the child rows are set to Nothing. After the last line in the above snippet, child rows of row are set to Nothing. On the other hand, this problem does not occur if i change the value of the other column (which is not in the group by box). The problem occurs only if i group a column and change its value not otherwise.

View 1 Replies

.net - LINQ Query For Filter By Selected Items In Checkbox List?

Jun 30, 2011

Could not find this through Google or in SO questions.I have a checkbox listbox on my form. I want to filter my List by the list of selected Ids from that listbox that are checked, in SQL I would have done this like "Where TypeId In (1, 4, 5, 7)"... how do I do that in LINQ?

I feel like I am missing a really obvious answer, but cannot get it.

For argument sake... here is the what I have for sample data:

In Colors (List<of currentColors>)
ID, Name, TypeId
1, Red, 1
2, Blue, 1
3, Green, 2
4, Pink, 3

Selected Types 2 and 3 in CheckboxList: filteredColors

[Code]...

View 1 Replies

VS2008 Query - Modify Code To Search For Items Containing Specified Characters

Jul 3, 2010

SELECT ContactID, FirstName, LastName, Male, Female, Email, Address
FROM Contact
WHERE (FirstName LIKE @FirstName + '%')

The above query works and returns values on first names that begin with whatever item I type in a text box.

How can I modify that code to search for items containing specified characters instead of just searching for items that begin with the specified characters. I would still need to use a wildcard because it is a search field. see below for an example

For example can I query the FirstName column to display people who's name contain the letters "an" in that order a before n but anywhere within the word e.g.

- Anna
- Jane
- Samantha

View 7 Replies

Get Selected Item Index Or A Sorted & Grouped List Relative To Item Source?

Feb 12, 2011

I'm having some difficulty with a sorted & grouped listbox in WPF vb.net 3.5 that has an items source bound to an ObservableCollection.

What I want to be able to do is to retrieve a piece of data from the ObservableCollection items source depending on what item in the listbox the user has selected.I've almost got it working but because the listbox is sorted it does not match the index of the items source.

[code]...

As I previously mentioned, because the lstBox is sorted and also grouped the index's don't match up.Does anyone know how I can get the correct information I want from the List Source depending on the selected item in the list box?

View 3 Replies

Find The Number Of Expanded / Collapsed Master Rows And Grouped Rows In A DevExpress GridView?

Jan 18, 2012

I am currently using DevExpress 10.2 within Visual Studio 2010. In a previous question I was trying to print the current user view of a DevExpress GridControl with the user's choice of expanded or collapsed master rows and/or group sections. I was told this was not possible at this time. I have now decided to use the following code:

[Code]...

View 1 Replies

LINQ To SQL Query - Select Customer From Dropdownlist And Then Gridview - Load All Items Of All Orders Of That Customer

Oct 4, 2011

I have the following problem: I select a customer from a dropdownlist and then the gridview should load all items of all orders of that customer. I have the following query:

Dim allorders = From ord In db.Orders

Where ord.CustomerID = Convert.ToInt32(CustomerDropDownList.SelectedValue)

[CODE]..............

I also tried to modify the query as follows:

Dim orderitems = From oi In db.OrderItems

Where oi.OrderNumber = (From From ord In db.Orders

[CODE]...........

But this does not work. I just started using LINQ.

View 5 Replies

Asp.net - Linq Group By An Entity And Then Order By The Grouped (nullable) Entity

Jul 27, 2011

I am trying to build a survey engine from an existing database design which is like this.

[Code]...

The line Order By Key.RecordOrder throws a null reference exception and I can understand why. Can someone advise on how to resolve this by just modifying this one query? I am fairly new to LINQ and the necessity to write this project

[Code]...

View 1 Replies

Sql - When Run The Query In Query Analyzer, It Returns One Row But When Use The Same Query, No Rows Are Returned?

Aug 19, 2010

Here is the code:

Function getData(ByVal id As String)
Dim reader As SqlClient.SqlDataReader
Dim statement As String

[code].....

View 1 Replies

When Creating A Query Error "The Schema Returned By The New Query Differs From The Base Query"

Feb 29, 2012

When Creating a query using the sear Criteria Builder . I keep getting the error : "The schema returned by the new query differs from the base query" what does that mean and how do i avoid this problem in future

View 2 Replies

Access Database And Using A Parameter Query With The LIKE Operator To Return All Rows That Match Query?

Apr 28, 2010

I am connecting to an Access database and using a parameter query with the LIKE operator to return all rows that match query. The string to search for is taken from a Textbox

sql =

"Select * FROM Allview WHERE Info Like" &
"*" &
CStr(TextBox1.Text) &
"*"
The query does not return any data in vb, but when run from access with same string, there is data returned.The connection to the database is done correctly, as I am able to return data with various other queries.

Partial code :
Dim
con As
New OleDb.OleDbConnection[code]....

View 8 Replies

Running A Query On Access From VB, Passing Variable Or Function As Query Parameter

Apr 7, 2012

I am trying to run a query an Access db from VB. The general query which I want to run is

SELECT * FROM Patient WHERE Patient.PatientID = ?
or SELECT * FROM Patient WHERE Patient.PatientLname = ?


I tried using an input box which captured the user input and pass that variable to the query, but that failed. Then I read about writing a function and using that, however, I keep getting an error which says Function not defined, but when stepping through the code, it seems to work. Here is my function: [Code]

View 1 Replies

Correct SQL Query: Error "Microsoft Jet Database Engine Cannot Find The Input Table Or Query 'IF'?

Aug 27, 2010

The database:
"ID (Primary key)" | "Title"
0 | "title1"

[code].....

OK, before adding values to database, we should check if a row exists with this values :)TO do this, creating a Stored Procedure is a best way to deal with the database fastly.So... The problem now is, at the runtime, Miss OleDB throw this error:Microsoft Jet database engine cannot find the input table or query 'IF

View 2 Replies

Correct This SQL Query: Error "Microsoft Jet Database Engine Cannot Find The Input Table Or Query 'IF'?

May 29, 2009

i should say hi experts :D . Help me with this pretty code :)

The database:
"ID (Primary key)" | "Title"
0 | "title1"

[code].....

OK, before adding values to database, we should check if a row exists with this values :)TO do this, creating a Stored Procedure is a best way to deal with the database fastly.So... The problem now is, at the runtime, Miss OleDB throw this error:Microsoft Jet database engine cannot find the input table or query 'IF

View 11 Replies

Asp.net - Complex Linq Query - Add A .Where() Or .Any() Predicate To Reduce Query Complexity (in VB)?

Sep 14, 2010

I have to join two main tables, and I need to filter the results by elements in an ASP.NET web form. These filters are created on the fly so I have to use a lot of where extensions to filter the query. I want to execute the query with as optimized SQL as possible.

I am first doing a simple join between TW_Sites and TW_Investigators. Then there are two sub-tables that are involved. TW_InvestigatorToArea and TW_InvestigatorToDisease. While most of the where clauses are working fine, I have found a performance issue that won't be an issue right now, but will be an issue as the table gets bigger.

The arrays DiseaseCategories and DiseaseAreas would be the results of a CheckBoxList result.

Protected Sub LoadResults()
'Get Dictionary of Filters
Dim FilterDictionary As OrderedDictionary = Session.Item("InvestigatorFilterDictionary")
' Initialize LinqToSql

[code]....

View 2 Replies

Query A DataTable - Change The Query At Runtime (ex. Changing The Customer Name)

Oct 15, 2011

I created a RDCL in the designer. I would like the User to Determine what is shown in the Report. How can I change the query at runtime? (ex. changing the customer Name)

This is the query that I have in the properties:

SELECT [Date], Address, Customer, Orders
FROM Report_qry
WHERE (Customer= 'JohnDoe')

View 5 Replies

Results From Query - Populate Textbox Controls With Query Result?

Mar 11, 2010

how to take a query that returns a single row of data and load that data into textbox controls.I know about ExecuteScalar but it is only good for a single column of data from the query.

View 2 Replies

Asp.net - Complecated SQL Query: Members' Downline Business SQL Query?

Jul 11, 2010

I've got confused at the moment.I've got one database to developed for MLM (Multi level marketing) company.so, there's one Members database.. each member have unique ID called membercode.. and all members can members under them.. can make 2 members down to 1 member.. all members are added in same table named "tbMembers" and for each member there's a "parentID" where I add member's membercode under whome that member is?.. right?

Now what's the matter is .. I want a SQL Query which can go under and under.. like 'John Doe' having two members under him named 'Suzan' and 'Ellie' and 'Suzan' is also having 2 members under her.. and same for Ellie. and for example.. 'John Doe' having about 300 members in his down line? All members are making transactions.. like they purchase product and Invoices are being generated (invoices tables have 'memberID'). Now the query is how to generate query which can calculate John Doe's Downline's TurnOver from invoices table's 'totalAmount' field's SUM?

I'm using Asp.net 2.0 (VB.net) and use SQL Server 2005 Express.

View 2 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved