Click here to Skip to main content
13,593,290 members
Rate this:
 
Please Sign up or sign in to vote.
See more: , +
when i run this Stored-Procedures from vb.net it run but not work properly.
i want
1st ---- delete the all data against the 'Saleinvid'
2nd ---- insert data against the 'Saleinvid'
But it delete all data and insert only single data(one) which is entry at last in list view in vb.net application
please help me....

What I have tried:

ALTER PROC P_UpdateSaleInv
@saleinvid	nvarchar(255)	,
@slno	int	,
@ItemIDsale	int	,
@SDQty	int	,
@SDMRP	decimal(18, 2)	,
@SDRate	decimal(18, 2)	,
@SDdis	decimal(18, 2)	,
@Sdcgst	decimal(18, 2)	,
@Scgstamt	decimal(18, 2)	,
@Sdsgst	decimal(18, 2)	,
@Ssgstamt	decimal(18, 2)	,
@Sdigst	decimal(18, 2)	,
@Sigstamt	decimal(18, 2)	,
@STaxableAmt	decimal(18, 2)	,
@Stotaltaxamt	decimal(18, 2)	,
@SDAmount	decimal(18, 2)	,
@S_type	varchar(2)	,
@S_ItemHSNid	int,	
@SaleInvdt	date

as 
BEGIN  
 
begin    
delete from TBL_sale_invdet where saleinvid=@saleinvid
end    

begin

insert into TBL_sale_invdet(saleinvid,slno,ItemIDsale,SDQty,SDMRP,SDRate,SDdis,Sdcgst,Scgstamt,Sdsgst,Ssgstamt,Sdigst,Sigstamt,STaxableAmt,Stotaltaxamt,SDAmount,S_type,S_ItemHSNid,SaleInvdt)                  
values(@saleinvid,@slno,@ItemIDsale,@SDQty,@SDMRP,@SDRate,@SDdis,@Sdcgst,@Scgstamt,@Sdsgst,@Ssgstamt,@Sdigst,@Sigstamt,@STaxableAmt,@Stotaltaxamt,@SDAmount,@S_type,@S_ItemHSNid,@SaleInvdt)
end 
end
Posted 19-Feb-18 8:59am
Updated 19-Feb-18 10:51am
Comments
PIEBALDconsult 19-Feb-18 15:57pm
   
A) I strongly recommend against using string for IDs.
B) If it's an update, then why not use UPDATE?
Jayanta Modak 20-Feb-18 2:29am
   
TBL_sale_invdet this table i used for entry the product details of sale invoice. when i edit the sale invoice change the product details (delete, add, change etc.) then only UPDATE is not complete the total work. that for I used the DELETE, for all data against "saleinvid" from this table TBL_sale_invdet. Then i re **INSERT** the product details.
please help me.........
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

You code does indeed delete every record from TBL_sale_invdet where any record has the saleinvid of @saleinvid. It then inserts a single record with the same specified @saleinvid.

What did you think was going to happen?

If every record in your TBL_sale_invdet has the same saleinvid value, every one of those records is going to be deleted. It would probably be a good idea to inspect the contents of the table before running your code.

The problem doesn't appear to be the SQL but with your understanding of what's going on and the data your supplying it. This is there the debugger comes in handy. It's there to debug YOU and your understanding of the code.
  Permalink  
Comments
Jayanta Modak 20-Feb-18 2:28am
   
TBL_sale_invdet this table i used for entry the product details of sale invoice. when i edit the sale invoice change the product details (delete, add, change etc.) then only UPDATE is not complete the total work. that for I used the DELETE, for all data against "saleinvid" from this table TBL_sale_invdet. Then i re INSERT the product details.
please help me.........
Dave Kreskowiak 20-Feb-18 10:11am
   
Ummm... what?

Why are you deleting all of the records for a certain invoice and then reinserting them? That's .... stupid. Delete the records you don't need, update the records that are edited, and insert the new records for the invoice.

The SQL you posted will never do what you want. It can only delete all records for an invoice and insert a single record. Your have to separate these operations into different SP's or just put the SQL in your application code. You cannot get away with what you're doing right now.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

HI, you need to create separate procedure to delete the record and then insert the records.
Explanation:


BEGIN  
 
begin    -----If u insert the second record from the code it will delete the previous inserted record
delete from TBL_sale_invdet where saleinvid=@saleinvid
end    

begin

insert into TBL_sale_invdet(saleinvid,slno,ItemIDsale,SDQty,SDMRP,SDRate,SDdis,Sdcgst,Scgstamt,Sdsgst,Ssgstamt,Sdigst,Sigstamt,STaxableAmt,Stotaltaxamt,SDAmount,S_type,S_ItemHSNid,SaleInvdt)                  
values(@saleinvid,@slno,@ItemIDsale,@SDQty,@SDMRP,@SDRate,@SDdis,@Sdcgst,@Scgstamt,@Sdsgst,@Ssgstamt,@Sdigst,@Sigstamt,@STaxableAmt,@Stotaltaxamt,@SDAmount,@S_type,@S_ItemHSNid,@SaleInvdt)
end 
end

Solution:
1. Create the Delete sproc and call from code to delete the record. and then create in sert record to for the invoice id.
  Permalink  
Comments
CHill60 20-Feb-18 3:23am
   
"If u insert the second record from the code it will delete the previous inserted record" ?? Why would inserting a record delete another? There is absolutely no need to have a separate procedure to delete and insert
khanabrar 20-Feb-18 3:30am
   
Hi, For Example if you have to insert below scenario:
Insert 3 records with sale ID 2000. the code will run 3 times to insert the three records.
Let see the problem.
Loop 1. delete the record for sales ID 2000 and insert (0 record deleted, 1 record inserted).
Loop 2 . delete the record for sales ID 2000 and insert (1 record deleted, 1 record inserted).
Loop 3. delete the record for sales ID 2000 and insert (1 record deleted, 1 record inserted).
Finale result is you will get only One record saved in the db and others are deleted.
Now what i told to use two separate Code for deletion and insertion.
1. First called the delete sproc and delete the record with the sales ID.
2. Call insert sproc and insert all the data with loop.
CHill60 20-Feb-18 6:58am
   
I was being sarcastic. There is no need for separate procedures. The OP needs to understand what they are doing versus what they think they need to do.
Jayanta Modak 20-Feb-18 4:21am
   
Sir please help me...
how to solve the problem in a single procedure
please help me....
khanabrar 20-Feb-18 5:10am
   
can u share the code when u r calling the sproc
Jayanta Modak 20-Feb-18 5:28am
   
ok sir, but now i am in office, i share the code after some time sorry sir please forgive me...
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I suspect all of your records have the same value for saleinvid.
  Permalink  
Comments
Jayanta Modak 20-Feb-18 2:28am
   
TBL_sale_invdet this table i used for entry the product details of sale invoice. when i edit the sale invoice change the product details (delete, add, change etc.) then only UPDATE is not complete the total work. that for I used the DELETE, for all data against "saleinvid" from this table TBL_sale_invdet. Then i re **INSERT** the product details.
please help me.........

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.180618.1 | Last Updated 20 Feb 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100