Using RETURN_VALUE from stored procedure via SQLDataSource

Using RETURN_VALUE from stored procedure via SQLDataSource

• K.Sridharan

  I figured this out myself.

  The key is to use the SQLDataSource's ItemInserting event to add a return parmeter, like so:

  Protected Sub sqlTest_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sqlTest.Inserted

  hdnTestId.Value = e.Command.Parameters("RETURN_VALUE").Value

  End Sub

  and then retrieving it's value from the ItemInserted event, also of the SQLDataSource, like so:

  Protected Sub sqlTest_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sqlTest.Inserting

  Dim p As New SqlClient.SqlParameter("RETURN_VALUE", SqlDbType.Int)

  p.Direction = ParameterDirection.ReturnValue

  e.Command.Parameters.Add(p)

  End Sub

  What I was doing wrong was using the FormView's ItemInserting and ItemInserted events.

  Happy coding!

  JZ

  
  

• Sam N.

  Sorry Sohail, that doesn't answer my question. I know how to *generally* extract the return value from a SQLcommand object. The problem is that this doesn't seem to work when I'm using a FormView. I simply can't figure out *where* to put this code so I can capture the return_value. Since I used the wizard to create my SQLDatSource and FormView, there is no code to specify SQLCommand.

  At another attempt, I even tried using ObjectDataSource for this and coded everything, but had the same problem. In that case, how do I capture the return_value of the method that I'm calling to perform the insert

  Thanks for your response.

  JZ

  
  

• alideniz

  AOA!

  // Assumes that connection is a valid SqlConnection object.
  SqlCommand command = new SqlCommand("SampleProc", connection);
  command.CommandType = CommandType.StoredProcedure;
  
  SqlParameter parameter = command.Parameters.Add(
   "RETURN_VALUE", SqlDbType.Int);
  parameter.Direction = ParameterDirection.ReturnValue;
  
  parameter = command.Parameters.Add(
   "@InputParm", SqlDbType.NVarChar, 12);
  parameter.Value = "Sample Value";
  
  parameter = command.Parameters.Add(
   "@OutputParm", SqlDbType.NVarChar, 28);
  parameter.Direction = ParameterDirection.Output;
  
  connection.Open();
  
  SqlDataReader reader = command.ExecuteReader();
  
  Console.WriteLine(
   "{0}, {1}", reader.GetName(0), reader.GetName(1));
  
  while (reader.Read())
  {
   Console.WriteLine(
    "{0}, {1}", reader.GetInt32(0), reader.GetString(1));
  }
  
  reader.Close();
  connection.Close();
  
  Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
  Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

  cheers

  
  
  
• Using RETURN_VALUE from stored procedure via SQLDataSource