-
Dec 15th, 2013, 10:26 AM
#1
Thread Starter
Lively Member
[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.
-
Dec 15th, 2013, 10:35 AM
#2
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Dec 15th, 2013, 11:25 AM
#3
Thread Starter
Lively Member
Re: How to update rows if exists else insert new rows in access table
Originally Posted by .paul.
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...
-
Dec 15th, 2013, 11:33 AM
#4
Thread Starter
Lively Member
Re: How to update rows if exists else insert new rows in access table
Originally Posted by tnncprojects
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()
-
Dec 15th, 2013, 12:28 PM
#5
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
-
Dec 15th, 2013, 12:31 PM
#6
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Dec 15th, 2013, 12:36 PM
#7
Thread Starter
Lively Member
Re: How to update rows if exists else insert new rows in access table
Originally Posted by make me rain
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"
-
Dec 15th, 2013, 12:38 PM
#8
Thread Starter
Lively Member
Re: How to update rows if exists else insert new rows in access table
Originally Posted by .paul.
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...
-
Dec 15th, 2013, 12:51 PM
#9
Re: How to update rows if exists else insert new rows in access table
Paul is answered with option 3
-
Dec 16th, 2013, 09:55 AM
#10
Fanatic Member
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.
-
Dec 16th, 2013, 10:37 AM
#11
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.
-
Dec 26th, 2013, 01:12 PM
#12
Thread Starter
Lively Member
Re: How to update rows if exists else insert new rows in access table
Originally Posted by IanS
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|