Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server - displaying PK errors

Posted on 2006-05-22
9
Medium Priority
?
308 Views
Last Modified: 2008-02-01
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
Comment
Question by:Navicerts
9 Comments
 
LVL 28

Assisted Solution

by:strickdd
strickdd earned 1000 total points
ID: 16734021
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
 
LVL 7

Author Comment

by:Navicerts
ID: 16734079
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
 
LVL 28

Assisted Solution

by:strickdd
strickdd earned 1000 total points
ID: 16734122
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 200 total points
ID: 16734138
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
 
LVL 33

Accepted Solution

by:
raterus earned 800 total points
ID: 16734145
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
 
LVL 7

Author Comment

by:Navicerts
ID: 16734149
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
 
LVL 28

Assisted Solution

by:strickdd
strickdd earned 1000 total points
ID: 16734170
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
 
LVL 7

Author Comment

by:Navicerts
ID: 16734833
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
 
LVL 7

Author Comment

by:Navicerts
ID: 16735885
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question