• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

Read output parameters from a stored procedure using c#

I am trying to read the output parameters from the stored procedure.  There are two of them.

The stored procedure executes correctly.  But, it does not have rows.  

Now, if i drill into the sqlDR and go to
    Non Public Memebers
    Command
    Parameters
    Non Public Members
    Inner List
    Then open up both of my output parameters, they both have values, one is a code, and the other is a description.  How can I get those values?
   
InterfaceExportShipDetails objInterfaceExportShipDetails = (InterfaceExportShipDetails) objInterfaceExport;
            odbcCon.ConnectionString = InterlinkOneDBConnection();

            SqlConnection ObjSQLConn = new SqlConnection();
            ObjSQLConn = InterlinkOneSQLCon(ObjSQLConn);
            SqlDataReader sqlDR;
            for (int i = 0; i <= objInterfaceExportShipDetails.aryStrHandlingID.Length; i++)
            {
                SqlCommand sqlCmd = new SqlCommand("spShipmentImport", ObjSQLConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("@Cost", objInterfaceExportShipDetails.dblPkgCost);
                sqlCmd.Parameters.AddWithValue("@ShipDate", objInterfaceExportShipDetails.dtShipDate);
                sqlCmd.Parameters.AddWithValue("@TrackingNumber", objInterfaceExportShipDetails.aryStrTrackingNumber[i]);
                sqlCmd.Parameters.AddWithValue("@SystemOrderNumber", objInterfaceExportShipDetails.strOrderNumber);
                sqlCmd.Parameters.AddWithValue("@PackageType", objInterfaceExportShipDetails.strPackageType);
                sqlCmd.Parameters.AddWithValue("@PackageID", (i + 1));
                //BWL - 04.23.2010 - status of 0 means to add the shipment detail to the order.
                sqlCmd.Parameters.AddWithValue("@Status", 0);
                sqlCmd.Parameters.AddWithValue("@ShipServiceCode", objInterfaceExportShipDetails.strShipViaCode);
                sqlCmd.Parameters.AddWithValue("@Weight", objInterfaceExportShipDetails.aryStrPkgWeight[i]);
                sqlCmd.Parameters.AddWithValue("@TotalNumberOfPackages", objInterfaceExportShipDetails.aryStrHandlingID.Length);
                //sqlCmd.Parameters.AddWithValue("@ProductCode", DBNull.Value);
                //sqlCmd.Parameters.AddWithValue("@Quantity", DBNull.Value);
                //BWL - 04.23.2010 - adding 999 as the default id, this means remote process.
                sqlCmd.Parameters.AddWithValue("@UpdateRepID", 999);
                sqlCmd.Parameters.AddWithValue("@ShipMethod", "Processed");
                SqlParameter parmReturnCode = sqlCmd.Parameters.Add("@ReturnCode", SqlDbType.Int);
                parmReturnCode.Direction = ParameterDirection.Output;
                SqlParameter parmReturnDesc = sqlCmd.Parameters.Add("@ReturnDesc", SqlDbType.NVarChar, 255);
                parmReturnDesc.Direction = ParameterDirection.Output;
                
                try
                {
                    objLogging.intSeverity = 1;
                    objLogging.strInterface = "InterlinkOne";
                    objLogging.strEvent = "InterlinkOnePutback";
                    objLogging.strUserID = objInterfaceExportShipDetails.strPSUserID;
                    objLogging.strdeliveryDocNum = objInterfaceExportShipDetails.strOrderNumber;
                    objLogging.strDescription = sqlCmd.CommandText.ToString() + "   " + sqlCmd.Parameters.ToString();
                    objLogging.InsertQuery();
                }
                catch (Exception ex)
                { }
                try
                {
                    sqlDR = sqlCmd.ExecuteReader();
                    
                    while (sqlDR.Read())
                    {
                        string strErrorCode = (sqlDR.GetString(0));
                        string strErrorDescription = (sqlDR.GetString(1));
                    }
                }

Open in new window

0
Bran-Damage
Asked:
Bran-Damage
  • 3
1 Solution
 
DaveJellisonCommented:
Just read your parameters as fields of your command after your command has been executed.



returnCode = (int)sqlCmd.Parameters["@ReturnCode"].Value;
returnDescription = (int)sqlCmd.Parameters["@ReturnDesc"].Value;

Open in new window

0
 
DaveJellisonCommented:
I'm sorry, please ignore the cast to (int) on your return description.



returnDescription = sqlCmd.Parameters["@ReturnDesc"].Value as string;

-or-

returnDescription = sqlCmd.Parameters["@ReturnDesc"].Value.ToString();

Open in new window

0
 
Bran-DamageAuthor Commented:
I did it a little more like this, but you got me almost there.

thanks!

                    string strErrorCode = parmReturnCode.Value.ToString();
                    string strErrorDescription = parmReturnDesc.Value.ToString();
0
 
DaveJellisonCommented:
Ah very cool. I wasn't sure if that would work. Learned something myself so thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now