Nel

Nel

  • NA
  • 716
  • 1.1m

Problem with stored procedure

Jul 27 2012 6:32 AM
Hi,
I have a stored procedure which I call in my C# application. It should  store data in the data table.
But, everytime I call it, it changes the  previous entered data, not keep them as they were previously stored. It changes  the values of the columns (except the data column) as the value of the last  call.
Can Anybody help me please?
Here is the calling code of the  SP:

SqlCommand command4 = new SqlCommand("vnsaldo",  connection);
  command4.CommandType =  CommandType.StoredProcedure;
  command4.Parameters.AddWithValue("@data",  data);
  command4.Parameters.AddWithValue("@brkiz",  vkupnoKI);
  command4.Parameters.AddWithValue("@saldo", saldo1);
  int rowsAffected1 = command4.ExecuteNonQuery();
  if (rowsAffected1 >=  1)
  {
  result =  true;
  br = br +  1;
  }

For calculating saldo1 I use this previous code, which also calls a SP:

  SqlCommand command5 = new SqlCommand("prSaldo",  connection);
  command5.CommandType =  CommandType.StoredProcedure;
  command5.Parameters.AddWithValue("@idki",  posl_kas_izv);
  command5.Parameters.AddWithValue("@data", data);
  SqlParameter p = new  SqlParameter();
  p.ParameterName =  "@saldo";
  p.Direction =  ParameterDirection.InputOutput;
  p.SqlDbType = SqlDbType.Decimal;
  command5.Parameters.AddWithValue("@saldo",  saldo1);
  saldo1 = Convert.ToDecimal(command5.ExecuteScalar());

and for calculating vkupnoKI I also call a SP previously.

As a resul from the vnsaldo procedure I get
idki  data  saldo
3  20.07.2012  -52,000
4  27.07.2012  -52,000
3  11.07.2012  -52,000
3  12.07.2012  -52,000

and it should be:

idki  data  saldo
3  20.07.2012  2098,400
4  27.07.2012  2046,000
1  11.07.2012  45,000
2  12.07.2012  399,400

Also, I call the stored procedure prSaldo with @saldo parameter as input/output in the C# code, but I declare it as output in the SQL Server MAnagement studio, since I don't know how to call it as input/output.

Here is the SQL sp in SQL SERVER MANAGEMENT STUDIO:

create procedure [dbo].[prSaldo]
(
@idki int,
@data DateTime,
@saldo numeric(12,3) output

as

Declare @priem numeric(12,3)           

Declare @isplata numeric(12,3)
 

select @priem = sum(iznosden) from blagajna_jspturs
where data=@data and vp>=0 and vp<=10

select @isplata = sum(iznosden)
from blagajna_jspturs
where vp>10 and data=@data

set @saldo = isnull(@saldo,0) + (isnull(@priem,0)) - (isnull(@isplata,0))

update Kasovizvestaj

set 
 idki=@idki,
 saldo=@saldo

select saldo from Kasovizvestaj
return



Can anybody help me howto set it as in/out here? Or it is not a problem???

Thanks

Answers (6)