Solved

Duplicate Records: How do you trap for a duplicate record when importing data from a .txt file and saving it to a SQL 2000 table?

Posted on 2004-10-13
3
212 Views
Last Modified: 2010-04-24
I am importing a text file that contains studentent identifcation information.  Each student has a student Id.  I read the text file and save it to a table.  The program works fine.  While it is processing, it will throw an exception based on the student Id saying there is a duplicate record.  I hit ok and the program continues.  This will occur about 10 times from start to finish.  When I click ok, does it save the duplicate record or does it ignore the record and move to the next record.  How do I know which record it is having a problem with?

Thanks in advance.

Kenny
0
Comment
Question by:kwh3856
3 Comments
 
LVL 8

Accepted Solution

by:
bramsquad earned 500 total points
ID: 12301515
you could create a dataset, and run a query seeing if there exists a record with the same student ID.  

this may be time consuming, but if and when you see a student ID that matches the one you are importing from the text file, you can prompt the user, write the error to a log file, etc.

somewhat psudocode, you could do it like this

'create your query
        Dim sql As String = "SELECT tbl.StudentID FROM tbl WHERE tbl.StudentID = " + Chr(34) + StudentID + Chr(34) + ";"

'create your connection
        conn = New OleDbConnection(YourConnectionString)
        adpt = New OleDbDataAdapter(sql, conn)

'fill dataset with query info
        adpt.Fill(ds, tblName)

'check to see if data exists
        If ds.Tables(tblName).Rows.Count > 0 Then
            'student is already in db!
            MsgBox("ERROR: student ID #" + StudentID + " already exists")
        Else
            'do whatever else you do
        End If

hope this leads you in the right direction

~b
0
 
LVL 1

Expert Comment

by:osp70
ID: 12301612
~ When I click ok, does it save the duplicate record or does it ignore the record and move to the next record.  How do I know which record it is having a problem with? ~

Is this app something you did up yourself and you can modify?

If not then nothing can be done and you need a new app

if so similar to above, trap the student id but instead of a msgbox popping up why not write the error to an error file that you can review at your leisure when you need to.

just an idea.
0
 

Author Comment

by:kwh3856
ID: 12302022
Thanks to each of you for your help.  It is greatly appreicated.

Thanks
Kenny
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now