Solved

Stored Procedure Return Value to .Net c#

Posted on 2013-11-07
8
1,529 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 400 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

630 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