?
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
Medium Priority
?
256 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
[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 Comments
 
LVL 8

Accepted Solution

by:
bramsquad earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month12 days, 2 hours left to enroll

752 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