The stored procedure doesn't work fine when inserted condition for date

if you are using datetimepicker in you code then,

pass parameter to procedure as :::dateTimePicker1.Value.ToLongDateString()

Hi Nel :)

Can you upload a file containing:

• Create Script of the Tables and stored procedure.
• Sample data ( 4 -10 rows, each table) which can fit in your scenario.
• Detail, what do you need exactly with an suitable example.

I tried like this
exec presmetajSaldo
@idki=2,
@data='07/12/2012',
@saldo=4

as well, and the result is the same:(. Nothing changed

run this

exec presmetajSaldo
@idki=2,
@data='07/12/2012',
@saldo=4

I changed the datetime format as you wrote, but again I get the same message. Nothing changed.

Running
exec presmetajSaldo
@idki=2,
@data='12.07.2012',
@saldo=4

I get "0 rows affected" without row

select saldo from Kasovizvestaj

Ok, change date format of your system as

Format English(Unites States)
Short Date M/d/yyyy
Long Date dddd, MMMM dd,yyyy

After changing this, try to execute your SP.

what is the datetime format of your computer?

Hi Pradip,
I tried with your suggestion, but

exec presmetajSaldo
@idki=1,
@data='2012.07.12',
@saldo=45

throws a message "0 rows affected"

Jignesh, I tried this, and the

exec presmetajSaldo
@idki=1,
@data='2012-07-12',
@saldo=45

returns a message: "0 rows affected" again:(

hi nel,

replace following line with update statement of your sp..
are you use sql server 2008?

update Kasovizvestaj
set saldo=@saldo
where cast(data as date) =  cast(@data as date)

please make sure when you pass from C# application you must truncate time from datetime format
let us know the result.

Hi Nel
try this..

ALTER procedure [dbo].[presmetajSaldo]
(
@idki int,
@data varchar(12),
@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=cast(@data as varchar(12)) and vp>=0 and vp<=10

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

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

update Kasovizvestaj

set 
 idki=@idki,
 saldo=@saldo
 where data=cast(@data  as varchar(12))

select saldo from Kasovizvestaj
return

Hi Jignesh, I tried that and again the same message :(

"0 rows affected".

sure Nel,

Try following change

ALter procedure [dbo].[presmetajSaldo]
(
@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
 saldo=@saldo
 where CONVERT(VARCHAR(10),data,104) =  CONVERT(VARCHAR(10),@data,104)
return

run this code...
exec presmetajSaldo
@idki=1,
@data='2012-12-07',
@saldo=66

hi Nel,

Try with date which present in you db and also change style of date like 'yyyy-mm-dd' instead of 'yyyy.mm.dd'

hope this time it will work.

Jignesh I tried that and I got a message
"(0 row(s) affected)"

Here is the create table script:

USE [Bl]
GO

/****** Object:  Table [dbo].[Kasovizvestaj]    Script Date: 07/31/2012 14:24:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Kasovizvestaj](
 [idki] [int] NOT NULL,
 [data] [datetime] NULL,
 [saldo] [decimal](11, 3) NULL
) ON [PRIMARY]

GO

hi Nel,

I tried self with sp and with out sp bot are working..

check below code in sql server. is it work?

Declare @data DateTime
Declare @saldo numeric(12,3)
set @data= '2012.07.12'
set @saldo = 5000
update Kasovizvestaj
set
saldo=@saldo
where data  = @data

also are you use any spacial COLLATE in SQL Server?

can you please share create table script which is generated form SQL server?
thx

Hi Pradip,

I tried with

exec presmetajSaldo
@idki=1,
@data='07.12.2012',
@saldo=45

but I got "0 rows affected":(

Hi Jignesh,

This didn't help.When I excevute the sp

exec presmetajSaldo

@idki=1,
@data='2012.07.12',
@saldo=45

I get "0 rows affected."

hi Nel,

Rewrite your update statement...

update Kasovizvestaj set saldo=@saldo
 where data=@data and idki=@idki

I assume that there is unique value in idki.

can please provide details of input i mean to say parameter value when SP called?

thx.

Created a table, inserted records based on the data given by you and result look like this

1    2012-07-11 00:00:00.000    45000.000
2    2012-07-12 00:00:00.000    45000.000
3    2012-07-20 00:00:00.000    45000.000
So I found that this problem is related with date format, I mean to say that while you execute SP like this
exec [presmetajSaldo] 2,'12/07/2012',0
It gives an error but in case of this
exec [presmetajSaldo] 2,'07/12/2012',0
runs fine

So, check your date format while you are passing as a parameter to execute SP.

Hope it will help.

Column name  Data type         allow nulls

idki                  int                  Unchecked
data                datetime          Checked
saldo              decimal(11, 3)  Checked

1	11.07.2012 00:00:00	45,000
2	12.07.2012 00:00:00	45,000
3	20.07.2012 00:00:00	45,000

 and it should be

1	11.07.2012 00:00:00	45,000
2	12.07.2012 00:00:00	399,400
3	20.07.2012 00:00:00	2098,000

Can you send the exact table structure and the records of the table.

No, it didn't unfortunately:(. I get the same result, as the previous (first) entered value for saldo in the table.

Hi Nel
  You should cast the date where you are using in update statement i.e

update Kasovizvestaj

set 
 idki=@idki,
 saldo=@saldo
 where data=Cast(@data as varchar(12))

Hope it will help.