Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored Procedure Return Value to .Net c#

Posted on 2013-11-07
8
Medium Priority
?
1,549 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 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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