Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

svibuk
Yak Posting Veteran

62 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  
 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.02 seconds. Powered By: Snitz Forums 2000