One SP For Updating & Insertion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am using one SP to perform updation as well as insertion on a table in SQLSvr2000. But somehow, the control seems to be going to the Insertion part of the procedure. This is how i have defined the SP (icode is defined as identity, primary key)

CREATE PROCEDURE dbo.[UpdtMaster]
@whattodo int
@icode int
@iname nchar(100),
A
declare @trans_err in
if @whattodo =
begin
Update ItemMaste
Set Itemname = @iname,
where itemcode = @icod
end
els
begi
Insert into ItemMaste
(itemname)
values (@iname
end
G

In the corresponding vb.net snippet, i am doing a call as follows
MyDA.UpdateCommand.Parameter.Add("@whattodo", sqldbtype.int, 4).value =
 
Hi Sanjay,

How do you invoke sp?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Sanjay Agrawal said:
Hi,

I am using one SP to perform updation as well as insertion on a table in
Click to expand...
SQLSvr2000. But somehow, the control seems to be going to the Insertion part
of the procedure. This is how i have defined the SP (icode is defined as
identity, primary key) :
CREATE PROCEDURE dbo.[UpdtMaster]
@whattodo int,
@icode int,
@iname nchar(100),
AS
declare @trans_err int
if @whattodo = 0
begin
Update ItemMaster
Set Itemname = @iname,
where itemcode = @icode
end
else
begin
Insert into ItemMaster
(itemname)
values (@iname)
end
GO

In the corresponding vb.net snippet, i am doing a call as follows :
MyDA.UpdateCommand.Parameter.Add("@whattodo", sqldbtype.int, 4).value = 0
.
.
Despite hardcoding the "@whattodo" value to 0, when control reaches the
Click to expand...
SP, the condition seems to fail. Is there anything that i should be doing
further at this stage ?
 
Hello Miha,

myDA.UpdateCOmmand.Parameters.CommandText = "SP_UPDT"
myDA.UpdateCommand.Parameters.CommandType =
commandtype.storedprocedure
myDA.UpdateCommand.Parameters.Add("@whattodo", sqldbtype.int,
4).Value = 0
. 'code for @icode and @iname.
..
..

I changed the dbtype to bit and char as well, correspondingly changing
the @whattodo in the SP as well, but no dice.

Thanks for your time,

Regards,
Sanjay.
 
Hello Miha

I have two cases there - either it is a new entry made by the user - in which case i build up the myDA.INSERTCOMMAND and pass '1' to the whattodo parameter - or it is an updation situation, in which case i build up the myDA.UPDATECOMMAND and pass '0' to the whattodo parameter

Finally, I call the myDA.UPDATE(TBL) statement to proceed with the execution

In both the situations, the sp seems to go to the insert condition, as if the condition checking for the @whattodo parameter is always getting set to false for the update condition

Thanx for your time, Miha,

Regards

Sanjay
 
Back
Top