Execute stored proc that returns single value - displayed in a label

>string result = sqlCommandC.ExecuteScalar(); // This will work
should actually be:
object result = sqlCommandC.ExecuteScalar();
or
string result = sqlCommandC.ExecuteScalar().ToString();

Sorry - First should be:
string retVal = string.Empty;
object obj = sqlCommand.ExecuteScalar();
if (obj != null)
{
   retVal = obj.ToString();
}
If you are using it for anything. Otherwise, can just be:
sqlCommand.ExecuteScalar();

to ignore the return value.

Jim

Talk about quick response - THANKS!!!

Code amended as suggested - compilation is clean. When I run the app and click on the button, I get the following error:

Server Error in '/Stored_Proc_03_Scalar_Param' Application.
Object reference not set to an instance of an object.

 Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
   _Default.Button1_Click1(Object sender, EventArgs e) +29
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919


Amended code:

public partial class _Default : System.Web.UI.Page
{
    protected SqlConnection sqlConnectC;
    protected SqlCommand sqlCommandC;
         
    private string conString = (@"Server=MASK\SQLEXPRESS;trusted_connection=yes;database=Pubs");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            sqlConnectC = new SqlConnection(conString);
            sqlCommandC = new SqlCommand("countContracty", sqlConnectC);
            sqlCommandC.CommandType = CommandType.StoredProcedure;

            // Input parameter
            SqlParameter workParam = new SqlParameter("@contractType", SqlDbType.Int);
            workParam.Direction = ParameterDirection.Input;
            workParam.Value = 1;
            sqlCommandC.Parameters.Add(workParam);

            // Output parameter
            SqlParameter workParam2 = new SqlParameter("@contractCount", SqlDbType.Int);
            workParam2.Direction = ParameterDirection.Output;
            sqlCommandC.Parameters.Add(workParam2);

        }
    }

    protected void Button1_Click1(object sender, EventArgs e)
    {
        if (RadioButtonList1.SelectedIndex == 0)
        {
         sqlCommandC.Parameters["@contractType"].Value = 0;
        }
        else
        {
            sqlCommandC.Parameters["@contractType"].Value = 1;
        }

        sqlConnectC.Open();
        string result = sqlCommandC.ExecuteScalar().ToString();
        sqlConnectC.Close();
        //int iCnt = int.Parse(sqlCommandC.Parameters["@contractType"].Value.ToString());      
        //lblCount.Text = "Count: " + iCnt.ToString();
        lblCount.Text = "Count: " + result;
       
   
    }
}

Sorry - I must be loosing my mind!!!

The code for the stored proc:

USE [Pubs]
GO
/****** Object:  StoredProcedure [dbo].[countContracty]    Script Date: 08/12/2007 21:52:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[countContracty] @contractType int, @contractCount int OUTPUT
AS
select count(*) contractCount from authors
where authors.contract = @contractType

ALTER PROCEDURE [dbo].[countContracty] @contractType int, @contractCount int OUTPUT
AS
select  @contractCount  = count(*) contractCount from authors
where authors.contract = @contractType

Applied stored proc as suggested and got the following:

Msg 102, Level 15, State 1, Procedure countContracty, Line 3
Incorrect syntax near 'contractCount'.

Changed it to:

ALTER PROCEDURE [dbo].[countContracty] @contractType int, @contractCount int OUTPUT
AS
select  @contractCount  = count(*) from authors
where authors.contract = @contractType
(dropped the contractCount after count(*) )

Recompiled the Web app and ran it - same error as previously??

What am I missing?

By previously, do you mean the NullReference exception? If so, Open your project in visual studio, and from the main menu, select Debug/Exceptions. In the ensuing dialog, select to break on exceptions whether handled or not. Then start the application. Now the app will break into the debugger on the line that is causing trouble.

When that happens, you can select an object or variable and right-click to pop a context menu. Select Quickwatch. That will show current values for the selected objects. Also, the call stack is live, so if you are getting something unexpected passed into a method, you can click on the caller to go to that position in that class, and you have live data there to examine.

Try this and let us know what you find.

Jim

Hi Jim!

Sorry about the delay ......

It breaks on the following line:   sqlCommandC.Parameters["@contractType"].Value = 0;

It should read: sqlCommandC.Parameters["@startType"].Value = 0;

Re-ran it  -  error is still the same!!!!!!!!!!!!!!!!!1

Rebooted my machine. Retested. Now getting this:

Server Error in '/Stored_Proc_03_Scalar_Param' Application.
An SqlParameter with ParameterName '@startType' is not contained by this SqlParameterCollection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IndexOutOfRangeException: An SqlParameter with ParameterName '@startType' is not contained by this SqlParameterCollection.

Source Error:

Line 51:         else
Line 52:         {
Line 53:          sqlCommandC.Parameters["@startType"].Value = 1;
Line 54:         }
Line 55:

@startType does not exist in the code you have posted. It is using @contractCount in the stored proc and the parameter creation.

That could be why it is null.

Jim

The name you use for that parameter needs to be the same every place you reference it. In the stored proc and C# both.

ALTER PROCEDURE [dbo].[countContracty] @contractType int, @contractCount int OUTPUT

Changed source code to:

SqlParameter workParam = new SqlParameter("@startType", SqlDbType.Int);
SqlParameter workParam2 = new SqlParameter("@contractCount", SqlDbType.Int);

Just to try something crazy, I copied all the code into the Button1_Click, as shown below. The app then crashes on this line:

Line 55:         string result = sqlCommandC.ExecuteScalar().ToString();

 Procedure or function 'countContracty' expects parameter '@contractType', which was not supplied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'countContracty' expects parameter '@contractType', which was not supplied.


The temp changed code:

protected void Button1_Click1(object sender, EventArgs e)
    {
        sqlConnectC = new SqlConnection(conString);
        sqlCommandC = new SqlCommand("countContracty", sqlConnectC);
        sqlCommandC.CommandType = CommandType.StoredProcedure;

        // Input parameter
        SqlParameter workParam = new SqlParameter("@startType", SqlDbType.Int);
        workParam.Direction = ParameterDirection.Input;
        workParam.Value = 99;
        sqlCommandC.Parameters.Add(workParam);

        // Output parameter
        SqlParameter workParam2 = new SqlParameter("@contractCount", SqlDbType.Int);
        workParam2.Direction = ParameterDirection.Output;
        sqlCommandC.Parameters.Add(workParam2);

        if (RadioButtonList1.SelectedIndex == 0)
        {
         sqlCommandC.Parameters["@startType"].Value = 0;
        }
        else
        {
         sqlCommandC.Parameters["@startType"].Value = 1;
        }

        sqlConnectC.Open();
        string result = sqlCommandC.ExecuteScalar().ToString();
        sqlConnectC.Close();
       
        lblCount.Text = "Count: " + result;
       
   
    }

1. If you want to change the parameter name, you need to drop the stored proc and rebuild it with the new name.
drop procedure [dbo].[countContracty]

CREATE PROCEDURE [dbo].[countContracty] @startType int, @contractCount int OUTPUT
AS
select  @contractCount  = count(*) from authors
where authors.contract = @startType
go

2. Execute scalar is the wrong call if you are getting the result through an OUT parameter. instead, invoke ExecuteNonQuery

Notice how the SP has an OUT parameter, but the code is looking for a return value. So, change this:
        string result = sqlCommandC.ExecuteScalar().ToString();
        sqlConnectC.Close();
       
        lblCount.Text = "Count: " + result;

to this:
        sqlCommandC.ExecuteNonQuery();
        sqlConnectC.Close();
       
        lblCount.Text = "Count: " +sqlCommandC.Parameters[1].ToString();

Had to change this line: lblCount.Text = "Count: " +sqlCommandC.Parameters[1]..ToString();
to this: lblCount.Text = "Count: " +sqlCommandC.Parameters[1].VALUE.ToString();

Works - 100% !!!!!!!!!!!!!!

One last (I hope) question befiore we put this one to bed:

The following few lines of code - I would have imagined that they belong in the PageLoad section.Yet, we get this error:

Server Error in '/Stored_Proc_03_Scalar_Param' Application.
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 46:         if (RadioButtonList1.SelectedIndex == 0)
Line 47:         {
Line 48:          sqlCommandC.Parameters["@startType"].Value = 0;
Line 49:         }
Line 50:         else

Source File: d:\MyStuff - Allan\New Horizons\Training\Course_Practicals\ASP_Projects\Stored_Proc_03_Scalar_Param\Default.aspx.cs    Line: 48


Is my understanding of this flawed??

THE CODE:
        sqlConnectC = new SqlConnection(conString);
        sqlCommandC = new SqlCommand("countContracty", sqlConnectC);
        sqlCommandC.CommandType = CommandType.StoredProcedure;

        // Input parameter
        SqlParameter workParam = new SqlParameter("@startType", SqlDbType.Int);
        workParam.Direction = ParameterDirection.Input;
        workParam.Value = 99;
        sqlCommandC.Parameters.Add(workParam);

        // Output parameter
        SqlParameter workParam2 = new SqlParameter("@contractCount", SqlDbType.Int);
        workParam2.Direction = ParameterDirection.Output;
        sqlCommandC.Parameters.Add(workParam2);

I do not work in code-behind very much - stick in business objects abd data layer, but I suspect you have to do something like this:
RadioButtonList rbl =  this.FindControl("RadioButtonList1") as RadioButtonList;
if (rbl) != null)
    do stuff
else
    handle the error

Jim