5
Reply

Calling a stored procedure from c# method

Mario Cuervo

Mario Cuervo

Aug 20 2014 2:38 PM
1.1k
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??  
 
 

Answers (5)