Solved

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

Posted on 2007-11-27
4
593 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
  • 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 250 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

685 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