locked
STORED PROCEDURE PEFORMING AN INSERT FROM C# app but ResultMessage not coming back RRS feed

  • Question

  • The folowing is my C# code

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;

    namespace INSERT_NEW_CUSTOMER
    {

        class Program
        {
            public static string custid;
            public static string companyname;


            public void RunStoredProc()
            {
              
               string retu = "";

                try
                {
                    SqlConnection conn = null;
                    SqlDataReader rdr = null;
                    String result = "";
                    conn = new SqlConnection("Data Source=RAY-PC;Initial Catalog=Northwind;User ID=sa;Password=stevena!");
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("dbo.InsertCustomer", conn);

                    cmd.CommandType = CommandType.StoredProcedure;
                   SqlParameter myParm1 = cmd.Parameters.Add("@CustomerID", SqlDbType.Text, 20);
                    myParm1.Value = custid;

                    SqlParameter myParm2 = cmd.Parameters.Add("@CompanyName", SqlDbType.Text, 20);
                    myParm2.Value = companyname;

                   SqlParameter myParm3 = cmd.Parameters.Add("@ReturnMessage", SqlDbType.Text,255);
                //    myParm3.Value = result;


                   SqlParameter myParm4= cmd.Parameters.Add("@ReturnCode", SqlDbType.Int,20);
                //    myParm3.Value = result;


                        rdr = cmd.ExecuteReader();
                        while (rdr.Read()) // skips the read
                        {
                            retu = rdr[0].ToString();
                            retu = rdr["ReturnMessage"].ToString();
                            Console.WriteLine("return is " + retu);
                            break;
                        }

                }


                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                }

                finally
                {
                    //if (conn != null)
                    //{
                    //    conn.Close();
                    //}
                    //if (rdr != null)
                    //{
                    //    rdr.Close();
                    //}
                }
            }

            public static void Main(string[] args)
            {
                Console.WriteLine("Start Program");


                Console.WriteLine(" Enter the Custonmer ID ");
                custid = Console.ReadLine();

                Console.WriteLine(" Enter the Company Name ");
                companyname  = Console.ReadLine();

              
                Program p = new Program();
                p.RunStoredProc();
                Console.Read();
            }
        }
    }

    The this is the stored procedure:

    USE [Northwind]
    GO
    /****** Object:  StoredProcedure [dbo].[InsertCustomer]    Script Date: 08/17/2012 15:27:48 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[InsertCustomer]
        @CustomerID nvarchar(15) = NULL,
     @CompanyName nvarchar(15) = NULL,
     @ReturnCode int = NULL OUTPUT,
     @ReturnMessage nvarchar(255) = NULL OUTPUT
    AS
     SET NOCOUNT ON

     -- Declare some working variables
     DECLARE @Err int;
     DECLARE @Rows int;
     DECLARE @identity int;

     -- Exit if CategoryName is null.
     IF @CustomerID IS NULL
      BEGIN
       SELECT @ReturnCode = 515,
       @ReturnMessage = 'Null CustomerID not allowed.'
       RETURN
      END

     -- Exit if CategoryName already exists.
     IF EXISTS (SELECT CustomerID FROM dbo.Customers 
      WHERE CustomerID=@CustomerID)
      BEGIN
       SELECT @ReturnCode = 2601,
       @ReturnMessage = 'Name already exists.'
       RETURN
      END

     -- Perform the transaction
     BEGIN TRANSACTION
     INSERT INTO dbo.Customers (CustomerID,CompanyName)
      VALUES (@CustomerID,@CompanyName);

     -- Pick up @@ERROR and @@ROWCOUNT
     SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT

     -- If there's an error, rollback.
     IF @Err <> 0
      BEGIN
       SELECT @ReturnCode=@Err,
        @ReturnMessage= 'Transaction rolled back.'
       ROLLBACK TRANSACTION
       RETURN
      END

     -- If no rows inserted, rollback.
     IF @Rows = 0
      BEGIN
       SELECT @ReturnCode=2601,
        @ReturnMessage='Insert failed.'
       ROLLBACK TRANSACTION
       RETURN
      END

     -- Success! Commit the transaction.
     ELSE
      BEGIN
       COMMIT TRANSACTION
       SET @identity=SCOPE_IDENTITY();
       SELECT @ReturnCode=0, --@CustomerID=@identity,
        @ReturnMessage='Transaction succeeded, row added.'
       RETURN
      END

    The insert to the table is working but thr resuls are not coming back to the calling program.

    Friday, August 17, 2012 7:36 PM

Answers

  • Hi Steve,

    Welcome to the MSDN Forum.

    Please try to change the following things:

    1. Set the Direction property of myParm3 and myParm4 to Output.

    2. Because you just use "Return" rather than "return blablabla" in the store procedure, I suggest you change the statement 

    rdr = cmd.ExecuteReader();

    To 

    cmd.EndExecuteNonQuery();

    3. After above code, check the parameters' value, they should contains the return values.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Mike Feng Thursday, September 6, 2012 3:19 AM
    Monday, August 20, 2012 9:17 AM