none
how to avoid adding items to database if they are already in it RRS feed

  • Question

  • Hello,

     

    I'd like to know how can i add items to a database but only when they are not added yet.

     

    For example i want to add a name to the database: "John Smith".

    First i need to check whether John Smith exists in the database and if not i'd like to add it otherwise i'd like to skip the name.

     

    Im using VB2005.

     

    Thanks;

    Thursday, June 5, 2008 8:22 PM

Answers

  • There might be a way to do it with some DDL that I'm not aware of. However, I believe this should work.

     

    Code Snippet
    IF NOT EXISTS(SELECT name FROM t1 WHERE name='John Smith')
         INSERT INTO t1 VALUES('John Smith')

     

     

     

    Thursday, June 5, 2008 11:23 PM
  • One really easy way to do this is define a unique index on the field in the table.

     

    Then all you have to do is fire off the insert inside a try/catch and the database will reject duplicates.

     

    For example:

     

    create table List (MyName varchar(255))

    go

    create unique index idxListMyName on List (MyName)

    go

    insert List values ('John Smith')

    insert List values ('John Smith')

    go

    Tuesday, June 10, 2008 6:31 AM

All replies

  • There might be a way to do it with some DDL that I'm not aware of. However, I believe this should work.

     

    Code Snippet
    IF NOT EXISTS(SELECT name FROM t1 WHERE name='John Smith')
         INSERT INTO t1 VALUES('John Smith')

     

     

     

    Thursday, June 5, 2008 11:23 PM
  • Thanks, i'll check it as soon as i get home...

     

    Friday, June 6, 2008 10:23 AM
  • okay, i don't know what to get with this... i add items this way:

     

    Code Snippet

    Dim ds As New DataSet

    Dim da As OleDb.OleDbDataAdapter

    Dim sql As String

    sql = "select * from [list]"

    da = New OleDb.OleDbDataAdapter(sql, con)

    da.Fill(ds, "list")

    Dim cb As New OleDb.OleDbCommandBuilder(da)

    Dim dsNewRow As DataRow

    'if john smith is not in the database then add him <- how to check this here?

    dsNewRow = ds.Tables("list").NewRow()

    dsNewRow.Item("Name") = "John Smith"

     

     

     

     

    Friday, June 6, 2008 5:01 PM
  • One really easy way to do this is define a unique index on the field in the table.

     

    Then all you have to do is fire off the insert inside a try/catch and the database will reject duplicates.

     

    For example:

     

    create table List (MyName varchar(255))

    go

    create unique index idxListMyName on List (MyName)

    go

    insert List values ('John Smith')

    insert List values ('John Smith')

    go

    Tuesday, June 10, 2008 6:31 AM