Sql stored-procedures-DO-NOT work please help

Question

1.92/5 (3 votes)

See more:

SQL-Server-2008R2

, +

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:

SQL

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

Jayanta Modak

Updated 19-Feb-18 10:51am

Add a Solution

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.........

3 solutions

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.

Posted 19-Feb-18 9:30am

Dave Kreskowiak

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.

Solution 1

I suspect all of your records have the same value for saleinvid.

Posted 19-Feb-18 9:21am

#realJSOP

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.........

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

khanabrar · Accepted Answer · 2018-02-19T20:56:00

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.

Posted 19-Feb-18 20:56pm

khanabrar

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...