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?
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?
"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?
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
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
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.
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.
tottenham_ru...
Member
11 Points
87 Posts
Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.Award...
Jun 02, 2010 05:05 PM|LINK
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 ClassHow 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
Draak
Participant
816 Points
126 Posts
Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.A...
Jun 02, 2010 05:51 PM|LINK
This article explains error handling
http://msdn.microsoft.com/en-us/library/5b2yeyab(VS.71).aspx
Antony Highsky About Me
hoodedperson...
Star
12950 Points
3196 Posts
Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.A...
Jun 02, 2010 05:55 PM|LINK
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?
tottenham_ru...
Member
11 Points
87 Posts
Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.A...
Jun 02, 2010 05:56 PM|LINK
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
tottenham_ru...
Member
11 Points
87 Posts
Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.A...
Jun 02, 2010 06:03 PM|LINK
"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?
Draak
Participant
816 Points
126 Posts
Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.A...
Jun 02, 2010 06:12 PM|LINK
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 SubIf 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 TryAntony Highsky About Me
rsw20
Member
548 Points
112 Posts
Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.A...
Jun 02, 2010 06:12 PM|LINK
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 functionhoodedperson...
Star
12950 Points
3196 Posts
Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.A...
Jun 02, 2010 06:13 PM|LINK
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 UsingSo 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?
Draak
Participant
816 Points
126 Posts
Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.A...
Jun 02, 2010 06:17 PM|LINK
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
Antony Highsky About Me
tottenham_ru...
Member
11 Points
87 Posts
Re: Violation of PRIMARY KEY constraint 'PK_Awards'. Cannot insert duplicate key in object 'dbo.A...
Jun 02, 2010 06:19 PM|LINK
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