Why I can only execute a stored procedures two times?

Question

In my VB.NET code I construct a ODBC.COMMAND to call a stored procedure that eliminates a record from table in SQLSERVER 2008, I use this code in a FOR NEXT loop, it may contain 1 to 20 records_ID to pass to stored procedures to delete the records.

This code work well for several months, but since the last compilation it only deletes the first 2 records.

In debug mode in VS 2008 IDE works fine.

VB.NET CODE

    ''' <summary>
    ''' DELETE CANCELED CUSTOMER ORDERS
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub tsbDiarioVenta_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbDiarioVenta.Click

        On Error GoTo Proc_Err

        Using frmrslt As New BuscarForm()

            frmrslt.Titulo = "Elimina Diarios de Venta en Contabilidad"
            frmrslt.TextFieldID = "Cg_MovCab_ID"

            frmrslt.Sql = " SELECT  TIPO_COMP, NUM_COMP, FEC_DOC, COD_AUX, COD_REF, NUM_REF, STATUS, Cg_MovCab_ID " & _
                        " FROM Cg_MovCab " & _
                        " WHERE COD_EMPR = " & DataShared.gintCODEMPR & _
                        " AND YEAR(FEC_DOC) = " & Me.txtPER_ANUAL.Text & _
                        " AND MONTH(FEC_DOC) = " & Me.txtPER_MENSUAL.Text & _
                        " AND COD_REF IN ('FAC', 'NV') " & _
                        " ORDER BY 1,2 "

            frmrslt.ShowDialog()

            If frmrslt.Selected Then

                Debug.Print(frmrslt.CountRows.ToString)

                If frmrslt.CountRows > 0 Then

                    Cursor.Current = Cursors.WaitCursor

                    frmrslt.StartRow()
                    Dim idx As Integer
                    For idx = 0 To frmrslt.CountRows

                        Debug.Print(frmrslt.KeyValueID)
                        ClsCgMovs.DeleteID(frmrslt.KeyValueID)
                        frmrslt.NextRow()


                    Next

                    MsgBox(frmrslt.CountRows.ToString & " Regs. Eliminados", MsgBoxStyle.Information, "Mensaje del Sistema")

                End If

            End If

        End Using

        Cursor.Current = Cursors.Default
        Exit Sub

Proc_Err:
        Cursor.Current = Cursors.Default
        ErrHnd(Err.Number, Err.Description, ONDEBUG, "Frm_VtActLote: tsbDiarioVenta_Click: ", Erl)
    End Sub



    Public Function DeleteID(ByVal PRowID As Integer) As Boolean

        On Error GoTo Proc_Err

        Using odbcconn As New OdbcConnection(DataShared.gstrCNN)

            odbcconn.Open()
            Dim OdbcCmd As New OdbcCommand( _
                "{ ?=CALL proc_Cg_MovCab_DeleteID(" & PRowID & ") }", odbcconn)

            OdbcCmd.Parameters.Add("@return", OdbcType.Int)
            OdbcCmd.Parameters("@return").Direction = ParameterDirection.ReturnValue

            OdbcCmd.ExecuteNonQuery()
            If CInt(OdbcCmd.Parameters("@return").Value) = 0 Then
                DeleteID = True
            Else
                DeleteID = False
            End If

        End Using

        Exit Function

Proc_Err:
        ErrHnd(Err.Number, Err.Description, ONDEBUG, "ClsCgMovs: DeleteID: ")
        DeleteID = False
    End Function




ALTER PROCEDURE [dbo].[proc_Cg_MovCab_DeleteID] 
(
    @ID int
)

AS
    SET NOCOUNT ON

    DECLARE @errornum int

    BEGIN TRAN

    DELETE  FROM Cg_MovCab
    WHERE Cg_MovCab_ID = @ID

    SET @errornum = @@ERROR
    IF @errornum <> 0 GOTO PROC_ERR

    COMMIT TRAN

    RETURN @errornum

PROC_ERR:
    ROLLBACK TRAN
    RETURN @errornum

Answer 1

If your DELETE depends on a VIEW you could try to drop and recreate the relevant VIEW. If the underlying tables change, VIEWS happen to return wrong values (i.e. switching columns and such).

Just takein a shot...

EDIT:

atPS:

AFAIR changeing the table structure (adding / removing fields) caused this issue on production system of mine. SQL 2005 btw.

Answer 2

It could be that you're running out of available connections, try calling odbcconn.Close() before you Exit Function. This could explain why it worked before, if the allowed number of connections were modified or reset via an update, or someone tinkering.