Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET Sqlite WinForm
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)
        'conn.SetPassword(dbPassword)
        conn.Open()
        Dim cmd As New SQLiteCommand(conn)
        Dim mytransaction As SQLiteTransaction = conn.BeginTransaction()
        Try
 

            '// 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)
 
                'Debug.Print(cmd.CommandText)
                cmd.ExecuteNonQuery()
              Next
            mytransaction.Commit()
            Return Nothing
        Catch ex As Exception
            mytransaction.Rollback()
            Debug.Print(ex.Message)
            Return ex.Message
        Finally
            cmd.Dispose()
            conn.Dispose()
            mytransaction.Dispose()
        End Try
Posted 15-Jun-11 23:05pm
Rate this: bad
good
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."
  Permalink  
Comments
Cool Smith at 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 at 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
good
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 | :)
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 390
1 Jochen Arndt 165
2 Richard MacCutchan 135
3 DamithSL 95
4 Garth J Lancaster 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 16 Jun 2011
Copyright © CodeProject, 1999-2014
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