Calling a stored procedure from c# method

Mario Cuervo

12y1.7k

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)

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??

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramesh Maruthi12y ago
now I understood your problem, once go and check the permission on the table which your updating
Mario Cuervo12y ago
Hi Ramesh, I followed your suggestion:
using:
cmd.Parameters.Add(new SqlParameter("@LoanID", LoanNo));
instead of:
cmd.Parameters.Add("@LoanID", SqlDbType.NVarChar).Value = LoanNo;

for every parameter value assignment, but still don't get any update committed.

Dipankar: You are pointing at a parameter of the method I created but the real assignment to the sql Command is made inside the method.

Abhay: The problem I'm facing is that the updates are not committed when I called the SP from c#, even when the parameter values passed are right and the SP executes fine and the updates commit when executed outside the c# environment.
Abhay Shanker12y ago
what's the error, you are getting?
Ramesh Maruthi12y ago
Try to mention in this way

cmd.Parameters.Add(new SqlParameter("@LoanID", LoanNo));
Dipankar Biswas12y ago
UPDATE dbo.Loans
SET
OriginalAmount = @OriginalAmount,
ContractDate = @ContractDate,
MaturityDate = @MaturityDate,
InterestRate = @InterestRate
WHERE (YearMonth = @YearMonth) and (LoanID = @LoanID)
END

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

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;

I don't know but , i think prb hr..........