Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-27
4
Medium Priority
?
597 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:KBSLPDev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20362408
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)
0
 

Author Comment

by:KBSLPDev
ID: 20366029
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.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 750 total points
ID: 20368672
try these instead

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

Author Comment

by:KBSLPDev
ID: 20368738
Found that if I put a Size="250" as a parameter property, the problem is fixed.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question