Hi,
how do i determine whether a INSERT/UPDATE/DELETE stored procedure is successed/failured.
if for example, I have the following stored proc here: -
create procedure sp_add_employee
-
@userid int,
-
@password varchar(20)
-
as
-
insert into employee values(@userid,@password)
-
How do I know whether the insert is successful?
Please advise.
Thanks
9 1137 Plater 7,872
Recognized Expert Expert
For that Insert statement, if it failed, an Exception would have been thrown.
Updates and Deletes are trickier
If you had for example -
UPDATE employee
-
SET Password=@password
-
WHERE
-
UserID= @userid
-
You could modify it do say this: -
SELECT @AffectedRows=count(*)
-
FROM employee
-
WHERE
-
UserID= @userid
-
-
UPDATE employee
-
SET Password=@password
-
WHERE
-
UserID= @userid
-
-
return @AffectedRows
-
That should tell you how many rows were effected by the UPDATE command (0 rows, 1 row, 36 rows, whatever the case may be)
You can do similar for the DELETE command
You need to add error handling to your stored procedure. Check out this link for more information. Pay particular attention to the @@ERROR system function.
Nathan
Dear Friend,
I will suggest you to write the query in sql server itself and run at the same time, it will let you know the error if you passing through, and i think just copy the codes written in the fronthand and just paste to the sql server and just, follow the errors and you would like to learn the more as you indulje to write more queries there..
Regds
Aziz
Dear Friend,
I will suggest you to write the query in sql server itself and run at the same time, it will let you know the error if you passing through, and i think just copy the codes written in the fronthand and just paste to the sql server and just, follow the errors and you would like to learn the more as you indulje to write more queries there..
Regds
Aziz
I always do like what Aziz suggest.
hi,
thanks for the reply.
just now i was practicing using output parameter from StoredProcedure.
i have the following StoredProcedure -
create procedure sp_get_supplier_code
-
(
-
@supplier_code char(3) output
-
)
-
as
-
set nocount on
-
set @supplier_code = 'SP'
-
then i try to access it by creating a SqlConnection and a SqlCommand and setup the output parameter, then i execute it using sqlCmd.executeNonQuery(), then i try to access the output parameter by using the following code -
MsgBox(sqlCmd.Parameters("@supplied_code").Value)
-
but i only get an "S" from the messagebox, it seems that it only return the first character of 'SP'. is there something wrong?
Thank you
Plater 7,872
Recognized Expert Expert
try varchar instead of char ?
hello,
thanks for the reply. i've changed it to varchar, but still i only get an 'S'.
Thank you
Plater 7,872
Recognized Expert Expert
How did you define the @supplied_code in code side, as what data type?
i define it as SqlDbType.VarChar
here's the code that define it -
MainForm.dbCon.addOutputParameter("@supplier_code_output", "", SqlDbType.VarChar)
-
(in the example above i've changed the parameter name to @supplier_code_output).
in the program i have a class which encapsulate a DBConnection to the SQL Server 2K.
here's the definition of the addOutputParameter method -
Public Sub addOutputParameter(ByVal key As String, ByVal value As String, _
-
ByVal type As SqlDbType)
-
Dim sqlParam As New SqlParameter()
-
-
sqlParam.ParameterName = key
-
sqlParam.Value = value
-
sqlParam.SqlDbType = type
-
sqlParam.Direction = ParameterDirection.Output
-
-
sqlParams.Add(sqlParam)
-
-
'register it to sqlOutput, so it's available to outside class
-
sqlOutput.Add(key, value)
-
End Sub
-
Thank you.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: audipen |
last post by:
Hi,
I trap the 'OnBuildProjConfigDone' and perform some custom enhancement
to the assembly. I check the 'bool Success' parameter before I perform
my step. I go ahead only if 'Success == true"
...
|
by: MLH |
last post by:
I have been using variations of the following to determine whether
a functional connection to internet exists. I'm thinking of replacing
it with a simple PING, but I've never seen any code for...
|
by: spam.noam |
last post by:
Hello,
What is the convention for writing C functions which don't return a
value, but can fail?
If I understand correctly,
1. PyArg_ParseTuple returns 0 on failure and 1 on success.
2....
|
by: Marc DVer |
last post by:
I have a stored procedure that has a simple insert statement. I want to
be able to tell if the insert statement executed successfully, and
return using a tinyint whether the statement was...
|
by: kathy |
last post by:
what is the better way to determine if a string is number or not?
| |
by: lovecreatesbeauty |
last post by:
Hello experts,
I write a function named palindrome to determine if a character string
is palindromic, and test it with some example strings. Is it suitable
to add it to a company/project library...
|
by: Joris De Groote |
last post by:
Hi,
I'm looking for a javascript that can give me the screensize of a computer.
Can anyone help me?
Thanks
|
by: magix |
last post by:
Hi,
when I read entries in file i.e text file, how can I determine the first
line and the last line ?
I know the first line of entry can be filtered using counter, but how about
the last line...
|
by: Christopher Corbell |
last post by:
This question pertains to PyUnit, esp. unittest.TestCase subclasses.
Does anyone know of a way from within the TestCase tearDown() method
to determine whether the current test succeeded or not?
...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |