.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

Subreport using Stored Procedure

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :Windows Application

I've racked my brain trying to figure this out. Hopefully someone can help.

What I am trying to achieve is a User report with the option of showing the Groups the Users belong to.



- Users (accepts UserID as a parameter; if UserID is empty, pulls all users)

- Groups (same parameter as above; I realize I could have used the same stored proc but this is where I am after trial and error)



- Users (main report)

- UserGroups (sub report; accepts a parameter for UserID)

- UsersWithGroups (main report w/ sub report; on the SubReport object, I've added a parameter for UserID)


The Users report works great. It pulls a single user or all users depending on the web user's selection.

The UsersWithGroups report works if the web user selects a single user to display. But when the web user selects all users, the subreport shows all groups for all users.


Code behind:

    Protected Sub RunReport()
        Dim dtUserData As New DataTable


        ReportViewer1.ProcessingMode = ProcessingMode.Local

        If chkShowDetail.Checked Then 'Show Group Subreport
            ReportViewer1.LocalReport.ReportPath = "UserReportWithGroups.rdlc"
            AddHandler ReportViewer1.LocalRep

View Complete Post

More Related Resource Links

Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions


I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

PersonID (FK)
LocationID (FK)

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Creating A Stored Procedure Which Searches Team Names



I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />

Stored procedure generator?


Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.

Create stored procedure from asp.net



we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.



Call SQL Stored Procedure Asyncronously



I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.

Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.

Any help, please.

Best regards,


Unable to select any stored procedure while creating TableAdapters in wizard


I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?

Problems connecting stored procedure to Crystal Reports


I am working on updating a reporting system that uses Crystal Reports.  All of the 250+ reports were created in CR 8 and I have been opening up the old files in Visual Studio 2005 and updating the database location.  All I have had to do with all connections to views and even a couple of stored procedures is create a new connection to the database and then update the old report's datasource location.  The reason I need to update the location is so that the .NET application will use the correct driver.  Everything has worked fine up until I got to one stored procedure.  The old version of the report works perfectly with the stored procedure, but when I try to run the report using VS 2005 I keep getting errors.

First I connected to the database using the Oracle OLE DB provider, then updated the location of the stored procedure in the report.  It then prompts me for the two parameter values for the stored procedure, both of which I keep as NULL so that the report parameters will be passed to the procedure.  Then I get the following error:

Query Engine Error: 'ADO Error Code: 0x
Source: OraOLEDB
Description: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STORED_PROCEDURE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored'

The proce

Trying to run a stored procedure from vb code with oracle data provider.



Here is my SP:

create or replace
 Open p_getuserssignon_recordset1 for
 SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
WHERE substr(UserId,1,2) <> vUid
Order By UserId;

I would like to run this SP from code and fill a gridview with the result. 

I am not sure how to go about this, as I have found several different examples, other than the one I  think I need.

I am using the oracle data provider and I have an input parameter (vUid, which will equal "zz").

First question. When filling a gridview with a result set from a stored procedure should the recordset OUT be defined as a REFCURSOR (like i did above)? 


Do you have example code as to how to execute the SP and fill a gridview?  I keep trying different variations of code i've found on the internet without any success other than getting more confused.

(I am using VS 2005, VB).

Thank you.



Using the BDC API how to catch the returned code when executing a stored procedure?

using BDC API, how can i catch the return code a stored procedure returns?

I tried Entity.Execute to execute a generic method. However, it returns an empty Entity table.

I know you can do it by setting up an output variable. I prefer returning an integer for tracking errors.

Any ideas?



Entity Framework 4.0 Dynamic Data - stored procedure


I'm using Dynamic Data and have created a custom List.aspx page.  I simply want to populate the grid with a stored procedure.  I've seen some posts about doing this with a LinqDataSource but not an EntityDataSource.  Does someone have an example or a different way of doing this?  Thanks.

Stored Procedure in ASP.Net

Dear All, I have a stored procedure with parameter, which i need call from an aspx file.  the stored procedure code is appended below: - The SP when executed thru SQL is working but when called thru a aspx form, the results are not shown.USE [pmscomp] GO /****** Object: StoredProcedure [dbo].[test] Script Date: 08/28/2010 11:20:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[test] (@FAgency nvarchar (1000)) AS DEclare @N int, @intFlag INT, @Tno varchar (max), @EOIno varchar (max), @Ptitle varchar (max), @FundAgency varchar (max), @PropNo varchar (max) create table ##tempjj1 ( TNo VARCHAR (1000), Ptitle VARCHAR (1000), FundAgency VARCHAR (1000)) INSERT INTO ##tempjj1 (TNo, Ptitle, FundAgency) select Eoino As Tno, ProjectTitle, FundingAgency from mtblEoi where FundingAgency = @FAgency and ExternalStatus = 'Awaiting Response' union select Proposalno As Tno, ProjectTitle, FundingAgency from mtblProposal where FundingAgency = @FAgency and ExternalStatus = 'Awaiting Response'; SELECT @N=COUNT(*) FROM ##tempjj1 SET @intFlag = 1 WHILE (@intFlag <=@N) BEGIN Select @Tno=TNo,@Ptitle = Ptitle, @FundAgency = FundAgency From (select Tno, Ptitle, FundAgency, (Row_Number() Over (Order By TNo)) As Rownum From ##tempjj1) P Where P.Rownum =@intFlag SET @intFlag = @intFlag + 1 end drop table ##tempjj1 I have dropdownlist in

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

stored procedure call to C#

Hi I created the Only stored procedure  for dynamically  I want to call that procedure in C# stored procedure CREATE   PROC SearchAndReplace ( @SearchStr nvarchar(100), @ReplaceStr nvarchar(100) ) AS BEGIN   -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.   -- Purpose: To search all columns of all tables for a given search string and replace it with another string   -- Written by: Narayana Vyas Kondreddi   -- Site: http://vyaskn.tripod.com   -- Tested on: SQL Server 7.0 and SQL Server 2000   -- Date modified: 2nd November 2002 13:50 GMT   SET NOCOUNT ON   DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int   SET @TableName = ''   SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')   SET @RCTR = 0   WHILE @TableName IS NOT NULL   BEGIN   SET @ColumnName = ''   SET @TableName =     (   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))   FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_TYPE = 'BASE TABLE'   AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName   AND OBJECTPROPERTY(   OBJECT_ID(   QUOTENAME(TABLE_SCHEM

Error Executing CLR Stored Procedure "Item has already been added. Key in dictionary"

Hello, I'm consuming a web service through assemblies in a Sql Server 2005 database. The client was made with VB.net 2005. Everything was fine in the deploy phase but once I compile the assembly generated in my sql server database and try to execute the CLR stored procedure defined in the assembly it crashes throwing the following error: Error: There was an error generating the XML document. Inner Exception: Item has already been added. Key in dictionary: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Key being added: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Where SqlCifin is the name of the assembly, InfoComercial is the web reference namespace and ParametrosConsultaDTO is a complex type defined in the WSDL to encapsulate the request parameters. I tried almost everything but nothing seems to work: Already checked the enviroment variables and . I would appreciate any help you can provide me.  PD: I'm using WSE 3.0. Thanks, Andres Diab.
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