Solved

Using a stored procedure with output parameters in a gridview

Posted on 2006-10-19
3
603 Views
Last Modified: 2008-03-17
He there:

I have a stored procedure that has some input parameters and some output parameters. The input parameters are supposed to be drawn from two user controls (hiddenfields) and one is supposed to be taken from the querystring. The output parameters are supposed to be viewed in the detailsview that displays my data.

However I have no luck doing this. It seems as if I cannot display the output parameters as rows in my gridview, and it seems I have to give my output parameters a value before the stored procedure is executed - which is pointless, because this is not relevant.

Any ideas?

The stored procedure looks like this:
ALTER PROCEDURE dbo.ejNotifiedTilmeldinger

            @antal int OUTPUT,
            @not_notified bit output,
            @dato varchar(30),
            @kursus_id int,
            @profil_id int

AS
      declare @antal2 int
      
      SET @antal=0
      set @antal2=0
      set @not_notified='false'
      
      select @antal = count(*)
      from kursusTilmelding
      WHERE kursusTilmelding.dato = @dato
      AND        kursusTilmelding.kursus_id=@kursus_id
      AND        kursusTilmelding.profil_id = @profil_id
      AND        kursusTilmelding.slettet = 'false'
      and   kursusTilmelding.notified = 'false'
      
      select @antal2 = count(*)
      FROM  undertilmeldingNy
      WHERE undertilmeldingNy.dato = @dato
      AND        undertilmeldingNy.kursus_id = @kursus_id
      AND   undertilmeldingNy.slettet = 'false'
      AND        undertilmeldingNy.betalingsprofil_id = @profil_id
      and   undertilmeldingNy.notified = 'false'
            
      set @antal=@antal+@antal2
      if @antal>0 set @not_notified='true'

RETURN @antal
0
Comment
Question by:RunePerstrup
3 Comments
 
LVL 10

Accepted Solution

by:
jnhorst earned 500 total points
ID: 17777569
The problem is probably that when you construct your SqlCommand, the output params are not identfied as such in the Direction property.  If you are using VS2003, try this:

Open the Server Explorer and create a connection to your database.  Show your stored procedures and then drag the one you want to use onto your web page when it is in design mode.  You should see an icon appear in the component tray below the page in design view.  This represents a SqlCommand object, the code for which has been written by the IDE in the "Web Form Designer generated code" section of your code page.

You can right click this icon and select Properties.  Inspect the Parameters collection and you will see that your output params have been declare as such.

Set the input params in code, ExecuteNonQuery() against the command, and then get the values from the output parameters.

John
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question