[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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?

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
kwh3856
Asked:
kwh3856
1 Solution
 
bramsquadCommented:
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
 
osp70Commented:
~ 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
 
kwh3856Author Commented:
Thanks to each of you for your help.  It is greatly appreicated.

Thanks
Kenny
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now