asked on

ExecuteScalar error

Hi guys some help please.

I have this sp:

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

AS
DECLARE @ProjectKey int

SET @ProjectKey = 0

SET NOCOUNT ON
      If Exists(Select ProjectTitle from Project Where ProjectTitle = @ProjectTitle)

            return @ProjectKey
      Else
      Begin
            insert into Project ([ProjectUserKey],[ProjectTitle],[RetailerCompanyKey], [ProjectCreatedOn]) values (@UserKey,@ProjectTitle,@RetailerCompanyKey,getDate())
            Set @ProjectKey = @@IDENTITY
      End

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

            insert into ProjectStatus(ProjectKey,StatusKey,ChangeDate) values (@ProjectKey,'1',getDate())
      End
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;
                  }
                  else
                  {
                        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

Wayne

SRigney

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;

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

ASKER CERTIFIED SOLUTION

melodiesoflife

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial