Why does stored procedure always return-1 fetch the Stored Procedure value from the dataset?

 

I. Why does the stored procedure always return-1?

 

Are you using ExecuteNonQuery to perform the "add" operation?

 

ExecuteNonQuery does not return any row of data, but any output parameter or return value mapped to the parameter will be filled with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

For all other types of statements, the return value is-1. If rollback occurs, the returned value is-1.

 

If a stored procedure is executed, the returned value must be-1. Therefore, you cannot determine whether the execution is successful Based on the returned value.

 

Reference above (CSDN: benbirdar's speech)

 

Ii. Obtain the Stored Procedure Value

 

Process:

 

Alter procedure [dbo]. [Up_ B _EQCalculation]

 

@ AmmId varchar (18) = '',

 

@ RtuCode varchar (12) = '',

 

@ Caltime datetime,

 

@ Total decimal (18, 2 ),

 

@ ReEQ decimal (18, 2) = 0 OUTPUT

 

 

 

 

SqlParameter dSQLPara = new SqlParameter ("@ ReEQ", SqlDbType. Decimal );

 

DSQLPara. Direction = ParameterDirection. Output;

 

Cmd. Parameters. Add (dSQLPara );

 

 

 

Cmd. CommandText = "Up_ B _EQCalculation ";

 

Cmd. CommandType = CommandType. StoredProcedure;

 

Cmd. ExecuteReader ();

 

Cmd. Connection. Close ();

 

 

 

StrReturn = dSQLPara. Value. ToString (); // Return Value

 

 

 

Iii. dataset Extraction

 

String cnstr = "data source =.; initial catalog = company; persist security info = False; user id = sa; pwd = sa ;";

 

Private SqlCommand cm = new SqlCommand (); // create a Command object

 

// Run the stored procedure and return DataSet

 

Public DataSet runSPDataSet (string StoredProcedureName)

 

{

 

Cm. Connection = new SqlConnection (cnstr );

 

Cm. Connection. Open ();

 

Cm. CommandText = StoredProcedureName;

 

Cm. CommandType = CommandType. StoredProcedure;

 

Try

 

{

 

SqlDataAdapter da = new SqlDataAdapter (cm );

 

DataSet DS = new DataSet ();

 

Da. Fill (DS );

 

Return DS;

 

}

 

Catch (Exception ex)

 

{

 

Throw ex;

 

}

 

Finally

 

{

 

Cm. Connection. Close ();

 

}

 

}

 

// Execute the stored procedure by passing in the stored procedure name, and put the first record in the first returned record set in the Object array.

 

// Mostly display details, because in this case, only one record is used. If it is null, strValue [0] = "null" is returned ".

 

 

 

// Run the stored procedure and return the array of the first record

 

Public Object [] runSPItems (string StoredProcedureName)

 

{

 

Object [] strValue = new Object [1];

 

Cm. CommandText = StoredProcedureName;

 

Cm. CommandType = CommandType. StoredProcedure;

 

Try

 

{

 

Cm. Connection. Open ();

 

SqlDataReader r = cm. ExecuteReader (CommandBehavior. CloseConnection );

 

If (r. Read ())

 

{

 

StrValue = new Object [r. FieldCount];

 

R. GetValues (strValue );

 

}

 

Else

 

{

 

StrValue [0] = "null ";

 

} R. Close ();

 

}

 

Catch (Exception ex)

 

{

 

Throw ex;

 

}

 

Finally

 

{

 

Cm. Connection. Close ();

 

}

 

Return strValue;

 

}

 

 

 

 

From: Hanshan