Return data from SQL Server using Stored Procedure

Question

I am having trouble returning data from my sql-server database to an aspx page using a stored procedure and was hoping somebody could highlight where I am going wrong.

When I run the project data is successfully input into the table but nothing is returned on the following page (Confirm.aspx)

Confirm.aspx.cs

using Devworks;

namespace OSQARv0._1
{
    public partial class Confirm_Questionnaire : System.Web.UI.Page
    {
        OscarSQL b;

        protected void Page_Load(object sender, EventArgs e)
        {
            b = new OscarSQL();
            string qstname = b.GetQuestionName();
            ReturnQstID.Text = qstname;          

        }// End Page_Load

    } // Emd Class Confirm_Questionnaire

} // End Namespace

SQL.cs (App Code)

public OscarSQL()
        {
            _productConn = new SqlConnection();
            _productConnectionString += "data source=mssql.database.co.uk; Initial Catalog=devworks_oscar;User ID=username;Password=password";
            _productConn.ConnectionString = _productConnectionString;
        }
    public string GetQuestionName()
            {
                SqlCommand myCommand = new SqlCommand("GetQuestion", _productConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter retval = myCommand.Parameters.Add("@QUESTTEXT", SqlDbType.VarChar);
                retval.Direction = ParameterDirection.Output;
                _productConn.Open();
                string returnvalue = (string)myCommand.Parameters["@QUESTTEXT"].Value;
                _productConn.Close();
                return returnvalue;
            }

Stored Procedure

USE [devworks_oscar]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [hgomez].[GetQuestion]  

AS
    /*SET NOCOUNT ON;*/
    SELECT QuestionText FROM [Questions] WHERE QuestionnaireID = 21
    RETURN

Any help would be much appreciated.

with dapper: string value = connection.Query<string>("GetQuestion", commandType: CommandType.StoredProcedure).Single(); - just sayin' — Marc Gravell
– Marc Gravell, Commented Nov 9, 2011 at 19:37

McKay · Accepted Answer · 2011-11-09 19:26:21Z

8

You aren't executing the proc. You are setting it up, but not returning it.

string returnvalue = (string)myCommand.ExecuteScalar();

answered Nov 9, 2011 at 19:26

McKay

12.7k7 gold badges58 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

McKay Over a year ago

@MarcGravell looks like a scalar to me? SELECT QuestionText FROM [Questions] WHERE QuestionnaireID = 21

Marc Gravell Over a year ago

my apologies - I read it in a hurry and imagined a SELECT @somevar = ...

HGomez Over a year ago

@MarcGravell I will in future be using a variable such as @personID, will ExecuteScalar() still be correct?

McKay Over a year ago

@Rupert, if you are assigning a parameter in a SQL statement (which is what he thought), then ExecuteScalar won't return anything, but if you are just selecting from an existing variable, then you still want ExecuteScalar

McKay Over a year ago

@Rupert, you should use ExecuteScalar, because the result of that query returns precisely one value.

|

Jeff Ogata · Accepted Answer · 2011-11-09 19:23:17Z

4

Your stored procedure does not use an output parameter to return a value, so instead, use ExecuteScalar():

string returnvalue = (string)myCommand.ExecuteScalar();

answered Nov 9, 2011 at 19:23

Jeff Ogata

58k20 gold badges117 silver badges128 bronze badges

2 Comments

stefan Over a year ago

In this particular case he should use ExecuteScalar

Jeff Ogata Over a year ago

@stefan, yeah, realized that immediately after I posted :)

Jon Raynor · Accepted Answer · 2011-11-09 19:28:52Z

1

A return value is what the stored procedure returns, typically this is an integer value, sometimes it is used to drive business logic or error conditions. In your case, you want the data that is returned, not the return value. So, use ExecuteScalar if the query is returning a single value or ExecuteReader if the query is returning one or more records of data. ExecuteNonQuery is typically used for insert/updates and/or delete operations.

answered Nov 9, 2011 at 19:28

Jon Raynor

3,9126 gold badges33 silver badges45 bronze badges

Comments

MethodMan · Accepted Answer · 2011-11-14 16:11:18Z

1

ExecuteScalar() is a good idea if you know there is only one occurance of that record in the database, ExecuteScalar if I am not mistaken only returns the first record or occurance it finds.. you could use a datareader and do datareader.ExecuteReader() this is forward only but there are ways to make it biDirectional hehe... :) anyway if you want to loop thru the results use ExecuteReader() if you want to execute a StoredProceduere with UDATE,DELETE, or INSERT use reader.ExecuteNonQuery().

Hope this helps.

answered Nov 14, 2011 at 16:11

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

Collectives™ on Stack Overflow

Return data from SQL Server using Stored Procedure

4 Answers 4

6 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related