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

SQL Server - displaying PK errors

Hello,

I am writing a ASP.NET page for the user to create some new records for a particular process that happens.  If they attempt to create records for a record set that has already gone through the process the ASP page gives an ugly error message about not being able to see the error message due to security reasons.

I do the same thing on the local server and i get the following primary key violation error message...

"Violation of PRIMARY KEY constraint 'PK_Process'. Cannot insert duplicate key in object 'Process'. The statement has been terminated. "

This is correct, and what i intended the database to do but i would like a nicer looking way to display the error message for the end user.

Thanks!

-Navicerts
0
Navicerts
Asked:
Navicerts
5 Solutions
 
strickddCommented:
If you control the server, then you should put up custom error pages. You could also write and ErrorHandler class for your project. Have a method that you call from the Global.asax in the OnApplicationError. this method should handle the error how ever you want.  Still, you should not "expect" and error, you should prevent this error from happening.
0
 
NavicertsAuthor Commented:
The only way i could prevent the error from happening is to change the text box variables to drop down list variables, only displaying the avalible ones.  The drop down list and actually get a bit long so i was going with the text box's.

With custom errors, it will still go to a seprate page where the user is going to have to hit "back" and try to submit again.  this is a bit ugly.  Perhaps i can handle this by putting the SQL statements in a stored proceedure and making some type of "If" statement?

I'll look into the options you wrote here though, thank you so far.

-Navicerts
0
 
strickddCommented:
What you can do is a custom validator on the page. Have it call a function that you write in the Code-Behind that will verify that the textbox/drop down list is correct.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Carl TawnSystems and Integration DeveloperCommented:
Or if you wanted to handle the Exception locally you can just catch SqlException and display a message. Something like:

        SqlConnection cn = new SqlConnection("Connection string");
        SqlCommand cmd = new SqlCommand("INSERT INTO Table_1 (ID, Name) VALUES (1, '" + TextBox1.Text + "')", cn);

        cn.Open();

        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            if (ex.Message.Contains("Violation of PRIMARY KEY constraint"))
                Response.Write("A record with the specified ID already exists.");
        }
        finally
        {
            // Make sure connection is closed whatever happens
            cn.Close();
        }
0
 
raterusCommented:
I'd definitely go for the dropdown option, or maybe even pop up a window that allows them to make a selection.  Users never do what they are supposed to do with your application.  I treat my users like lemmings..(like the game), that is--dumb, stupid creatures that WILL walk right off a cliff and tell their buddies to do the same.  You must create your application with great validation as to prevent this and only allow users to go in the path you want them to.

As for your problem, I think it can be fixed by using the validator controls.  You set up how a form should be entered, and you can easily check before you ever run an insert statement, to see if the user has complied.  if you want to keep the textbox entry, fine...  But I'd have maybe a custom validator that checked what they entered to what they should have entered, and pop up an error message accordingly.  This should happen way before the SQL insert statement is ran.
0
 
NavicertsAuthor Commented:
Ok, i have written custom validators before but none that evaluate info returned from a query, just ones that validate date's to make sure they are dates, text box's to make sure they are filled in, excetra.  I'll give this a shot and post back.  Thank You.

-Navicerts
0
 
strickddCommented:
Custom Validators are pretty easy because you can do any code you want in one. That way you can do IF's and FOR's and all that good stuff to make sure the input is valid.
0
 
NavicertsAuthor Commented:
Just an update, I am changing the page to accept values from drop down lists instead of text box's.  If i narrow down the avalible choices based off current date the list in the drop down menu goes to a managable size.  This is my first time making a page in Visual Studio .NET as opposed to ASP Matrix so evreything is a bit of a challenge.

After i get the drop down lists working ill work on that validation control and get back to you guys.  Thanks again for your help.

-Navicerts
0
 
NavicertsAuthor Commented:
Once i had pull down's instead of text box's i was able to narrow down the record set so that it should be impossible to select something that will not work throught my stored proceedures.  I guess im all set with custom validation controls that pass a value for now, did it through SQL.

Thanks all for the help.

-Navicerts
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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