Catching SQL Server Error Before It Happens: “Cannot insert duplicate key row in object 'dbo.tablename' with unique index 'idxName'.

Environment as follows:Language – VB.Net 2005Asp.net – 2.0Visual Studio 2005SQL Server 2005  I have an SQL formview (formview1) connected to an SQL Server data base.  There is an insert template in the formview which is used to insert new records to the sql server table.  The table has a strict no duplicates index consisting of the following fields:  year (int), mcu (nvarchar) and gmcatcd (nvarchar).  Formview1 has the following controls which correspond to the sql server data base table:  yearTextBox, mcuTextBox,gmcatcdTextBox.  

I have an existing sub for the insert button that looks like the following:

 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)        Try             If (FormView1.CurrentMode = FormViewMode.Insert) Then ' makes use of a private function which is already defined earlier                Dim strBranchPlant As String = CType(FormView1.FindControl("mcuTextBox"), TextBox).Text                Dim strCatCode As String = CType(FormView1.FindControl("gmCatCdTextBox"), TextBox).Text                Dim strCatDesc As String = CType(FormView1.FindControl("gmCatDscTextBox"), TextBox).Text                CType(FormView1.FindControl("mcuTextBox"), TextBox).Text = toupper(strBranchPlant)                CType(FormView1.FindControl("gmCatCdTextBox"), TextBox).Text = toupper(strCatCode)                CType(FormView1.FindControl("gmCatDscTextBox"), TextBox).Text = toupper(strCatDesc)                ' end call to private function              End If  End Sub Right now if the user enters a record that violates the duplicate key they get the very ugly SQL Server error:  “Cannot insert duplicate key row in object 'dbo.cmscatbg' with unique index 'idxCmsCatBg'.
The statement has been terminated.”
 What I need is to catch the SQL server error before it gets ugly.  Preferably this would present the user with a message that the record they are entering already exists and reset the field controls in the form back to blank. I would like to be able to use the language in the edititem template as well.   Thanks in advance …

Sad

0
officewonk
5/16/2008 6:03:24 PM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

2 Replies
1278 Views

Similar Articles

[PageSpeed] 6

You wont be able to catch a sql error before it happens. however you can wrap the call to the insert in a try catch and handle the exception and provide a user friendly message

try

'Method call to insert

catch ex as SQLException

'Show friendly message


Jeev
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you get the answer to your question, please mark it as the answer.
0
Jeev
5/16/2008 7:44:20 PM

Jeev, thanks for gettng back to me on this.  If you mean doing something like the following, it still returns the same ugly SQL error to the user.  OW 

Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Try

Catch ex As System.Data.SqlClient.SqlException

Response.Write("Record with matching key already exists")

End Try

 

End Sub

0
officewonk
5/20/2008 6:05:26 PM
Reply:

Similar Artilces:

Cannot search
I have multiple protals, on different servers and many of them have problems searching. I always get the following error...BTW this is a brand new install of DNN 3.2 and i am having the same problems. This is the exception i always get...I tried to re-index the search, it does succeed but i still can never perform a search :(Any help on this would be appreciated.TIA,Ahmed-----------------------------------------------------AssemblyVersion: -1Method: FileName: FileLineNumber: -1FileColumnNumber: -1PortalID: -1PortalName: UserID: -1UserName: ActiveTabID: -1ActiveTabName: AbsoluteURL: AbsoluteU...

DNN3012
I'm not actually using DNN's own search on this site and have hidden the search input box in the skin. I'm not aware of any problems but noticed many errors in DNN's error log like that below. Since I'm not using it, is there any advantage to 'turning off' DNN's search? and if so, how would I do this? ThanksLeslie   InnerException: Cannot insert duplicate key row in object 'SearchItem' with unique index 'IX_SearchItem'. The statement has been terminated.Message: System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'SearchItem' with unique index 'IX_SearchIt...

''''''''''''''''''''
Name: haznen Email: haznenatyahoodotcom Product: Gran Paradiso Alpha 8 Summary: '''''''''''''''''''' Comments: '''''''''''''''''''''''''''''''''''' Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 UGES/1.7.2.0 GranParadiso/3.0a1 From URL: http://www.mozilla.org/projects/granparadiso/ Note to readers: Hendrix gives...

'''''
Name: mario Email: ramar17atfastwebnetdotit Product: Gran Paradiso Alpha 2 Summary: ''''' Comments: K: Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a2) Gecko/20070206 GranParadiso/3.0a2 ...

Violation of PRIMARY KEY constraint 'PK_tblType'. Cannot insert duplicate key in object 'dbo.tblType'.
the point here that i have a small table with two fileds, ID (guid) as primerykey RAF(char) and the table is empty when i add a new row i recieve this exception, Violation of PRIMARY KEY constraint 'PK_tblType'. Cannot insert duplicate key in object 'dbo.tblType'. i found no way to solve the problem. thanks in advans  Muhanad YOUNISMCSD.NETMy Blog || My Photos || LinkedIn Apparently you are a value that already exists. How are you generating the GUID?***********************Dinakar NethiLife is short. Enjoy it.*********************** Yes, i think the problem is...

Error: Primary key for row in table 'x' is referenced by foreign key 'f' in table 'y'
(ASA 9 newest ebf) I'm getting this error even though mentioned foreign key has cascade updates (and deletes too) enabled. Is this some new behaviour in asa 9, or why can i get such message for cascaded foreign key? Any ideas? Thanks Pete Found a reason for it...but should it really work so? It's this way: - table a has columns a,b,c - table b has columns a,c, and references columns a,c (with a,c) in table a with cascade updates - table a hes before update trigger to change columns a when column b is changed (set new_row.a = ...) and that set new_row.a = ... i...

Key 'd' and Key 'Control'
Dear sirs / madams, I want to add a short cut key in my datawindow, the script as shown as below, Script 1 IF KeyFlags = 2 THEN IF KeyDown(Keyd!) THEN THIS.TriggerEvent("ue_deleterow") END IF END IF Script 2 IF KeyDown(KeyControl!) then IF KeyDown(Keyd!) then THIS.TriggerEvent("ue_deleterow") END IF END IF Script 3 IF KeyDown(KeyControl!) AND KeyDown(Keyd!) THEN THIS.TriggerEvent("ue_deleterow") END IF But, all of the above, if I press the Key 'Control' first, and then press Key 'd',...

'do' won't 'do' if '/'
Greetings to All from Au, Have a NetWare Perl 5.8.4 and wanted to tweak File\Spec\NW.pm to try and standardise on '/' separators. If I run a test script (t/uni/lower.t) with an unmodified NW.pm, it calls t/uni/case.pl, that, in case.pl, (when the path separators are '\'), the $file is '..\lib\unicore\To\Lower.pl' and the following code portion works, with $simple getting a returned table: sub casetest { my ($base, $spec, $func) = @_; my $file = File::Spec->catfile(File::Spec->catdir(File::Spec->updir, "lib", &qu...

'''
Name: L Lachowsky Email: e2brutus_10atyahoodotcom Product: Firefox Summary: ''' Comments: why duddn this surprise me... I change default server from IE to Mozilla, and then i get error messages. well, shud I decide to not get on my computer with Mozilla..or shud I risk security breeches with IE....hmmm at least i can get on the internet with IE Browser Details: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.5) Gecko/2008120122 Firefox/3.0.5 From URL: http://hendrix.mozilla.org/ Note to readers: Hendrix gives no expectation of a response to this fee...

''null' or not an object' error using validation controls in user control
Wonder if anybody has come across this before.I’m using Atlas together with some user controls I’ve created for user input that has validation controls on the forms.  If I remove the validation controls then all works fine… however once they are enabled I get a very annoying javascript error saying “ ‘null’ is null or not an object “ Where do I even start debugging this? If somebody knows what im doing wrong or how I could fix this please let me know.   Thanks ...

EXECUTE permission denied on object 'TransInsertCommand', database 'TSD_SQL', owner 'dbo'.
Hello, Any help on the above would be great.  I will give some more information on this.  I have two sets of Stored Procedures.  One works and this one does not.  They are inserting into two different tables.  The permissions are the same.  I am just wondering why one set of stored procedures would work and the other set does not.    Thanks,Nighthawk When you get this message, it just means that the user you're using to connect to the database does not have Execute permissions on that Stored procedure You'll need to grant that user execu...

Cast from type ''''DBNull'''' to type ''''String'''' is not valid.
How do I avoid getting this error: Cast from type ''''DBNull'''' to type ''''String'''' is not valid. I am trying to get values from the database into a form for updating using a Datareader, SQL is set to allow nulls for certain fields as they are not required but the only way I can get the form to display is by adding a space in SQL. Is there another way around this? Check if it is equal to DBNull.Value first and if it is, don't do the cast.Stanley Tan theSpoke Blog Where in the code does it need to go? I am using the following and it's the profile that isn't always requi...

SQL replace '-' by ''
Hi all i have a question regarding sql, i want to replace some characters...   any knows simply how to do this?   I want to replace "999-25000-69" by "9992500069"   grtzDon't forget to click "Mark as Answer" on the post that helped you. SELECT Replace('999-25000-69','-', '') or SELECT Replace(col1,'-', '') as col1 FROM yourTableLimno...

error in ''%'' operator
hi... friends    i got this error (Syntax error: Missing operand after ''%'' operator)below statement. dtBtnScr.DefaultView.RowFilter = "LOCATION_CITY'" + txtCity.Text.Trim() + "%'AND LOCATION_STATE '" + ddlState.SelectedValue + "%'AND UNIT_ID'" + txtUnitCode + "%'AND UNIT_NAME '" + txtUnitName.Text.Trim() + "%'";   thnax friends It thinks you are trying to use the modulo operator: %. Look at the RowFilter string after you build it ...you should see the prob...

Web resources about - Catching SQL Server Error Before It Happens: “Cannot insert duplicate key row in object 'dbo.tablename' with unique index 'idxName'. - asp.net.presentation-controls

Resources last updated: 12/8/2015 2:41:17 AM