• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3228
  • Last Modified:

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

0
wdarnellg
Asked:
wdarnellg
2 Solutions
 
Paul JacksonCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now