Solved

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

Posted on 2007-11-27
4
584 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now