Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-08-12
17
Medium Priority
?
356 Views
Last Modified: 2013-11-05
Hi!

I am trying to execute a procedure that takes an input parameter and returns a single value ( a count of records) that is displayed in a label.

Error      1      Cannot implicitly convert type 'object' to 'System.Data.SqlClient.SqlDataReader'. An explicit conversion exists (are you missing a cast?)      The offending line: drCount = sqlCommandC.ExecuteScalar();
Error      2      Cannot implicitly convert type 'object' to 'int'. An explicit conversion exists (are you missing a cast?)   The offending line:  int iCnt = sqlCommandC.Parameters["@contractType"].Value;

My code:

public partial class _Default : System.Web.UI.Page
{
    protected SqlConnection sqlConnectC;
    protected SqlCommand sqlCommandC;
    protected SqlDataReader drCount;
     
    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();
        drCount = sqlCommandC.ExecuteScalar();
        int iCnt = sqlCommandC.Parameters["@contractType"].Value;
        lblCount.Text = "Count: " + iCnt.ToString();
        drCount.Close();
        sqlConnectC.Close();
   
    }
}


Many Thanks!!!
0
Comment
Question by:allanmark
  • 7
  • 7
  • 3
17 Comments
 
LVL 22

Accepted Solution

by:
JimBrandley earned 525 total points
ID: 19679864
You have two problems. 1. ExecuteScalar returns a single value as an object. So:
string result = sqlCommandC.ExecuteScalar(); // This will work

Second is in this one:
int iCnt = sqlCommandC.Parameters["@contractType"].Value;

Value is an object, so it needs to be:
int iCnt = int.Parse(sqlCommandC.Parameters["@contractType"].Value.ToString());

Jim

 
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 225 total points
ID: 19679868
first of all, we would need to know the procedure code...

now, when you have no resulting recordset, use ExecuteNonQuery() instead of ExecuteReader() or ExecuteScalar():

sqlCommandC.ExecuteNonQuery();
int iCnt = int.Parse(sqlCommandC.Parameters["@contractType"].Value.ToString());
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19679870
>string result = sqlCommandC.ExecuteScalar(); // This will work
should actually be:
object result = sqlCommandC.ExecuteScalar();
or
string result = sqlCommandC.ExecuteScalar().ToString();
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:JimBrandley
ID: 19679873
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
0
 

Author Comment

by:allanmark
ID: 19679919
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;
       
   
    }
}
0
 

Author Comment

by:allanmark
ID: 19679949
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

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19679966
ALTER PROCEDURE [dbo].[countContracty] @contractType int, @contractCount int OUTPUT
AS
select  @contractCount  = count(*) contractCount from authors
where authors.contract = @contractType
0
 

Author Comment

by:allanmark
ID: 19685803
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?
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19702382
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
0
 

Author Comment

by:allanmark
ID: 19712180
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
0
 

Author Comment

by:allanmark
ID: 19712261
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:

0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19712273
@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
0
 
LVL 22

Expert Comment

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

Author Comment

by:allanmark
ID: 19712421
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;
       
   
    }
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19712578
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();
0
 

Author Comment

by:allanmark
ID: 19712706
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);
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19712797
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

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Loops Section Overview
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

578 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