Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Hi guys, I'm getting this error code in a voting system which I made.

"Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated."

It's good because it means I am stopping people from voting twice, however, I want to display an error message in the site, and I want to make this occurance user friendly, as I have no idea how this error will look when the website is live.

Here is all of my code:

Imports System.Data.Sql
Imports System.Data.SqlClient


Partial Class Vote
    Inherits System.Web.UI.Page


    Protected Sub PostAward_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles PostAward.Click
        Dim currentUser As MembershipUser = Membership.GetUser()
        Dim currentUserId As Guid = CType(currentUser.ProviderUserKey, Guid)


        Dim connectionString As String = ConfigurationManager.ConnectionStrings("SecurityTutorialsConnectionString").ConnectionString
        Dim insertSql As String = "INSERT INTO Awards (Award1, Award2, Award3, Award4, Award5, Award6, Award7, Award8, UserId) VALUES (@Award1, @Award2, @Award3, @Award4, @Award5, @Award6, @Award7, @Award8, @UserId)"




        Using myConnection As New SqlConnection(connectionString)
            myConnection.Open()
            Dim myCommand As New SqlCommand(insertSql, myConnection)
            myCommand.Parameters.AddWithValue("@Award1", Award1.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award2", Award2.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award3", Award3.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award4", Award4.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award5", Award5.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award6", Award6.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award7", Award7.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award8", Award8.Text.Trim())
            myCommand.Parameters.AddWithValue("@UserId", currentUserId)
            myCommand.ExecuteNonQuery() <----- THIS IS WHERE THE ERROR POINTS WHEN DEBUGGING IS ON
            myConnection.Close()
        End Using


        Response.Redirect("Default.aspx")


    End Sub
End Class

How will this error look when the website is live? How can I display a message to warn the user that he has already voted?

Thanks,

Mike

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

This article explains error handling

http://msdn.microsoft.com/en-us/library/5b2yeyab(VS.71).aspx

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Try a "Try"/"Catch" block. Put everything in your "using" area inside the "Try", and inside the "Catch ex As Exception", put your code for letting the user know they already voted. Make sense?

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Thanks for the reply Draak, but I couldn't really find what I was looking for. I'm an amateur by the way, so it could be staring me in the face.

Thanks,

Mike

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

"Try a "Try"/"Catch" block. Put everything in your "using" area inside the "Try", and inside the "Catch ex As Exception", put your code for letting the user know they already voted. Make sense?"

That sort of makes sense. Could you explain how to construct it and why, so I can learn?

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Here's the general syntax:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As _
System.EventArgs) Handles Button1.Click
   Try
      Process.Start("http://www.microsoft.com")
   Catch ex As Exception
      MsgBox("Can't load Web page" & vbCrLf & ex.Message)
   End Try
End Sub

If you are making a web app, instead of MsgBox just drop a Label on your page, and set it's Text property to a string that summarizes what the error is about in user-friendly terms. So, you'd have something like this:

Catch ex As Exception
      Label1.Text = "You can't vote twice"
End Try

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

why allow exception to occur, when you can avoid it?

Check if this userid already has vote in current table and then throw or show nice message on page saying

'You cannot vote more than once!"

Protected Sub PostAward_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles PostAward.Click
         Dim currentUser As MembershipUser = Membership.GetUser()
         Dim currentUserId As Guid = CType(currentUser.ProviderUserKey, Guid)
   

         Dim connectionString As String = ConfigurationManager.ConnectionStrings("SecurityTutorialsConnectionString").ConnectionString
         Dim insertSql As String = "INSERT INTO Awards (Award1, Award2, Award3, Award4, Award5, Award6, Award7, Award8, UserId) VALUES (@Award1, @Award2, @Award3, @Award4, @Award5, @Award6, @Award7, @Award8, @UserId)"
   

   	try
	   Using myConnection As New SqlConnection(connectionString)
             myConnection.Open()


	     'Check if user has already voted
             if hasvoted then
               lblmessage ="You cannot vote more than once!"
                exit sub
             end
             Dim myCommand As New SqlCommand(insertSql, myConnection)
             myCommand.Parameters.AddWithValue("@Award1", Award1.Text.Trim())
             myCommand.Parameters.AddWithValue("@Award2", Award2.Text.Trim())
             myCommand.Parameters.AddWithValue("@Award3", Award3.Text.Trim())
             myCommand.Parameters.AddWithValue("@Award4", Award4.Text.Trim())
             myCommand.Parameters.AddWithValue("@Award5", Award5.Text.Trim())
             myCommand.Parameters.AddWithValue("@Award6", Award6.Text.Trim())
             myCommand.Parameters.AddWithValue("@Award7", Award7.Text.Trim())
             myCommand.Parameters.AddWithValue("@Award8", Award8.Text.Trim())
             myCommand.Parameters.AddWithValue("@UserId", currentUserId)
             myCommand.ExecuteNonQuery() <----- THIS IS WHERE THE ERROR POINTS WHEN DEBUGGING IS ON
             myConnection.Close()
         End Using
	 Response.Redirect("Default.aspx")
	catch
	  throw ex;
	

	end try
   

     End Sub
  

   function hasvoted() as boolean
	'code to check if that user id already has record and return true or false
   end function

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Yes sir!

This is what it would look like with your code:

Using myConnection As New SqlConnection(connectionString)
    Dim myCommand As New SqlComand(insertSql, myConnection)
    'blah blah blah all the parameters junk


    Try
        myConnection.Open()
        myCommand.ExecuteNonQuery()
        myConnection.Close()
    Catch ex As Exception
        ' display message
    End Try


End Using

So I rearranged a little, but it's probably better to put it in this order (it would all execute the same).

Okay, so the point of try/catch is obviously to catch exception. The program "tries" to execute all code in the "Try" part, and anything that goes wrong in the system throws an exception. If an exception is thrown at any point during the code in the "Try" part, then the "Catch" part is executed and the exception is stored in ex. Usually, ex is converted to a string and displayed in the "Catch" part to tell the user what the problem is. You don't see exceptions unless you do a try/catch and show the exception, but things just don't work right and you won't know without this.

So in your new code's case, the exception can be anything about myConnection.Open() failing, myCommand.ExecuteNonQuery() failing, or myConnection.Close() failing (probably one of the first two). So I put myConnection.Open() inside in case something happens with the connection. But then there's no way to know which is the error from - connection or command...so if you want, put .Open() before Try and .Close() after End Try. You can do this because I guess we're really only worried about the ExecuteNonQuery working or not.

SO, if an exception is thrown (ExecuteNonQuery) fails in some way, then the Catch part executes, and this is where you can put whatever you want to show the user they've already voted, or just redirect them, or whatever.

Does this make sense?

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

And if you want to filter only for SQL-related errors, just replace Exception with SqlException in your Catch block. This way you'll be sure that your error message actually matches the error.

http://msdn.microsoft.com/en-us/library/686y883x(v=VS.100).aspx

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Wow thanks, Draak, rsw20 and hoodedperson 70, I appreciate the effort you have put in!

I actually understand this now, thanks for the extended explaination guys.

I suppose my site is now complete!! Does anyone know any good UK hosts for asp.net?

Thanks again,

Mike

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Uk hosting site

http://www.pixelinternet.co.uk/ref/wizhosting/9b855f76.php

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

You're welcome. Please remember to mark replies as answers if they help. Thanks.

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Congrats! Good to hear. I can't say I know any since I'm in the States, but let us know if you need anything else

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

hooded, where you've written "display message" can I just put "lblmessage ="You cannot vote more than once!"" or "Label1.Text = "You can't vote twice"" like what Draak and rsw20 said?

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Yep, that's exactly what I meant.

And did you put the .Open() & .Close() outside of the whole Try block? I think you should

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

that is a typo use lblmessage.text or labelname.text

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Protected Sub PostAward_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles PostAward.Click
        Dim currentUser As MembershipUser = Membership.GetUser()
        Dim currentUserId As Guid = CType(currentUser.ProviderUserKey, Guid)


        Dim connectionString As String = ConfigurationManager.ConnectionStrings("SecurityTutorialsConnectionString").ConnectionString
        Dim insertSql As String = "INSERT INTO Awards (Award1, Award2, Award3, Award4, Award5, Award6, Award7, Award8, UserId) VALUES (@Award1, @Award2, @Award3, @Award4, @Award5, @Award6, @Award7, @Award8, @UserId)"




        Using myConnection As New SqlConnection(connectionString)
            Dim myCommand As New SqlCommand(insertSql, myConnection)
            myCommand.Parameters.AddWithValue("@Award1", Award1.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award2", Award2.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award3", Award3.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award4", Award4.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award5", Award5.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award6", Award6.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award7", Award7.Text.Trim())
            myCommand.Parameters.AddWithValue("@Award8", Award8.Text.Trim())
            myCommand.Parameters.AddWithValue("@UserId", currentUserId)


            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()
            Catch ex As Exception


            End Try
            lblmessage = "You cannot vote more than once!"
        End Using


        Response.Redirect("Default.aspx")


    End Sub

Do you mean like that?

For some reason, I don't see the message, it just sends me back to default.aspx

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Real fast - put myConnection.Open() before the "Try", and put myConnection.Close() right before "End Using".

You need to put lblmessage="You cannot vote more than once!" on the line right after "Catch ex As Exception" (and before "End Try").

But quick question - do you want the page to always redirect to Default.aspx? Or only if successful vote? Or after so long the error message is displayed?

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

Catch ex As exception
 lblMessage.Text = "Message" 'use .text to display
end try

move lblMessage before end try and after catch

If you write code this way you will always display "You cannot vote more than once" for other exceptions/errors also.
So i would suggest you to avoid coding in that fashion.

Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Awards'. The statement has been terminated.

rsw20

move lblMessage before end try and after catch
<div class=dp-highlighter> <div class=bar> <div class=tools>view plaincopy to clipboardprint?</div></div>

Catch ex As exception
lblMessage.Text = "Message" 'use .text to display
end try<BR><BR>If you write code this way you will always display "You cannot vote more than once" for other exceptions/errors also. <BR>So i would suggest you to avoid coding in that fashion.<BR>
</div>
Catch ex As exception
 lblMessage.Text = "Message" 'use .text to display
end try



If you write code this way you will always display "You cannot vote more than once" for other exceptions/errors also. 

So i would suggest you to avoid coding in that fashion

Oops, yeah use lblMessage.Text

As for displaying the message for other exceptions...it'll only display an exception if ExecuteNonQuery fails, right? (if .Open() & .Close() are taken out of the Try block)