SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 issue with update query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

svibuk
Yak Posting Veteran

60 Posts

Posted - 10/06/2012 :  04:51:47  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000