The subject title pretty much sums it up. I have a few pages on my site where a user inserts new data. An example is where a new client is being entered in the system(clientID, firstname, lastname, etc.). I want to be able to display some error saying the
ID already exists, instead of getting the "unique constraint violated" error that usually displays.
You can write a function to capture common errors and substitue user friendly messages:
// ---- Format Exception Message ----------------------------
//
// Make common database exceptions friendlier.
static public String FormatExceptionMessage(Exception Exp)
{
String Message = "<b>ERROR: <b>";
SqlException SqlExp = Exp as SqlException;
if (SqlExp != null)
{
switch (SqlExp.Number)
{
case 515:
Message += "Null value, one or more mandatory fields are missing.";
break;
case 547:
Message += "That record has child records in another table. Can't delete it or orphan records will be created.";
break;
case 2601:
Message += "Duplicate key, a record with that value already exists.";
break;
case 8152:
Message += "Maximum length exceeded, the data is too large for the column.";
break;
default:
Message += SqlExp.Message;
break;
}
}
else
Message += Exp.Message;
return Message;
}
Message += "Null value, one or more mandatory fields are missing.";
break;
case 547:
Message += "That record has child records in another table. Can't delete it or orphan records will be created.";
break;
case 2601:
Message += "Duplicate key, a record with that value already exists.";
break;
case 8152:
Message += "Maximum length exceeded, the data is too large for the column.";
break;
default:
Message += SqlExp.Message;
break;
}
}
else
Message += Exp.Message;
return Message;
}
</div>
// ---- Format Exception Message ----------------------------
//
// Make common database exceptions friendlier.
static public String FormatExceptionMessage(Exception Exp)
{
String Message = "<b>ERROR: <b>";
SqlException SqlExp = Exp as SqlException;
if (SqlExp != null)
{
switch (SqlExp.Number)
{
case 515:
Message += "Null value, one or more mandatory fields are missing.";
break;
case 547:
Message += "That record has child records in another table. Can't delete it or orphan records will be created.";
break;
case 2601:
Message += "Duplicate key, a record with that value already exists.";
break;
case 8152:
Message += "Maximum length exceeded, the data is too large for the column.";
break;
default:
Message += SqlExp.Message;
break;
}
}
else
Message += Exp.Message;
return Message;
}
Hi,
Thanks for responding. I may give this method a try. Could you show this in VB?
another alternative is to return the message from the procedure so that you can update the messages or add new ones without changing the code.
make an output parameter in the procedure and based on the type of insert,
check for the count of the primary key without waiting for query to throw the error.
DECLARE @vCount Integer;
SELECT @vCount=COUNT(*) FROM TABLEA -- WHERE your conditions
now check for the count to insert or otherwise send out a custom message
you can do samething for others as well.
Hi,
Thanks for responding. I'm not sure if I follow you. I don't have any procedures in my codes. My parameters are in my INSERT statements. And due to the complexity of my database the INSERT statements are where the SELECT statements are normally found in
the sqldatasource.
What if I were to have a popup message displayed on the screen? I have tried the following the method:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If (GridView1.Rows.Count > 0) Then : MsgBox("ID already exists")
Else : MsgBox("Information inserted")
End If
End Sub
If there is no existing key, it will display "Informatin inserted", which I want; but if there is an existing key the same popup message is displayed and I am still directed to the page giving "unique constraint violated" error.
And giving more details, here is the code for my datasource:
phrozengoril...
Member
22 Points
97 Posts
display error if ID (PK) already exists when inserting new data
Jul 17, 2010 11:17 PM|LINK
Hello all,
The subject title pretty much sums it up. I have a few pages on my site where a user inserts new data. An example is where a new client is being entered in the system(clientID, firstname, lastname, etc.). I want to be able to display some error saying the ID already exists, instead of getting the "unique constraint violated" error that usually displays.
Thanks in advance for your suggestions.
SGWellens
All-Star
126033 Points
10311 Posts
Moderator
Re: display error if ID (PK) already exists when inserting new data
Jul 18, 2010 12:01 AM|LINK
You can write a function to capture common errors and substitue user friendly messages:
// ---- Format Exception Message ---------------------------- // // Make common database exceptions friendlier. static public String FormatExceptionMessage(Exception Exp) { String Message = "<b>ERROR: <b>"; SqlException SqlExp = Exp as SqlException; if (SqlExp != null) { switch (SqlExp.Number) { case 515: Message += "Null value, one or more mandatory fields are missing."; break; case 547: Message += "That record has child records in another table. Can't delete it or orphan records will be created."; break; case 2601: Message += "Duplicate key, a record with that value already exists."; break; case 8152: Message += "Maximum length exceeded, the data is too large for the column."; break; default: Message += SqlExp.Message; break; } } else Message += Exp.Message; return Message; }My blog
sansan
All-Star
53942 Points
8147 Posts
Re: display error if ID (PK) already exists when inserting new data
Jul 18, 2010 12:09 AM|LINK
another alternative is to return the message from the procedure so that you can update the messages or add new ones without changing the code.
make an output parameter in the procedure and based on the type of insert,
check for the count of the primary key without waiting for query to throw the error.
DECLARE @vCount Integer;
SELECT @vCount=COUNT(*) FROM TABLEA -- WHERE your conditions
now check for the count to insert or otherwise send out a custom message
you can do samething for others as well.
phrozengoril...
Member
22 Points
97 Posts
Re: display error if ID (PK) already exists when inserting new data
Jul 18, 2010 01:19 AM|LINK
Hi,
Thanks for responding. I may give this method a try. Could you show this in VB?
Thanks
phrozengoril...
Member
22 Points
97 Posts
Re: display error if ID (PK) already exists when inserting new data
Jul 18, 2010 01:24 AM|LINK
Hi,
Thanks for responding. I'm not sure if I follow you. I don't have any procedures in my codes. My parameters are in my INSERT statements. And due to the complexity of my database the INSERT statements are where the SELECT statements are normally found in the sqldatasource.
Thanks
phrozengoril...
Member
22 Points
97 Posts
Re: display error if ID (PK) already exists when inserting new data
Jul 18, 2010 01:31 AM|LINK
What if I were to have a popup message displayed on the screen? I have tried the following the method:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If (GridView1.Rows.Count > 0) Then : MsgBox("ID already exists")
Else : MsgBox("Information inserted")
End If
End Sub
If there is no existing key, it will display "Informatin inserted", which I want; but if there is an existing key the same popup message is displayed and I am still directed to the page giving "unique constraint violated" error.
And giving more details, here is the code for my datasource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="insert into branch values(?, location_type(?,?,?,?), staff_nested())">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="?" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox2" Name="?" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox3" Name="?" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox4" Name="?" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox5" Name="?" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
Thanks
SGWellens
All-Star
126033 Points
10311 Posts
Moderator
Re: display error if ID (PK) already exists when inserting new data
Jul 18, 2010 02:52 AM|LINK
There are many online converters to go between VB and C#. Google: Convert C# VB
You cannot use a MsgBox as you have done. Since the code is running at the server, that is where it will show up. Not at the client.
Hook into the Inserted event of the GridView. An exception will be passed in one of the parameters if there is an error.
My blog
phrozengoril...
Member
22 Points
97 Posts
Re: display error if ID (PK) already exists when inserting new data
Jul 18, 2010 03:07 AM|LINK
Ok. Thanks. But 2 questions: "Hook into the Inserted event"? Not following. What do you mean?
And I get an error: "Type 'SqlException' is not defined"
SGWellens
All-Star
126033 Points
10311 Posts
Moderator
Re: display error if ID (PK) already exists when inserting new data
Jul 18, 2010 11:01 AM|LINK
Controls have events that you can connect a function to.
You need to add the correct namespace: using System.Data.SqlClient;
My blog
phrozengoril...
Member
22 Points
97 Posts
Re: display error if ID (PK) already exists when inserting new data
Jul 18, 2010 01:55 PM|LINK
I meant hook what into the Inserted event?