Strange Error: SQL Error -2146232060

Hi Experts,
I am trying to do a bulk insert that is triggering an error:  -2146232060
When I have run the stored procedure in ssms it completes without error, but when I run my application, it inserts the first record but then breaks. I am not getting any help from the debugger or I simply don't know where to look for help.
I am including what I am trying to work with in both sql and vb.
Thanks for your help.
VB CODE:
Protected Sub UpdateAttendanceButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles UpdateAttendanceButton.Click
        'The connection variables
        Dim strConn As String = ConfigurationManager.AppSettings("SiteSqlServer")
        Dim objConn As New SqlConnection(strConn)
        Dim cmd As New SqlCommand

        'The popup notifier
        Dim cs As ClientScriptManager = Page.ClientScript


        Try
            If AttendanceDateTextBox.Text = "" Then
                AttendanceDateInfoLabel.Text = "Enter an Attendance Date."
                AttendanceDateInfoLabel.ForeColor = Drawing.Color.Red
                Exit Sub
            End If

            For Each row As GridViewRow In RegisteredPlayerGridView.Rows
                'The Attendance data to be collected
                Dim RegID As Label = CType(row.FindControl("RegIDLabel"), Label)
                Dim MarkAtten As CheckBox = CType(row.FindControl("MarkAttendanceCheckbox"), CheckBox)
                Dim AttType As New SqlParameter("@AttendanceType", SqlDbType.NVarChar)
                If MarkAtten.Checked = True Then
                    AttType.Value = "Attended"
                ElseIf MarkAtten.Checked = False Then
                    AttType.Value = "Absent"
                End If
                Dim ec As New SqlParameter("@ERRORCODEOUT", SqlDbType.Int)
                ec.Direction = ParameterDirection.Output

                'Open Connection
                cmd.CommandText = "dbo.sj_MarkAttendance"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Connection = objConn
                'insert the attendance info
                cmd.Parameters.AddWithValue("@RegID", RegID.Text)
                cmd.Parameters.AddWithValue("@AttendanceDate", AttendanceDateTextBox.Text)
                cmd.Parameters.Add(AttType)
                cmd.Parameters.Add(ec)
                objConn.Open()
                cmd.ExecuteNonQuery()
                If Not IsDBNull(ec.Value) Then
                    If CType(ec.Value, Integer) = 2627 Then
                        cs.RegisterStartupScript(Me.GetType, "zomaar", "alert('One or more members of this family already has a profile.');", True)
                        objConn.Close()
                        Exit Sub
                    End If
                End If
                objConn.Close()

            Next
            cs.RegisterStartupScript(Me.GetType, "zomaar", "alert('The Class Attendance has been recorded.');", True)
        Catch se As SqlException
            InsertUpdateSuccessLabel.Text = se.ErrorCode.ToString
            Exit Sub
        Catch ex As Exception
            InsertUpdateSuccessLabel.Text = ex.ToString
        End Try



    End Sub

Open in new window

SQL CODE:
ALTER PROCEDURE [dbo].[MarkAttendance]
	@RegID int
	,@AttendanceDate date
	,@AttendanceType nvarchar(20)
	,@ERRORCODEOUT int OUTPUT
	
	AS
BEGIN TRANSACTION
		BEGIN TRY
INSERT INTO [dbo].[ProgramAttendance]
           ([RegID]
           ,[AttendanceDate]
           ,[AttendanceType])
     VALUES
           (@RegID, @AttendanceDate, @AttendanceType)
END TRY


BEGIN CATCH
	 SET @ERRORCODEOUT = ERROR_NUMBER()
     IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

END CATCH; 

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

Open in new window

wdarnellgAsked:
Who is Participating?
 
Paul JacksonSoftware EngineerCommented:
try opening and closing the connection outside of the for loop :

objConn.Open()
For Each row As GridViewRow In RegisteredPlayerGridView.Rows
....
....
Next
objConn.Close()
0
 
käµfm³d 👽Commented:
You're not clearing the Parameters collection in each loop iteration. So for loop 1, your Command object has 4 parameters; for loop 2, it has 8 parameters; etc.
0
 
wdarnellgAuthor Commented:
Thanks jacko72 and kaufmed. I just applied both suggestions and everything works great, and since I have to get to an appointment, I am just going to split the points. Both made sense to me. I hope you guys are ok with that.
Thanks so much for the quick response and accurate help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.