Results 1 to 12 of 12

Thread: [RESOLVED] How to update rows if exists else insert new rows in access table

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Resolved [RESOLVED] How to update rows if exists else insert new rows in access table

    Hi,

    I am doing a window application using VB2010 and Access as back end.
    I have tried many ways to check if the rows exists then update a particular field else insert new rows.
    I am using datagridview in from1 where user able add new info (rows) and those rows will be saved in TableA and I need to insert same rows in another Table (TableB) if the rows not exists, else if rows exists then values should be updated.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: How to update rows if exists else insert new rows in access table

    can you post your code you use to bind the dgv?
    usually, you'd call update on the dataadapter + it'll insert, delete, update automatically

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to update rows if exists else insert new rows in access table

    Quote Originally Posted by .paul. View Post
    can you post your code you use to bind the dgv?
    usually, you'd call update on the dataadapter + it'll insert, delete, update automatically
    Thanks for your reply .Paul
    My DGV (Table A) is not binded
    Below is are the code to update TableB
    Code:
    Dim Con As OleDb.OleDbConnection
    Dim MyCom As OleDb.OleDbCommand
    Dim Sql As String
    Dim result As Integer
    Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Store.mdb;" & _
                                                  "Jet OLEDB:Database Password=12345;")
    Con.Open()
    Sql = "SELECT Itemid FROM TableB"
    MyCom = New OleDb.OleDbCommand(Sql, Con)
    result = MyCom.ExecuteScalar
    If result > 0 Then
    Dim strupdate As String
    strupdate = "UPDATE TableB SET Items = '00002' where Itemid = 32"
    MyCom.CommandText = strupdate
    MyCom.Connection = Con
    MyCom.ExecuteNonQuery()
    Else
    ' I need to insert the new rows in TableB
    Thanks...

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to update rows if exists else insert new rows in access table

    Quote Originally Posted by tnncprojects View Post
    Thanks for your reply .Paul
    My DGV (Table A) is not binded
    Below is are the code to update TableB
    Code:
    Dim Con As OleDb.OleDbConnection
    Dim MyCom As OleDb.OleDbCommand
    Dim Sql As String
    Dim result As Integer
    Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Store.mdb;" & _
                                                  "Jet OLEDB:Database Password=12345;")
    Con.Open()
    Sql = "SELECT Itemid FROM TableB"
    MyCom = New OleDb.OleDbCommand(Sql, Con)
    result = MyCom.ExecuteScalar
    If result > 0 Then
    Dim strupdate As String
    strupdate = "UPDATE TableB SET Items = '00002' where Itemid = 32"
    MyCom.CommandText = strupdate
    MyCom.Connection = Con
    MyCom.ExecuteNonQuery()
    Else
    ' I need to insert the new rows in TableB
    Thanks...
    This is my code to insert the rows in TableA
    Code:
    Dim Cnn_TableA  As New OleDbConnection
     Dim Conn_String1 As String = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Store.mdb;" & _
                                                    "Jet OLEDB:Database Password=12345;")
    
                        Cnn_TableA .ConnectionString = Conn_String1
    
                        Dim SQL_Cust1 As String = "INSERT INTO TableA (Description, Items) VALUES(?,?)"
    
                        Cnn_TableA .Open()
    
                        Dim Cmd_Insert1 As New OleDbCommand(SQL_Cust1, Cnn_TableA)
    
                        With Me.DataGridView1
                            For i As Integer = 0 To .RowCount - 1
                                Description = .Rows(i).Cells(0).Value
                                Items  = .Rows(i).Cells(1).Value
                              With Cmd_Insert1
                                        .Parameters.AddWithValue("@Description ", Description)
                                        .Parameters.AddWithValue("@Items", Items )
                                        .ExecuteNonQuery()
                                        .Parameters.Clear()
                              End With
                            Next
                        End With
                        Cmd_Insert1.Dispose()
                        Cnn_TableA .Close()

  5. #5
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: How to update rows if exists else insert new rows in access table

    in my opinion there are 3 ways
    (1) the best is as what .Poul advised ( but unfortunately doesn't suites you )

    (2) Insert all the selected / Required records into a table in your mdb file and then
    find the duplicate rows by compare to your original table
    use the same query to insert the new rows
    As far as the edit is concern there is no problem even if the update query executes

    (3) The best possible method i this case is , control the SQL execution from your front end it self

    Note:- If you would have used SQL / MySQL server then there would have more options like MySQL REPLACE
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: How to update rows if exists else insert new rows in access table

    something like this:

    Code:
    Dim Con As OleDb.OleDbConnection
    Dim MyCom As OleDb.OleDbCommand
    Dim Sql As String
    Dim result As Object
    Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Store.mdb;" & _
                                                  "Jet OLEDB:Database Password=12345;")
    Con.Open()
    With Me.DataGridView1
        For i As Integer = 0 To .RowCount - 1
    
            Sql = "SELECT COUNT(*) FROM TableB WHERE Itemid = " & 32
            MyCom = New OleDb.OleDbCommand(Sql, Con)
            result = MyCom.ExecuteScalar
            If result IsNot Nothing Then
                Dim strupdate As String
                strupdate = "UPDATE TableB SET Items = '00002' where Itemid = 32"
                MyCom = New OleDb.OleDbCommand(strupdate, Con)
                MyCom.ExecuteNonQuery()
            Else
                ' I need to insert the new rows in TableB
                Dim strInsert As String
                strInsert = "INSERT INTO TableA (Description, Items) VALUES(?,?)"
                MyCom = New OleDb.OleDbCommand(strInsert, Con)
                Description = .Rows(i).Cells(0).Value
                Items = .Rows(i).Cells(1).Value
                MyCom.Parameters.AddWithValue("@Description ", Description)
                MyCom.Parameters.AddWithValue("@Items", Items)
                MyCom.ExecuteNonQuery()
            End If
        Next
    End With

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to update rows if exists else insert new rows in access table

    Quote Originally Posted by make me rain View Post
    in my opinion there are 3 ways
    (1) the best is as what .Poul advised ( but unfortunately doesn't suites you )

    (2) Insert all the selected / Required records into a table in your mdb file and then
    find the duplicate rows by compare to your original table
    use the same query to insert the new rows
    As far as the edit is concern there is no problem even if the update query executes

    (3) The best possible method i this case is , control the SQL execution from your front end it self

    Note:- If you would have used SQL / MySQL server then there would have more options like MySQL REPLACE

    Thanks make me rain...
    can you explain about your 3rd option... how to "control the SQL execution from your front end it self"

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to update rows if exists else insert new rows in access table

    Quote Originally Posted by .paul. View Post
    something like this:

    Code:
    Dim Con As OleDb.OleDbConnection
    Dim MyCom As OleDb.OleDbCommand
    Dim Sql As String
    Dim result As Object
    Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Store.mdb;" & _
                                                  "Jet OLEDB:Database Password=12345;")
    Con.Open()
    With Me.DataGridView1
        For i As Integer = 0 To .RowCount - 1
    
            Sql = "SELECT COUNT(*) FROM TableB WHERE Itemid = " & 32
            MyCom = New OleDb.OleDbCommand(Sql, Con)
            result = MyCom.ExecuteScalar
            If result IsNot Nothing Then
                Dim strupdate As String
                strupdate = "UPDATE TableB SET Items = '00002' where Itemid = 32"
                MyCom = New OleDb.OleDbCommand(strupdate, Con)
                MyCom.ExecuteNonQuery()
            Else
                ' I need to insert the new rows in TableB
                Dim strInsert As String
                strInsert = "INSERT INTO TableA (Description, Items) VALUES(?,?)"
                MyCom = New OleDb.OleDbCommand(strInsert, Con)
                Description = .Rows(i).Cells(0).Value
                Items = .Rows(i).Cells(1).Value
                MyCom.Parameters.AddWithValue("@Description ", Description)
                MyCom.Parameters.AddWithValue("@Items", Items)
                MyCom.ExecuteNonQuery()
            End If
        Next
    End With
    Thanks .Paul....
    I will try your suggestion and let you know...

  9. #9
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: How to update rows if exists else insert new rows in access table

    Paul is answered with option 3
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  10. #10
    Fanatic Member
    Join Date
    Mar 2009
    Posts
    739

    Re: How to update rows if exists else insert new rows in access table

    Just do the UPDATE first

    UPDATE tblSomeTable WHERE ItemID = iSomeInteger

    if con.executenonquery < 1 then

    do the INSERT



    Did you know that ExecuteNonQuery returns an integer.? The result is an integer giving the number of affect rows.


    If the data often exists already the UPDATE succeeds and you've done it with just one execute. But if you do a SELECT first then you're always taking two.

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to update rows if exists else insert new rows in access table

    IanS has the right answer - no reason to check if it exists before the UPDATE - as the UPDATE will tell you by the "rows affected" value that is returned.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to update rows if exists else insert new rows in access table

    Quote Originally Posted by IanS View Post
    Just do the UPDATE first

    UPDATE tblSomeTable WHERE ItemID = iSomeInteger

    if con.executenonquery < 1 then

    do the INSERT



    Did you know that ExecuteNonQuery returns an integer.? The result is an integer giving the number of affect rows.


    If the data often exists already the UPDATE succeeds and you've done it with just one execute. But if you do a SELECT first then you're always taking two.
    Thank you very much IanS and other vbforums users, I solved this issue with IanS advice...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width