Click here to Skip to main content
11,927,187 members (51,161 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB VB.NET SQLite
I have to add items to database, but need to make sure that one item is unique. what do i do here?

e.g lest say i need to add 100 rows of three columns each (school name idcard_num)

it is obvious that name should be unique here, while the rest can have duplicates

1 should i create a unique field , and spit out the database errors that will be thrown to the user?
2. should i check each items before adding?

i have this

Dim conn As New SQLiteConnection("Data Source=" & DBPath)
        Dim cmd As New SQLiteCommand(conn)
        Dim mytransaction As SQLiteTransaction = conn.BeginTransaction()

            '// insert courses //
            For i = 0 To UBound(oCourses)
                cmd = New SQLiteCommand("INSERT INTO courses ([code]," & _
                    "[title],[unit]) VALUES (@code,@title,@unit)", conn)
                cmd.Parameters.AddWithValue("@code", oCourses(i).Code)
                cmd.Parameters.AddWithValue("@title", oCourses(i).Title)
                cmd.Parameters.AddWithValue("@unit", oCourses(i).Unit)
            Return Nothing
        Catch ex As Exception
            Return ex.Message
        End Try
Posted 15-Jun-11 23:05pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

i will suggest you to go with approach 1 but do not throw the exception as it is.Check it before like if it's related to unique key display a proper message saying "Name already exists in the database."
Cool Smith 16-Jun-11 4:26am
i need to add alot of rows, so what do i do it row10 has the duplicate value, move to next row or abort the whole operation
CS2011 16-Jun-11 4:47am
Well this depends on your requirement. But if i am at your place i would stop the insert operation as data given is wrong and ask the user to correct the data first. But again you can keep the bad data in another file and continue with the insert and inform the user once the operation is complete.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Checking all the items before adding will be an overhead. I suggest you go with #1. Just make sure to handle exceptions properly so users will not freak out when they see technical errors. Smile | :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web04 | 2.8.151126.1 | Last Updated 16 Jun 2011
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100