Calling a stored procedure from c# method

Question

Hi there,

I am using VS 10, C# and SQL server 2008.

 

I created the following stored procedure to update a table:

 

ALTER PROCEDURE dbo.AddDetailToLoans

(@LoanID nvarchar(15), @YearMonth nvarchar(10), @OriginalAmount Real, @ContractDate DateTime,

@MaturityDate DateTime, @InterestRate Real)

 

AS

if exists(SELECT LoanID from dbo.Loans WHERE (YearMonth = @YearMonth) and (LoanID = @LoanID))

BEGIN

 UPDATE dbo.Loans

  SET

   OriginalAmount = @OriginalAmount,

   ContractDate = @ContractDate,

   MaturityDate = @MaturityDate,

   InterestRate = @InterestRate

 WHERE (YearMonth = @YearMonth) and (LoanID = @LoanID)

END

 

 

Which works just fine. I executed it and the updates are committed as expected.

 

So I added the following method to a form to execute the above SP:

 

 

private void AddDetailstoMyLoans(string LoanNo, string DataDateST, float LoanAmnt, DateTime ContractDate,

                                                      DateTime MaturityDate, float InterestRate)

{

   try

   {

      var connectionString = MyUtilities.GetConnectionStringByName(MyConnStrName);

      using (SqlConnection con = new SqlConnection(connectionString))

      {

         using (SqlCommand cmd = new SqlCommand("AddDetailToLoans", con))

         {

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@LoanID", SqlDbType.VarChar).Value = LoanNo;

            cmd.Parameters.Add("@YearMonth", SqlDbType.VarChar).Value = DataDateST;

            cmd.Parameters.Add("@OriginalAmount", SqlDbType.Real).Value = LoanAmnt;

            cmd.Parameters.Add("@ContractDate", SqlDbType.DateTime).Value = ContractDate;

            cmd.Parameters.Add("@MaturityDate", SqlDbType.DateTime).Value = MaturityDate;

            cmd.Parameters.Add("@InterestRate", SqlDbType.Real).Value = InterestRate;

            con.Open();

            cmd.ExecuteNonQuery();

            return;

         }

      }

   }

   catch (Exception exceptionMessage)

   {

      MessageBox.Show("The Loans table was not be updated. There has been an error: "

      + exceptionMessage.ToString());

   }

}

 

 

and the updates are not committed, even though the parameters passed are correct... What am I doing wrong??