asp.net - SQL Server Stored Procedure Returns All Result properly for otherdays except the Starting day -

asp.net - SQL Server Stored Procedure Returns All Result properly for otherdays except the Starting day -

i facing problem while trying info database & displaying in gridview. in front-end have page search study particular day. code working days except 1st day. 1st day not displaying values.1st day means, suppose entered opening balance entry on 01/06/2014 in opening balance table . & in search page want see study 22/06/2014 working fine. if want see study of 01/06/2014 not showing result.

from stored procedure getting study info have written :

alter procedure [dbo].[usp_getdatailsfordailyreport] @vdate varchar(50) --declare @vprevdate varchar(50) begin --set @vprevdate=(select convert(varchar(50),dateadd(dd, datediff(dd, 0, convert(datetime,'19/06/2014',103)), -1),103)) set nocount on; -- insert statements procedure here select prevopening.schoolid, schooldetails.schoolname, schooldetails.schoolcode, schooldetails.sansadno, schooldetails.circle, isnull(noofstudents.totalnoofstudents,0) noofstudents, isnull(prevopening.recrice,0)-isnull(prevconsumption.conrice,0) openingrice, isnull(receiving.currecrice,0) recvrice, isnull(consumption.curconrice,0) conrice, (((isnull(prevopening.recrice,0)-isnull(prevconsumption.conrice,0))+ isnull(receiving.currecrice,0))- isnull(consumption.curconrice,0)) closingrice, isnull(prevopening.recamount,0)-isnull(prevconsumption.conamount,0) openingamount, isnull(receiving.currecamount,0) recvamount, isnull(consumption.curconamount,0) conamount, (((isnull(prevopening.recamount,0)-isnull(prevconsumption.conamount,0))+ isnull(receiving.currecamount,0))- isnull(consumption.curconamount,0)) closingamount ( select schoolid, isnull (sum(isnull(rice,0)),0) recrice, isnull (sum(isnull(amount,0)),0) recamount tblopeningbalance date<convert(datetime,@vdate,103) grouping schoolid ) prevopening left bring together ( select schoolid, isnull (sum(isnull(rice,0)),0) conrice, isnull (sum(isnull(amount,0)),0) conamount --sum(noofstudent) prevtotalnoofstudents tbldailymiddaydetails date<convert(datetime,@vdate,103) grouping schoolid ) prevconsumption on prevopening.schoolid=prevconsumption.schoolid left bring together ( select schoolid, isnull (sum(isnull(rice,0)),0) currecrice, isnull (sum(isnull(amount,0)),0)as currecamount tblopeningbalance date=convert(datetime,@vdate,103) grouping schoolid ) receiving on prevopening.schoolid=receiving.schoolid left bring together ( select schoolid, isnull (sum(isnull(rice,0)),0) curconrice, isnull (sum(isnull(amount,0)),0) curconamount --isnull (sum(isnull(noofstudent,0)),0) totalnoofstudents tbldailymiddaydetails date=convert(datetime,@vdate,103) grouping schoolid ) consumption on prevopening.schoolid=consumption.schoolid left bring together ( select schoolid, schoolname, schoolcode, sansadno, circle tblschooldetails ) schooldetails on prevopening.schoolid=schooldetails.schoolid left bring together ( select schoolid, sum(noofstudent) totalnoofstudents tbldailymiddaydetails date=convert(datetime,@vdate,103) grouping schoolid ) noofstudents on prevopening.schoolid=noofstudents.schoolid end

now stored procedure inserting in openingbalance table :

alter procedure [dbo].[usp_insertopeningbalance] ( @vopeningbalanceid int ,@vdate nvarchar(10) ,@vschoolid int ,@vrice nvarchar(30) ,@vamount nvarchar(30) ,@returnval int output ) declare @dupcount int begin set nocount on; if (@vopeningbalanceid=0) begin set @dupcount=(select count(schoolid) [dbo].[tblopeningbalance] convert(varchar(10),date,103)=convert (varchar(10),@vdate,103) , schoolid=@vschoolid) if (@dupcount>0) begin set @returnval=-70 end else begin insert [dbo].[tblopeningbalance] ( date ,schoolid ,rice ,amount ) values ( convert(datetime, @vdate, 103) ,@vschoolid ,convert(float,@vrice) ,convert(float,@vamount) ) set @returnval=1 end end else begin update [dbo].[tblopeningbalance] set date= convert(datetime, @vdate, 103) ,schoolid=@vschoolid ,rice=convert(float,@vrice) ,amount=convert(float,@vamount) openingbalanceid=@vopeningbalanceid set @returnval=2 end end

this stored procedure insertion of daily midday meal details daily consumption in midday meal table.

alter procedure [dbo].[usp_insertdailymiddaydetails] -- add together parameters stored procedure here ( @vschoolid int ,@vdate nvarchar(20) ,@vnoofstudent int ,@vrice nvarchar(30) ,@vamount nvarchar(30) ,@vvisittypeid int ,@returnval int output ) declare @i_count int begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; begin set @i_count=(select count(id) tbldailymiddaydetails ltrim(rtrim(date))=ltrim(rtrim(@vdate)) ); if (@i_count >0) begin set @returnval = -70; end else begin insert tbldailymiddaydetails ( schoolid ,date ,noofstudent ,rice ,amount ,visittypeid ) values ( @vschoolid ,convert(datetime,@vdate,103) ,@vnoofstudent ,convert(float,@vrice) ,convert(float,@vamount) ,@vvisittypeid ) set @returnval=1 end end end

can guide me wrong code?

you have several different variable types date.

@vdate varchar(50) @vdate nvarchar(10) @vdate nvarchar(20)

why difference?

why not store as

date

asp.net sql sql-server-2008