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
Deepak VermaPosted Jul 30, 2012, 1:27 AM
As I can see, you have written an UPDATE Query in the Stored Procedure without a WHERE CLAUSE. Thus, all rows in the table are updated. You must specify, which row to update. If you want to INSERT data into the table, avoid using Update query, use Insert instead.
tejal bhesaniyaPosted Jul 30, 2012, 7:35 AM
NelPosted Jul 30, 2012, 5:41 AM
I noticed when I put the condition
"where data=@data"
that the calculation is not correct. It remains on the same value as the previous record, not just taking the previous value for saldo to calculate it for the new date.
When I haven't put the condition for the data it calculated fine, but changes the previous values as well.
Now, I don't know why it remains on the previous value? Can anybody help me please?
Thanks
NelPosted Jul 30, 2012, 3:25 AM
Jignesh TrivediPosted Jul 30, 2012, 3:23 AM
Deepak / Tejal is right, there is missing of Where clause after update statement.
update Kasovizvestaj
set
idki=@idki,
saldo=@saldo
where data=@data
hope this will help you.
tejal bhesaniyaPosted Jul 30, 2012, 2:33 AM