Link to home
Start Free TrialLog in
Avatar of KBSLPDev
KBSLPDevFlag for United States of America

asked on

Returning return codes and output params from a sproc using a sqldatasource

Hey all-
I have a stored proc that is sending a return code and an output parameter. I'm using a SQLDataSource to execute the proc and have been using the following code:

        //handle error on return
      int returnval = (int) e.Command.Parameters["@RETURN_VALUE"].Value;
//to be used later as output param
//      string returnmessage = (string)e.Command.Parameters["@vcOutputMsg"].Value;
      if (returnval == -1)
      {
          //lblStatus.Text = returnmessage;
          lblStatus.Text = "Updated failed. Please check process log for details.";
          lblStatus.ForeColor = System.Drawing.Color.Red;
      }
      else
      {
          lblStatus.Text = "Updated successfully";
      }
it worked (return value only) until recently until someone changed the proc to additionally send an output param. Now, i'd like to use that output param but both return value and output param show up as null and an null exception is being thrown.

Ideas on how to do this using my existing sql datasource? Return value isn't a requirement but a nice to have.
Avatar of imitchie
imitchie
Flag of New Zealand image

e.Command.Parameters.Add("@outparam", SQLServerDbType.Numeric, 10).Direction = ParameterDirection.Output;
e.Command.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

retrieve using

x = e.Command.Parameters("@OutputParm").Value)
y = e.Command.Parameters("RETURN_VALUE").Value)
Avatar of KBSLPDev

ASKER

Where do I put the command.param.add? in Source view, I have the following:

        <SelectParameters>
            <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
            <asp:Parameter Direction="InputOutput" Name="vcOutputMsg" Type="String" />
        </SelectParameters>

Everything looks right to me but....no workie.
ASKER CERTIFIED SOLUTION
Avatar of imitchie
imitchie
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Found that if I put a Size="250" as a parameter property, the problem is fixed.