Solved

Stored Procedure Return Value to .Net c#

Posted on 2013-11-07
8
1,490 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:Hers2keep
  • 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:Hers2keep
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
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.

 
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:Hers2keep
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:Hers2keep
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:Hers2keep
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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