Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stored Procedure Return Value to .Net c#

Posted on 2013-11-07
8
Medium Priority
?
1,583 Views
Last Modified: 2013-11-07
I have a stored procedure that runs a manual backup on one of our sql servers that is the sqlexpress edition. The stored procedure runs correctly from sql and returns either "SUCCESSFUL" or "UNSUCCESSFUL" as an output parameter. What I want to do is set the lblResult.Text to the output value from the stored procedure. Here is my stored procedure:
USE [CRD]
GO
/****** Object:  StoredProcedure [dbo].[sp_FullBackup_CRD]    Script Date: 11/07/2013 10:29:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
==========================================================================
	Author:			Carla M Romere
	Create Date:	11-07-2013
	Description:	Script to Full Backup Crystal Reports Distributor
==========================================================================
*/

ALTER PROCEDURE [dbo].[sp_FullBackup_CRD] 
@retval AS VARCHAR(12) OUTPUT

AS
BEGIN
SET NOCOUNT ON;

BEGIN TRY
	DECLARE @FILENAME AS VARCHAR(255)
	SELECT @FILENAME = N'D:\sql_backups\CRD\CRD_Full_' + CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+CAST(DATEPART(MONTH,GETDATE())AS VARCHAR)+CAST(DATEPART(DAY,GETDATE()) AS VARCHAR)+CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR)+CAST(DATEPART(MINUTE,GETDATE())AS VARCHAR)+CAST(DATEPART(SECOND,GETDATE())AS VARCHAR)+CAST(DATEPART(MILLISECOND,GETDATE())AS VARCHAR) + '.bak'

	BACKUP DATABASE [CRD] TO  DISK = @FILENAME WITH NOFORMAT, NOINIT,  NAME = N'CRD-Full Database Backup', SKIP, NOREWIND, NOUNLOAD

	declare @backupSetId as int
	select @backupSetId = position from msdb..backupset where database_name=N'CRD' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'CRD' )
	if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''CRD'' not found.', 16, 1) end
	RESTORE VERIFYONLY FROM  DISK = @FILENAME WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
	
	SET @retval = 'SUCCESSFUL';
END TRY

BEGIN CATCH
	SET @retval = 'UNSUCCESSFUL';
END CATCH

END

SELECT @retval AS result

Open in new window


When I run this from an aspx page, I get the label instantly set to @retval (literally). Here is my code behind for the click event:
        protected void fullCRD_Click(object sender, EventArgs e)
        {
            string ConnStr = ConfigurationManager.ConnectionStrings["CRDConnectionString"].ConnectionString;

            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();
            SqlCommand dbCommand = new SqlCommand("sp_FullBackup_CRD", conn);
            dbCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter result = dbCommand.Parameters.Add("@retval", SqlDbType.VarChar);
            result.Direction = ParameterDirection.ReturnValue;

            lblResult.Text = result.ToString();
        }

Open in new window


The lblResult.Text should be either "SUCCESSFUL" or "UNSUCCESSFUL" and not literally @retval. I know it's something simple I've missed, but I don't see it.
0
Comment
Question by:Carla Romere
  • 4
  • 3
8 Comments
 
LVL 15

Expert Comment

by:pateljitu
ID: 39631504
Change "result.Direction = ParameterDirection.ReturnValue;" to "result.Direction = ParameterDirection.Output;"
0
 
LVL 15

Assisted Solution

by:unknown_routine
unknown_routine earned 400 total points
ID: 39631513
You code looks good but you need to Execute the command:

 lblResult.Text=SqlCommand.ExecuteScalar()
0
 

Author Comment

by:Carla Romere
ID: 39631616
pateljitu - I made that change and am getting the same exact result. Label immediately goes to @retval.

unknown_routine - I am trying that, but getting an error on the code page itself that says:

"An object reference is required for the non-static field, method, or property 'System.Data.Common.dbCommand.ExecuteScalar()"
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Expert Comment

by:pateljitu
ID: 39631635
In your store procedure [dbo].[sp_FullBackup_CRD]  remove line "SELECT @retval AS result"
0
 

Author Comment

by:Carla Romere
ID: 39631722
This is just weird - I'm getting the same exact thing. Sproc is not firing and label immediately goes to "@retval". Here are a couple of other things I've tried:

Running the sproc from sql:
DECLARE @retval VARCHAR(12)
EXECUTE [CRD].[dbo].[sp_FullBackup_CRD] @retval OUTPUT
SELECT @retval

I get: SUCCESSFUL (no column name)

I tried puttin SELECT @retval back in the sproc before the final END and it ran and also returned the word "SUCCESSFUL."

As soon as I run it from the aspx page, the sproc does not fire and label goes to @retval.
0
 

Author Comment

by:Carla Romere
ID: 39631779
Okay making progress. I changed the output back to an INT and here is my current code behind:
        protected void fullCRD_Click(object sender, EventArgs e)
        {
            string ConnStr = ConfigurationManager.ConnectionStrings["CRDConnectionString"].ConnectionString;

            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();
            SqlCommand dbCommand = new SqlCommand("sp_FullBackup_CRD", conn);
            dbCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter result = dbCommand.Parameters.Add("@retval", SqlDbType.Int);
            result.Direction = ParameterDirection.Output;
            dbCommand.ExecuteNonQuery();

            if (result.ToString() == "1")
            {
                lblResult.Text = "Backup successful";
            }

            if (result.ToString() == "0")
            {
                lblResult.Text = "Backup not successful";
            }
        }

Open in new window

The backup does run, however the label is not getting set to anything now.
0
 
LVL 15

Accepted Solution

by:
pateljitu earned 1600 total points
ID: 39631809
Please try this code:

protected void fullCRD_Click(object sender, EventArgs e)
        {
string ConnStr = ConfigurationManager.ConnectionStrings["CRDConnectionString"].ConnectionString;

            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();

            System.Data.SqlClient.SqlCommand dbCommand = new System.Data.SqlClient.SqlCommand("sp_FullBackup_CRD", conn);
            dbCommand.CommandType = CommandType.StoredProcedure;
           
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@retval", SqlDbType.VarChar, 14, ParameterDirection.Output, false, 0, 10, "retval", DataRowVersion.Default, null));
            dbCommand.ExecuteNonQuery();

            string db_retup = (string)dbCommand.Parameters["@retval"].Value;

            Label1.Text = db_retup.ToString();
}

Open in new window

0
 

Author Closing Comment

by:Carla Romere
ID: 39631856
Thank you so much for your help. Here is the final working code:
        protected void fullCRD_Click(object sender, EventArgs e)
        {
            string ConnStr = ConfigurationManager.ConnectionStrings["CRDConnectionString"].ConnectionString;

            SqlConnection conn = new SqlConnection(ConnStr);
            conn.Open();

            System.Data.SqlClient.SqlCommand dbCommand = new System.Data.SqlClient.SqlCommand("sp_FullBackup_CRD", conn);
            dbCommand.CommandType = CommandType.StoredProcedure;
           
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@retval", SqlDbType.Int, 1, ParameterDirection.Output, false, 0, 10, "retval", DataRowVersion.Default, null));
            dbCommand.ExecuteNonQuery();

            int db_retup = (int)dbCommand.Parameters["@retval"].Value;

            if (db_retup.ToString() == "1")
            {
                lblResult.Text = "Backup was successful.";
            }

            if (db_retup.ToString() == "0")
            {
                lblResult.Text = "Backup was not successful.";
            }
        }

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

824 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