Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

If Primary Key ID already exists in datatable, how to not show error?

Posted on 2009-07-01
5
Medium Priority
?
776 Views
Last Modified: 2012-05-07
I have a datatable that contains three columns: ID, firstName, lastName. This table is populated by the user clicking a linkbutton within a datalist. The datatable contents are displayed in a different region on the same page via a repeater control. The user can remove items from the datatable by clicking a linkbutton within the repeater control. This all works great.

Since I don't want the user to add the same record (person) to the datatable, I've made the ID column of my datatable the PRIMARY KEY to prevent the same ID from being submitted. However, if a user attempts to do this anyway, as you might imagine, they get an ASP.NET server error "Column 'ID' is constrained to be unique".

Is there a way, when the user clicks the linkbutton within the datalist, to check the ID column for the ID of the selected person and not take any action if the ID already exists? I don't want any error or messages to appear.

I've provided code below in case it helps. THANK YOU in advance for any insight you might provide.
Protected Sub AddToClinicianListFromOffices(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs)
 
        Dim ID As Integer = DataListOfficesTherapists.DataKeys(e.Item.ItemIndex)
        Dim firstNameAdd As Label = CType(e.Item.FindControl("firstNameLabel"), Label)
        Dim lastNameAdd As Label = CType(e.Item.FindControl("lastNameLabel"), Label)
        Dim firstNameAddText As String = firstNameAdd.Text
        Dim lastNameAddText As String = lastNameAdd.Text
 
AddClinicianTable(ID, firstNameAddText, lastNameAddText, CType(Session("CliniciansTableSession"), DataTable))
 
 Me.RepeaterClinicians.DataSource = CType(Session("CliniciansTableSession"), DataTable).DefaultView
 
        Me.RepeaterClinicians.DataBind()
        Me.DataListOfficesTherapists.DataBind()
        ID = CInt(ID)
        firstNameAddText = ""
        lastNameAddText = ""
 
    End Sub
 
 
Protected Sub AddToClinicianListFromHealthIssues(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs)
 
        Dim ID As Integer = DataListIssuesTherapists.DataKeys(e.Item.ItemIndex)
        Dim firstNameAdd As Label = CType(e.Item.FindControl("firstNameLabel"), Label)
        Dim lastNameAdd As Label = CType(e.Item.FindControl("lastNameLabel"), Label)
        Dim firstNameAddText As String = firstNameAdd.Text
        Dim lastNameAddText As String = lastNameAdd.Text
 
        AddClinicianTable(ID, firstNameAddText, lastNameAddText, CType(Session("CliniciansTableSession"), DataTable))
 
        Me.RepeaterClinicians.DataSource = CType(Session("CliniciansTableSession"), DataTable).DefaultView
 
        Me.RepeaterClinicians.DataBind()
        Me.DataListOfficesTherapists.DataBind()
        ID = CInt(ID)
        firstNameAddText = ""
        lastNameAddText = ""
 
    End Sub

Open in new window

0
Comment
Question by:John0214
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 12

Expert Comment

by:Adam
ID: 24754042
Not sure what type of database you are using, but most (including Access and SQL Server) can automatically generate the ID for you, so you don't have to add it yourself. This way it is guaranteed to be unique without you having to do anything!

In SQL2005/2008, right click on the table and select 'Design'
Select the ID column, then look at the Column Properties at the bottom of the dialog.
Scroll down until you find 'Identity Specification'. Click on the + sign to expand
Double click on the (Is Identity) row to set it to 'Yes'

In Access 2007, select the table and Click on 'Design View'. Make sure your ID column is of type 'AutoNumber'.
It will be similar in older versions of Access to, but I don't have any installed.

MySQL and other databases will also have some means of doing this.

Now, modify your AddClinicianTable() method so it doesn't take an ID property, nor send one to the database.
0
 

Author Comment

by:John0214
ID: 24754104
Cyber-spy, thank you for your speedy and thorough response. I really appreciate it!

Actually, I'm using a datatable that is stored in a session variable. This datatable is created when the user visits the website (created in the global.asax on session start). I just want to store data temporarily (much like a shopping cart)  during the user's visit.
0
 
LVL 2

Accepted Solution

by:
kbac earned 1000 total points
ID: 24754454
Use simply try catch else for catching the errors, and act as you like on any error.

For this particular ID situation, you can do a select first and then decide if you want to insert or not. But the try catch resolves this issue as well. In debug mode, catch the duplicate ID error code, and manage it in your code in the catch area. Telling the user that the ID already exists is better than acting nonresponsive.
0
 

Author Comment

by:John0214
ID: 24755177
kbac, great suggestion! I will do this. Thank you!
0
 

Author Closing Comment

by:John0214
ID: 31598800
Thank you, kbac - it works!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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