Solved

Stored Procedure Return Value to .Net c#

Posted on 2013-11-07
8
1,467 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
 
LVL 15

Expert Comment

by:pateljitu
ID: 39631635
In your store procedure [dbo].[sp_FullBackup_CRD]  remove line "SELECT @retval AS result"
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now