Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Value ..... : The Official Microsoft ASP.NET Forums

Reply

riyaju

Member

44 Points

118 Posts

Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Value ....

Sep 24, 2010 05:31 AM|LINK

My Store Prodecure.....

------------------------------------------------------------------------------------

ALTER PROC [dbo].[Polls_Polls_SaveAdmin]
@UserID INT,
@CategoryID INT,
@TypeID INT,
@OptionID INT=NULL,
@Title nvarchar(4000),
@Description nvarchar(MAX),
@StartDate DATETIME,
@EndDate DATETIME,
@PollID INT,
@OptionA NVARCHAR(100),
@OptionB NVARCHAR(100),
@OptionC NVARCHAR(100),
@OptionD NVARCHAR(100)
AS
   DECLARE @SaveStatus INT
   DECLARE @SaveStatus1 INT
   SET @SaveStatus = 0
   SET @SaveStatus1=0

BEGIN
   IF @PollID>0
       BEGIN
       IF NOT EXISTS (SELECT POLLID FROM POLLS WHERE (CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME) ) OR (CAST(CONVERT(VARCHAR, EndDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME) AND PollID<>@PollID))
           BEGIN
              IF NOT EXISTS (SELECT PollID FROM Polls WHERE Title=@Title AND PollID<>@PollID AND Status <>'D')
                   BEGIN
                      IF @TYPEID='2'
                       BEGIN
                           UPDATE [Polls] SET   CategoryID=@CategoryID,Title=@Title,TypeID=@TypeID,Description=@Description,StartDate=@StartDate,EndDate=@EndDate,ModifiedBY=@UserID,ModifiedDate=GETDATE() WHERE PollID=@PollID
                           SET @SaveStatus=@PollID
                           UPDATE [PollOptions] SET OptionA=@OptionA,OptionB=@OptionB,OptionC=@OptionC,OptionD=@OptionD,PollID=@SaveStatus,PostedBY=@UserID,PostedDate=GETDATE(),ModifiedBY=@UserID,ModifiedDate=GETDATE() WHERE PollID=@SaveStatus
                           SET @SaveStatus1=@OptionID
                       END
                       ELSE
                       BEGIN
                           UPDATE [Polls] SET   CategoryID=@CategoryID,Title=@Title,TypeID=@TypeID,Description=@Description,StartDate=@StartDate,EndDate=@EndDate,ModifiedBY=@UserID,ModifiedDate=GETDATE() WHERE PollID=@PollID
                           SET @SaveStatus=@PollID
                       END
                   END
               ELSE

                     SELECT -1;
           END
       ELSE

                   SELECT -3;
       END
   ELSE
       BEGIN
           IF NOT EXISTS (SELECT POLLID FROM POLLS WHERE (CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME) ) OR (CAST(CONVERT(VARCHAR, EndDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME)))
               BEGIN
                   IF NOT EXISTS (SELECT PollID FROM Polls WHERE Title=@Title AND PollID<>@PollID AND Status <>'D')
                      BEGIN
                           IF @TYPEID='2'
                           BEGIN
                                   INSERT INTO Polls (CategoryID,TypeID,Title,Description,StartDate,EndDate,CreatedBY,CreatedDate,ModifiedBY,ModifiedDate) VALUES (@CategoryID,@TypeID,@Title,@Description,@StartDate,@EndDate,@UserID,GETDATE(),@UserID,GETDATE())
                                   SELECT @SaveStatus = @@IDENTITY
                                   INSERT INTO    PollOptions (PollID,OptionA,OptionB,OptionC,OptionD,PostedBY,PostedDate,ModifiedBY,ModifiedDate) VALUES (@SaveStatus,@OptionA,@OptionB,@OptionC,@OptionD,@UserID,GETDATE(),@UserID,GETDATE())
                                   SELECT @SaveStatus1 = @@IDENTITY
                           END
                           ELSE
                           BEGIN
                                   INSERT INTO Polls (CategoryID,TypeID,Title,Description,StartDate,EndDate,CreatedBY,CreatedDate,ModifiedBY,ModifiedDate) VALUES (@CategoryID,@TypeID,@Title,@Description,@StartDate,@EndDate,@UserID,GETDATE(),@UserID,GETDATE())
                                   SELECT @SaveStatus = @@IDENTITY
                              END
                       END
                   ELSE

                          SELECT -1;
               END
           ELSE
                  SELECT -3;
       END

END

----------------------------------------------------------------------------------------------

Coding Part:......

public int SavePoll()
        {
            int bFlag = 0;
            SQLDAL oDAL = new SQLDAL();
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "PollID", SqlDbType.Int, 0, ParameterDirection.Input, iPollID);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@UserID", SqlDbType.Int, 0, ParameterDirection.Input, iUserID);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@Title", SqlDbType.NVarChar, 4000, ParameterDirection.Input, sTitle);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@Description", SqlDbType.NVarChar, 10000, ParameterDirection.Input, sDescription);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@StartDate", SqlDbType.DateTime, 0, ParameterDirection.Input, dtStartDate);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@EndDate", SqlDbType.DateTime, 0, ParameterDirection.Input, dtEndDate);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@CategoryID", SqlDbType.Int, 0, ParameterDirection.Input, iCategoryID);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@TypeID", SqlDbType.Int, 0, ParameterDirection.Input,iTypeID);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@OptionA", SqlDbType.NVarChar, 100, ParameterDirection.Input, sOptionA);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@OptionB", SqlDbType.NVarChar, 100, ParameterDirection.Input, sOptionB);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@OptionC", SqlDbType.NVarChar, 100, ParameterDirection.Input, sOptionC);
            oDAL.AddParamToSQLCmd(oDAL.SQLCommand, "@OptionD", SqlDbType.NVarChar, 100, ParameterDirection.Input, sOptionD);

            oDAL.SetCommandType(oDAL.SQLCommand, CommandType.StoredProcedure, "Polls_Polls_SaveAdmin");

bFlag = Convert.ToInt32(oDAL.ExecuteScalarCmd(oDAL.SQLCommand));

            // (Here Return value 0Xfffffd)
                oDAL = null;
                return bFlag;


        }

Moved by XIII on Sep 24, 2010 08:03 AM

Reply

Mr.Techno

Member

264 Points

87 Posts

Re: Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Val...

Sep 24, 2010 09:23 AM|LINK

HI,

In your stored procedure Use return statement instead of select statement..

like instead of

SElect -3

write

return -3.

hope this will help you.

Click on "Mark as Answer" button if this post is useful.

My blog: PashamBlog

Reply

hitesh s

Participant

1278 Points

303 Posts

Re: Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Val...

Sep 24, 2010 09:26 AM|LINK

Hi,

Check the condition before assigning it to a variable. If the Value is greater than or equal to 0 then proceed with the things else show him some error message and more over use return val.

Thanks,
Hitesh Kumar Vyas S.

"Nobody can do everything, but everyone can do something."

Reply

HoLLoW

Member

215 Points

94 Posts

Re: Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Val...

Sep 24, 2010 02:57 PM|LINK

Here is a little way i do it.

public enum Error
{
    NameOfError1 = -1,
    NameOfError2 = -2,
    NameOfError3 = -3,
    //here are your errors, you could put some names to understand what the number means, easy to remember when programing
}


/// SQL CODE
public int SavePoll()
{   // Connection structure and command    p = new SqlParameter("@ReturnValue", SqlDbType.Int);
    p.Direction = ParameterDirection.ReturnValue;
    command.Parameters.Add(p);


    connection.Open();


    int rowsAffected = command.ExecuteNonQuery();    // here we will retrive the @ReturnValue = @ErrorCode (if not error, then we will return 1, if there is, we will see wich error arrised)
    int errorId = ((p.Value != null) ? ((int)p.Value) : 1);


    if (errorId < 0)
        throw new Exception(GetException((Error)errorId);
    

    return rowsAffected;
}
//




protected string GetException(Error error)
{
   string key;
   switch(erro)
   {
       case Error.NameOfError1;
           key = Errors.SomeErrorFor1;
           break;
       case Error.NameOfError2;
           key = Errors.SobeErrorFor2;
           break;
       // continue with exceptions
   }
   return Errors.GetString(key);
}


internal static class Errors
{


    internal static string GetString(string s) { return s; }


    internal const string SomeErrorFor1 = "Name of Error 1";
    internal const string SomeErrorFor2 = "Name of Eror 2";
    // put more for your Enum error
}

In your DB Procedures:

// Procedure

    DECLARE @ErrorCode int;
    SET @ErrorCode = 0;

    IF NOT EXIST(Youer Code HERE)
    BEGIN
	IF NOT EXISTS (More code here)
	BEGIN
	    Do something here
	END
	ELSE
	BEGIN
	    SET @ErrorCode = -2
	    GOTO CleanUp;
	END
    END
    ELSE
    BEGIN
	SET @ErrorCode = -1;
        GOTO Cleanup;
    END

   IF ( @@ERROR <> 0 )
    
   BEGIN
        
        SET @ErrorCode = -3;
        
        GOTO Cleanup;
    
   END


Cleanup:
   //  you can put transaction here to rollback if you want
   RETURN @ErrorCode;

life is lost in dreaming, dreaming is lost in becoming
-----------------------------------------------------------
Please remember to click “Mark as Answer” on the post that helps you. This can be beneficial to other community members reading the thread.

Edited by HoLLoW on Sep 24, 2010 02:58 PM
Marked as answer by Wencui Qian - MSFT on Oct 01, 2010 06:48 AM

Reply

riyaju

Member

44 Points

118 Posts

Re: Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Val...

Sep 28, 2010 05:13 PM|LINK

thank u

Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Value ..... RSS

4 replies

riyaju

Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Value ....

Mr.Techno

Re: Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Val...

hitesh s

Re: Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Val...

HoLLoW

Re: Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Val...

riyaju

Re: Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Val...

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft