.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Return sum of 2 subsets to single SqlDataReader?

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :ASP.Net

I still have plenty to learn about SQL... I'm pretty sure what I want to do is possible, but I'm getting lost in the syntax.

I have two tables:

TABLE A: [ID] [Name]

TABLE B: [ID] [TableA.ID] [intValue] [intTypeID]

There are 3 pieces of information I want:

Total Sum(TableB.intValue)

Sum(TableB.intValue) WHERE intTypeID = 1

Sum(TableB.intValue) WHERE intTypeID = 2

Getting one of those pieces is simple: SELECT a.ID,a.Name,SUM(b.intValue) WHERE a.ID = x GROUP BY a.ID,a.Name

I assume I need some kind of JOIN, but I can't seem to get it... any ideas?

Thank you!

View Complete Post

More Related Resource Links

How to return looped procedure results so that they display as multiple rows of a single output inst


I understand that the below fragment will return a separate 1-row results set for each successful loop (also unnecessarily repeating the column header each time). How can I alter my procedure so that each value returned by the loop is appended underneath the previous value as multiple rows of a single results set? Do I have to store each return value in a temp table or someother storage object? It would be nice to just be able to spit them out row by row as each loop returned. Possible? Easy? Hard? I really appreciate the help I've received here so far.

SELECT (whatever)

OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
EXECUTE pr_FindExpenses4Tech @techNumLoop, @itemCount OUTPUT
SELECT @itemCount Item_Count

Application Architecture in Windows Forms 2.0-Single-MDI Applications

Consider an MDI application like Microsoft Excel; files opened from the file system (by double-clicking) are all opened as separate child windows within the parent Excel window.7 For the first instance of an MDI application to open a new child window to display the file that was passed to the second instance of the application, the second instance must be able to communicate with the initial instance.

ListView DataPager with SQL-Statements that already return only paged data.



As far as I understand the ListView-Control and the DataPager-Control, they work like this: The ListView gets its datarows from its data source, then the DataPager causes the ListView to display only a subset of those rows.

So what, when I want to use custom databinding for the ListView, that *already* gets the certain subset from SQL-Server (for better performance). Can I and how can I use the DataPager then?

Thanx, S.

Need Syntax To Make Results of LINQ Union Return Non-Generic Type



I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter

I have been going about trying to convert the above into a LINQ method and am having problems with return types.
As the above SQL illustrates, I am querying two distinct LINQ tables - Alerts and StatusUpdates -
and then attempting to perform a Union on them. In order to do the union in LINQ, I have to have agreeing types. So, I
am using "var" to make the results of "alerts" and "updates" generic before the union.  

public IList GetStatusUpdatesAsAlertsByAccountId<T>(Int32 accountId)
            using (WorkbookDataContext dc = _conn.GetCont

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

sqlDataReader dos not get results


I am trying to run a stored proc from this function. When I run it thorugh the debugger I can see that myReader.HasRows = true and myReader.FieldCount =14.

But the control does not go in the loop while (myReader.Read()) where I am trying to read Version field returned by the stored proc.

Pls check me code and suggest correction. Thank You.

private static int Get_CaseInfo(string CaseNum)
            int Ver = 0;
            String myConn = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
            SqlConnection conn = new SqlConnection(myConn);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;

            SqlDataReader myReader = null;
            String stmt1 = "EXEC [REPORTS].[CaseInfoByCaseNo_Get] @CaseNo = '" + CaseNum + "'"; 
            cmd.CommandText = stmt1;
            cmd.Connection = conn;
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                    Ver = (int)myReader["Version"];

            catch (Exception)
            return Ver;         

XMLHTTP.ResponseText return unexecuted code?


I have a simple asp with the following code.  When I access this page directly (via browser) everything works fine:  the xml object shows up fine.  When I call this page via VBA (excel), the ResponseText method of the 

XMLHTTP object returns the below code unexecuted.  Anybody knows what's going on?  thanks.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <% Response.Buffer = True Response.ContentType = "text/xml" Dim oDataXML As Object 'Custom Data XML passed in by caller. On Error Resume Next 'Load the XML passed into the request. oDataXML = Server.CreateObject("Microsoft.XMLDOM") oDataXML.Load(Request) Dim sResult As String sResult = "<?xml version=""1.0""?>" sResult = sResult & "<OrderProcessed>" sResult = sResult & "<Status>test</Status>" sResult = sResult & "<OrderID>111</OrderID>" sResult = sResult & "</OrderProcessed>" Response.Write(sResult) Response.End() %>

how To Insert the single quotes in sqlcommand in asp.net


Hi all

    i am developed on simple user registeration application in that i gave  address field .when i am trying to enter something like this (1st main,b'lore) but wont take while inserting.

my query is

sqlcommand cmd=new sqlcommand("insert into registeration_form(name,address)values('"+txtname.text+"','"+txt_address+"')",con);

how can i pass the  quotes also to my query please if any one know reply me.


G Dinakaran

Posting data to ASMX and return Object for jQuery


I am using fluent NHibernate with WebForms and I am trying to build a page where I allow a user to post a status update on their profile page and I a using a .asmx WebService to post the data to the database and then returnig a StatusUpdate instance to the page to be used by jQuery. I have a couple problems.

1) First off when I return a string from the WebService(i was testing) the textbox where the user enters their status doesnt empty the contents. And since the page doesnt refresh even if I manally clear out the textbox and put in something else, it still posts the previous status to the database again. How do I fix this?

2) Secondly, when I return the StatusUpdate Object from the Webservice I cant the results to display anything. Like I sad, Im using jQuery to make an AJX call to the WebService.

Here is my code:

User Profile Page:

Status: <asp:Label ID="status" runat="server"></asp:Label><span style="font-size:.7em;"><asp:Label ID="statustime" runat="server"></asp:Label></span><br />
<textarea id="statusBox" rows="3" cols="40"></textarea><br />
<input type="button" value="Update Status" onclick="SetStatus(); return false;

Inside Microsoft patterns & practices: Building WPF and Silverlight Applications with a Single Code


This article discusses the Project Linker tool and other techniques to create applications that target both WPF and Silverlight from a single code base.

Erwin van der Valk

MSDN Magazine August 2009

Cutting Edge: Single-page Interface and AJAX Patterns


This month we begin a look at the Single Page Interface (SPI) model and some design patterns for designing AJAX applications.

Dino Esposito

MSDN Magazine May 2008

Single Sign-On: A Developer's Introduction To Active Directory Federation Services


Use Active Directory Federation Services to allow other organizations to use your Web applications without the need for you to grant access explicitly.

Keith Brown

MSDN Magazine November 2006

Return of the Rich Client: Code Access Security and Distribution Features in .NET Enhance Client-Sid


Rich clients employ many of the features and conveniences of the operating system they run on, and the list of these features has been growing since the dawn of the PC. But as apps have migrated to the Web, the trend towards increasing client-side functionality has ground to a virtual halt. There are several reasons for this; chief among them are security and deployment problems. But that's all about to change. With the .NET Framework, you can participate in building the distributable rich client of the future. In this article, the author enumerates the pertinent features of .NET that will allow you to build safe, easily deployable controls. The features discussed include managed code, code access security, versioning control, Windows Forms classes, and isolation.

Jason Clark

MSDN Magazine June 2002

SQL and XML: Use XML to Invoke and Return Stored Procedures Over the Web


Front-end developers frequently want to add functionality to the presentation tier of an n-tier architecture, but such requests can require changes on all tiers just to get the data and present it. This process can be made easier and more flexible by using SQL Server stored procedures to automate the delivery of data in XML format from the database to the front-end components. In the component presented here, stored procedures are invoked by XML strings, XML is returned, converted using XSL, and presented to the client in HTML. The technique supports rapid changes yet doesn't sacrifice the n-tier approach. This approach can be used with either SQL Server 7.0 or SQL Server 2000.

Dave Grundgeiger, Anson Goldade, and Varon Fugman

MSDN Magazine August 2001

how to set multiple button in single image


Hi friends,

I need to make multiple button in a single image in asp.net.

and need coding also for vb.net.

thanx in advance

linq and max, if return null return error


hi there
i have this query :

var result2 = (from TH in DB.Threshold
                          where TH.ProductSKUID == int.Parse(HID_ProductSKUID.Value)
                          select TH.ThresholdID).Max();

the problem is that if this query does not find any items, return null, so thow exception
how can i do to return 0??

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend