violating the primary key in the database

[Howlleo]

Perfectly.
Thanks.
I'm running out of time, so I'm posting this question too even though I'm not sure you can help me with it.

I am now receiving an error about violating the primary key in the database. The primary key is a combination of SectionID, StudentID, Semester, and Year, to ensure that nobody can enroll in two classes at the same time.

I am not trying to enroll anyone in the same Section of the same semester.
I've tried INSERT through SQL Management Server Express Studio (without parameters, obviously, but similar values) and get no error. So I am tentatively assuming the problem is in the website code again.

Appreciate any input, even just ideas. Need this uploaded before midnight.

Sub WriteClasses(ByVal CreditsArray)
        Dim i As Integer
        Dim EnrollDb As SqlConnection
        Dim cmdEnroll As SqlCommand
        EnrollDb = New SqlConnection("Server=LONNA\SQLEXPRESS;Integrated Security=True;database=LGordonTouroReg")
        cmdEnroll = New SqlCommand("INSERT INTO Enrollment (SectionID, Semester, Year, ClassID, StudentID) VALUES (@SectionID, 'Fall', '2007', @ClassID, @StudentID)", EnrollDb)
        cmdEnroll.Parameters.AddWithValue("@SectionID", CreditsArray(i, 2))
        cmdEnroll.Parameters.AddWithValue("@ClassID", CreditsArray(i, 0))
        cmdEnroll.Parameters.AddWithValue("@studentID", Profile.StudentID)
        EnrollDb.Open()
        For i = 0 To 12
            If Not CreditsArray(i, 0) = "" Then
                cmdEnroll.ExecuteNonQuery()
                Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>")
            End If
        Next i
        EnrollDb.Close()
    End Sub

ERROR MESSAGE:
Server Error in '/LGordonTouroReg' Application.
________________________________________
Violation of PRIMARY KEY constraint 'aaaaaEnrollment_PK'. Cannot insert duplicate key in object 'dbo.Enrollment'.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'aaaaaEnrollment_PK'. Cannot insert duplicate key in object 'dbo.Enrollment'.
The statement has been terminated.

Source Error:

Line 190: For i = 0 To 12
Line 191: If Not CreditsArray(i, 0) = "" Then
Line 192: cmdEnroll.ExecuteNonQuery()
Line 193: Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>")
Line 194: End If

Source File: C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx Line: 192

Stack Trace:

[SqlException (0x80131904): Violation of PRIMARY KEY constraint 'aaaaaEnrollment_PK'. Cannot insert duplicate key in object 'dbo.Enrollment'.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2411
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1038
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
ASP.registrationpage_aspx.WriteClasses(Object CreditsArray) in C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx:192
ASP.registrationpage_aspx.NoDouble(Object CreditsArray) in C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx:176
ASP.registrationpage_aspx.CheckPrereq(Object creditsArray) in C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx:153
ASP.registrationpage_aspx.RegisterButton_Click(Object sender, EventArgs e) in C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx:25
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4921

________________________________________
Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.833

 

[JohnH]

I moved this post to Data Access forum, here's link to OT if any of the data access dudes need it.

 

[Howlleo]

Appreciate it. If any further information about the database structure or web page is necessary, I will provide it.

 

[giadich]

Dim i As Integer
Dim EnrollDb As SqlConnection
Dim cmdEnroll As SqlCommand
EnrollDb = New SqlConnection("Server=LONNA\SQLEXPRESS;Integrated Security=True;database=LGordonTouroReg")
cmdEnroll = New SqlCommand("blah blah blah)
cmdEnroll.Parameters.AddWithValue("@SectionID", CreditsArray(i, 2))
cmdEnroll.Parameters.AddWithValue("@ClassID", CreditsArray(i, 0))
cmdEnroll.Parameters.AddWithValue("@studentID", Profile.StudentID)

"i" has not been initialized yet. did you mean to move it into the For loop?

        EnrollDb.Open()
        For i = 0 To 12
            If Not CreditsArray(i, 0) = "" Then
        cmdEnroll.Parameters.AddWithValue("@SectionID", CreditsArray(i, 2))
        cmdEnroll.Parameters.AddWithValue("@ClassID", CreditsArray(i, 0))
        cmdEnroll.Parameters.AddWithValue("@studentID", Profile.StudentID)
                cmdEnroll.ExecuteNonQuery()
                Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>")
            End If
        Next i

 

[Howlleo]

You guys all deserve to go straight to heaven! Thanks - it's working, and I might just pass this class.

 

[JohnH]

Don't AddWithValue in loop, it will add the parameter of same name multiple times and only the first parameter/value will be used for each db call.
Create command, add parameters, then loop where you set only different values for existing parameters.

 

[Howlleo]

Yeah,
After a premature celebratory lunch, I realized that it only worked for a single iteration.

But I'm not sure what you mean by

>then loop where you set only different values for existing parameters.<

Meaning I should change the values in the parameter.addwithvalue? How?

Thanks.

 

[JohnH]

You can't use AddWithValue method. You have to use the Add method. In loop you just get the parameter and set its Value property.

 

[Howlleo]

I'm sorry, I have no idea how to do that. Can you provide some dummy code or a link?

 

[Howlleo]

I'm trying... I tried

cmdenroll.parameters.add(New sqlparameter("@SectionID", creditsArray(i,2)))

with no success. I'm getting the same error message about how @sectionID is already declared.

Pleasepleaseplease help me someone - this is due in several hours and I have to upload it and get it working on the server before then.

 

[JohnH]

Parameter and value are two different things. To add a parameter to a command use one of the Add methods:

command.parameters.add("@parametername", SqlDbType.Int)

To set a value for an existing parameter use the Value property of the parameter:

command.parameters("@parametername").Value = 123

 

[Howlleo]

Just want to make sure I have this straight, because I'm off to take a final in a few minutes and then it's all night over the LCD:

Question: By command, do you mean specify cmdEnroll, or do I just type "command"?

And in this case, adding a value for a parameter is a two step process, whereby I first add the parameter to the command, and then assign the value.

Question: do I do both steps out of the loop, or do I do the first out of the loop and the second inside the loop?

Thanks tons.

 

[JohnH]

Create command, add parameters, then loop where you set only different values for existing parameters. Ring a bell?

 

[Howlleo]

It doesn't seem to work. I'm getting desperate.

Sub WriteClasses(ByVal CreditsArray(,) As Object)
    Dim EnrollDb As SqlConnection
    Dim cmdEnroll As SqlCommand
    EnrollDb = New SqlConnection("Server=LONNA\SQLEXPRESS;Integrated Security=True;database=LGordonTouroReg")
    cmdEnroll = New SqlCommand("INSERT INTO Enrollment (SectionID, Semester, Year, ClassID, StudentID) VALUES (@SectionID, 'Fall', '2007', @ClassID, @StudentID)", EnrollDb)
    EnrollDb.Open()
    cmdEnroll.Parameters.Add("@SectionID", SqlDbType.text)
    cmdEnroll.Parameters.Add("@ClassID", SqlDbType.text)
    cmdEnroll.Parameters.Add("@studentID", SqlDbType.text)
    
    For i As Integer = 0 To 12
      If Not CreditsArray(i, 0) = "" Then
        cmdEnroll.Parameters("@SectionID").Value = CreditsArray(i, 2)
        cmdEnroll.Parameters("@ClassID").Value = CreditsArray(i, 0)
cmdEnroll.Parameters("@studentID").Value = profile.studentID
        cmdEnroll.ExecuteNonQuery()
        'Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>")
      End If
    Next i
    EnrollDb.Close()
  End Sub

ERROR MESSAGE:

Violation of PRIMARY KEY constraint 'aaaaaEnrollment_PK'. Cannot insert duplicate key in object 'dbo.Enrollment'.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'aaaaaEnrollment_PK'. Cannot insert duplicate key in object 'dbo.Enrollment'.
The statement has been terminated.

Source Error:

Line 194: cmdEnroll.Parameters("@ClassID").Value = CreditsArray(i, 0)
Line 195: cmdEnroll.Parameters("@StudentID").Value = Profile.StudentID
Line 196: cmdEnroll.ExecuteNonQuery()
Line 197: Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>")
Line 198: End If

Source File: C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx Line: 196

Stack Trace:

[SqlException (0x80131904): Violation of PRIMARY KEY constraint 'aaaaaEnrollment_PK'. Cannot insert duplicate key in object 'dbo.Enrollment'.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2411
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1038
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
ASP.registrationpage_aspx.WriteClasses(Object[,] CreditsArray) in C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx:196
ASP.registrationpage_aspx.NoDouble(Object CreditsArray) in C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx:176
ASP.registrationpage_aspx.CheckPrereq(Object creditsArray) in C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx:153
ASP.registrationpage_aspx.RegisterButton_Click(Object sender, EventArgs e) in C:\Users\Lonna\Documents\Visual Studio 2005\WebSites\LGordonTouroReg\RegistrationPage.aspx:25
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4921

________________________________________
Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.833

 

[Arg81]

It might not be the issue, but you are creating a parameter as a text datatype, but then trying to assign the value(s) of an array.

A parameter can only have 1 value (AFAIK). When you loop through the array, is there only 1 row in the array? If there are more, then I think this could be your problem.

"I think"...!