sp for saving data
IF EXISTS (SELECT billno,p.pid , p.pname as PRODUCT_NAME, QTY, RATE ,AMOUNT from trans t
join M_prdt P On P.pid = t.pid WHERE cid= @pcid and [Month] = @pmonth
and [year] = @pyear and p.pid=@ppid )
UPDATE trans set pid=@ppid ,qty=@pqty ,rate=@prate ,AMOUNT=@pamt ,billno=@pbillno ,frmdt=@pfrmdt ,todt=@ptodt
WHERE cid= @pcid and [Month] =@pmonth and [year] = @pyear and pid=@ppid and billno=@pbillno
Else
insert into trans(tid,cid,pid,qty,rate,AMOUNT,[month],[year],billno,frmdt,todt)
values (@pmaxid, @pcid ,@ppid,@pqty,@prate ,@pamt,@pmonth,@pyear,@pbillno,@pfrmdt,@ptodt)
code for populating grid with data
IF EXISTS(select * from trans WHERE cid= " & drpcust.SelectedValue & " )" _
& " (SELECT distinct p.pid , p.pname as PRODUCT_NAME, QTY, RATE ,AMOUNT, t.frmdt as From_DT, t.todt as To_DT from trans t " _
& " join M_prdt P On P.pid = t.pid " _
& " WHERE cid= " & drpcust.SelectedValue & " and [Month] = " & DateTimePicker1.Value.Month & " " _
& " and [year] = " & DateTimePicker1.Value.Year & “)” _
& " Else " _
& " (select DISTINCT p.pid , p.pname as PRODUCT_NAME,0 as QTY, p.PRATE as RATE, t.AMOUNT, frmdt as From_DT,todt as To_DT" _
& " from PRDT_TAKEN t " _
& " left join M_customer C On C.Cid = t.cust_id " _
& " left join M_prdt P On P.pid = t.pid left join trans a On a.pid = t.pid " _
& " where t.cust_id = " & drpcust.SelectedValue & “)”
the grid is binded with data from a tbl having fields items, qty, rate, amt , fromdate , todate
initally when the grid is loaded with default date values which is 01-01-1900 i get one item only in each row
suppose i hve 3 rows of data like
item qty rate amt fromdt todt
A 5 10 50 1/1/1900 1/1/1900
B 2 5 10 1/1/1900 1/1/1900
c 1 5 5 1./1/1900 1/1/1900
suppose i update the date of item c as 5/1/2011 - 5/1/2012
the data gets saved but whe i reload the same record
i get the above 3 rows as it is but i also get an additonal row with update date as
c 1 5 5 5/1/2011 5/1/2012
for other customers it shld be the default date only
he cust id record in which i had updated the date displays a single , i mean the updated date only . in this i dont get the reocrd with 01-01-1900 fr tht particular item
but other custid having this item i get 2 rows , one with the updated date & other with 1/1/1900