Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql server insert 13 27
ASP.NET MVC identity 6 26
Query Help - MSSQL - Averages 5 25
How come this regular expression allows "e"? 14 35
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

786 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