I read
"Error Handling in SQL Server - a Background",
http://www.algonet.se/~sommar/error-handling-I.html and
"Implementing Error Handling with Stored Procedures",
http://www.algonet.se/~sommar/error-handling-II.html
and made the following changes:
if I take out the try I still get the error and I know
how to find whats wrong.
Please hekp??
Stan
-----------------------------------------------------------
Public Function CSVUpdate(ByVal Name As String, _
ByVal code As String, _
ByVal Caption As String, _
ByVal Price As Integer, _
ByVal SalePrice As Integer, _
ByVal id As String, _
ByVal path As String)
' Create Instance of Connection and Command
Object
Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings
("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand
("Update_CSV", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType =
CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterName As SqlParameter = New
SqlParameter("@Name", SqlDbType.NVarChar, 255)
parameterName.Value = Name
myCommand.Parameters.Add(parameterName)
Dim parametercode As SqlParameter = New
SqlParameter("@code", SqlDbType.NVarChar, 255)
parametercode.Value = code
myCommand.Parameters.Add(parametercode)
Dim parameterCaption As SqlParameter = New
SqlParameter("@Caption", SqlDbType.NVarChar, 4000)
parameterCaption.Value = Caption
myCommand.Parameters.Add(parameterCaption)
Dim parameterPrice As SqlParameter = New
SqlParameter("@Price", SqlDbType.Int, 4)
parameterPrice.Value = Price
myCommand.Parameters.Add(parameterPrice)
Dim parameterSalePrice As SqlParameter = New
SqlParameter("@SalePrice", SqlDbType.Int, 4)
parameterSalePrice.Value = SalePrice
myCommand.Parameters.Add(parameterSalePrice)
Dim parameterid As SqlParameter = New
SqlParameter("@Id", SqlDbType.NVarChar, 255)
parameterid.Value = id
myCommand.Parameters.Add(parameterid)
Dim parameterpath As SqlParameter = New
SqlParameter("@path", SqlDbType.NVarChar, 255)
parameterpath.Value = path
myCommand.Parameters.Add(parameterpath)
Dim parametererr As SqlParameter = New
SqlParameter("@err", SqlDbType.Int, 4)
parametererr.Direction =
ParameterDirection.Output
myCommand.Parameters.Add(parametererr)
Try
' Open the connection and execute the
Command
myConnection.Open()
myCommand.ExecuteNonQuery()
Catch myException As SqlException
Dim errornumber As Integer = CInt
(parametererr.Value)
End Try
myConnection.Close()
-----------------------------------------------------------
-----------------
CREATE PROCEDURE Update_CSV
@Name nvarchar(255),
@code nvarchar(255),
@Caption nvarchar(4000),
@Price int,
@SalePrice int,
@id nvarchar(255),
@path nvarchar(255),
@err int output
as
SET NOCOUNT ON
DECLARE @CountItems int
SELECT
@CountItems = Count(*)
FROM CSV
WHERE code = @code
IF @CountItems > 0 /* There are items - update the
current quantity */
UPDATE CSV
SET Name = @Name, code = @code,Id = @id,
Caption = @Caption, Price = @Price, [Sale-Price] =
@SalePrice,
path = @path
WHERE code = @code
ELSE /* New entry for this Cart. Add a new record */
INSERT INTO CSV
(Name, code, Caption, Price, [Sale-
Price], path,id)
VALUES (@Name, @code, @Caption, @Price, @SalePrice,
@path,@id)
SELECT @err = @@error if @err <> 0 return @err
GO
---------------------------
