SQL Stored Procedures Editing

Question

I´m developing a database about the orders for my network with SQL and Visual Basic but I don´t see the results that i want, in visual basic I received an error this is my stored procedures

CREATE PROCEDURE modificar 
     @NumeroIP nvarchar(255), 
     @Usuario nvarchar(255),
     @DirecciónMAC nvarchar(255), 
     @MACRestricciones nvarchar(255), 
     @MACSinRestricciones nvarchar(255), 
     @Enmascarada nvarchar(255), 
     @SistemaOperativo nvarchar(255), 
     @DirecciónArea nvarchar(255), 
     @TipoEquipo nvarchar(255),
     @Observación nvarchar(255)
AS
    UPDATE dbo.IP
    SET Usuario = @Usuario, 
        DirecciónMAC = @DirecciónMAC, 
        MACRestricciones = @MACRestricciones, 
        MACSinRestricciones = @MACSinRestricciones, 
        Enmascarada = @Enmascarada,
        SistemaOperativo = @SistemaOperativo,
        DirecciónArea = @DirecciónArea, 
        TipoEquipo = @TipoEquipo,
        Observación = @Observación
    WHERE
        NumeroIP = @NumeroIP

    RAISERROR ('Valor modificado', 16, 1)

And my VB.NET is:

Dim conex As New SqlConnection("Data Source=GABRIELA;Initial Catalog=IPS_Database;Integrated Security=True")

Dim cmd As New SqlCommand("editar", conex)
cmd.CommandType = CommandType.StoredProcedure

conex.Open()

Dim para As New SqlParameter("@NumeroIP", SqlDbType.NVarChar)
para.Size = 255
para.Value = TextBoxDireccionIP.Text
cmd.Parameters.Add(para)

Dim da As New SqlDataAdapter
da.SelectCommand = cmd
da.SelectCommand.Connection = conex

Dim ds As New DataSet
da.Fill(ds, "IPS_DatabaseDataSet1")

DataGridView2.DataSource = ds.Tables("TablaIPS")

NumeroIPPTextBox.Clear()
UsuarioTextBox.Clear()
DireccionMACTextBox.Clear()
DireccionAreaTextBox.Clear()
ObservacionTextBox.Clear()
DireccionAreaTextBox.Clear()
ObservacionTextBox.Clear()

conex.Open()
cmd.ExecuteNonQuery()

Could you help me to run it?

You can use `RAISERROR ('Valor modificado', 0, 1) WITH NOWAIT` — Jim Hewitt, Jun 05 '16 at 02:37

score 3 · Answer 1 · answered May 31 '16 at 21:50

You will always receive an error in VB because with the RAISEERROR statement at the end of your procedure you are always returning an error.

From the documentation:

The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

You should consider switching to using PRINT instead of RAISERROR in your procedure.

score 0 · Answer 2 · edited May 23 '17 at 12:31

The way you have written your stored procedure it will always Raise an error.

Also please read this TSQL Try / Catch within Transaction or vice versa?, I explained in great detail how the TRY..CATCH block works and how you can appropriately do the error handling with it.

Your SP should be written like this...

CREATE PROCEDURE modificar 
  @NumeroIP nvarchar(255)
, @Usuario nvarchar(255)
, @DirecciónMAC nvarchar(255)
, @MACRestricciones nvarchar(255)
, @MACSinRestricciones nvarchar(255)
, @Enmascarada nvarchar(255)
, @SistemaOperativo nvarchar(255)
, @DirecciónArea nvarchar(255)
, @TipoEquipo nvarchar(255)
, @Observación nvarchar(255)
AS
BEGIN
  SET NOCOUNT ON;

 BEGIN TRY 
   BEGIN TRANSACTION; 
     update dbo.IP
       set  Usuario = @Usuario
          , DirecciónMAC = @DirecciónMAC
          , MACRestricciones = @MACRestricciones
          , MACSinRestricciones = @MACSinRestricciones
          , Enmascarada = @Enmascarada
          , SistemaOperativo = @SistemaOperativo
          , DirecciónArea = @DirecciónArea
          , TipoEquipo = @TipoEquipo
          , Observación = @Observación
    where NumeroIP = @NumeroIP
   COMMIT TRANSACTION;
 END TRY 

 BEGIN CATCH

  IF (@@TRANCOUNT <> 0)
   BEGIN
     ROLLBACK TRANSACTION;
   END

   raiserror ('Valor modificado',16,1)
 END CATCH 
END

SQL Stored Procedures Editing

2 Answers2