ExecuteScalar error

Posted on 2004-11-30
Last Modified: 2010-04-15
Hi guys some help please.

I have this sp:

ALTER PROCEDURE dbo.sp_CreateProject
@UserKey int,
@RetailerCompanyKey int,
@ProjectTitle nvarchar(50)

DECLARE @ProjectKey int

SET @ProjectKey = 0

      If Exists(Select ProjectTitle from Project Where ProjectTitle = @ProjectTitle)
            return @ProjectKey
            insert into Project ([ProjectUserKey],[ProjectTitle],[RetailerCompanyKey], [ProjectCreatedOn]) values (@UserKey,@ProjectTitle,@RetailerCompanyKey,getDate())
            Set @ProjectKey = @@IDENTITY

      IF NOT @ProjectKey IS NULL      
            insert into ProjectBrief (ProjectKey) values (@ProjectKey)

            insert into ProjectStatus(ProjectKey,StatusKey,ChangeDate) values (@ProjectKey,'1',getDate())
RETURN @ProjectKey

I call this sp with the following code:

public int NewProject(int userkey, int retailer, string title)
                  SqlParameter[] parameters = new SqlParameter[3];

                  parameters[0] = new SqlParameter("@UserKey", System.Data.SqlDbType.Int,4);
                  parameters[0].Value = userkey;

                  parameters[1] = new SqlParameter("@RetailerCompanyKey", System.Data.SqlDbType.Int,4);
                  parameters[1].Value = retailer;

                  parameters[2] = new SqlParameter("@ProjectTitle", System.Data.SqlDbType.NVarChar,50);
                  parameters[2].Value = title;
                  object obj =SqlHelper.ExecuteScalar(Connection.ConnectionString, CommandType.StoredProcedure, "sp_CreateProject",parameters);
                  if (obj == System.DBNull.Value)
                        return 0;
                        return (int) obj;

Instead of returning the next project number it crashs with an error stating that object is not set to an instance of an object.

If I change the ExecuteScalar to ExecuteNonQuery I get -1 returned.

Any help would be mostly appreciated

Question by:watherton
    LVL 15

    Expert Comment

    ExecuteScalar is for looking at the first field of a select statement.  So you definitely don't want to do that.

    I can see that you are returning items.  What you want to do is add add a return value

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = Connection;
    cmd.CommandText = "sp_CreateProject";
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.Parameters.Add("@UserKey", System.Data.SqlDbType.Int,4);
    cmd.Parameters["@UserKey"].Value = userkey;
    cmd.Parameters.Add("@RetailerCompanyKey", System.Data.SqlDbType.Int,4);
    cmd.Parameters["@RetailerCompanyKey"].Value = retailer;
    cmd.Parameters.Add("@ProjectTitle", System.Data.SqlDbType.NVarChar,50);
    cmd.Parameters["@ProjectTitle"].Value = title;
    cmd.Parameters.Add("RETURN_VALUE", System.Data.SqlDbType.Int,4);
    cmd.Parameters["RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;

    Console.Writeline( Convert.ToInt32(cmd.Parameters["RETURN_VALUE"].Value) );
    LVL 6

    Accepted Solution

    Hi  watherton

    + The ExecuteScalar method returns the first column of the first row of data returned by the command, no matter how many rows the command actually selects.

    when you call:

    return (int) obj;

    it crashed because the obj in your code may not be a number.

    + The ExecuteNonQuery method return the number of rows have been update, so in this case you should used ExecuteNonQuery rather than ExecuteScalar

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
    This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    18 Experts available now in Live!

    Get 1:1 Help Now