1 Replies - 289 Views - Last Post: 17 November 2019 - 07:40 AM Rate Topic: -----

#1 dpointer   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 50
  • Joined: 06-March 09

How to update value from one database to another database in vb.net?

Posted 17 November 2019 - 07:29 AM

I'm using MS Access database. I want to update data value of another database base on their primary key. ControlNo(primary key of Database1) and Controlno(primary key of Database2). If the value of Database1 is being updated, so as well in Database2.

This is my code:
Dim connstring As String
       connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;"
       Dim conn As OleDbConnection = New OleDbConnection(connstring)
       Dim updatescardempnum As String = "UPDATE Database1_Table [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database2.accdb;].Database2_Table ON Database1_Table.fn = Database2_Table.FirstName SET Database1_Table.ControlNo = Database2_Table.Controlno"
       Dim cmdupdatescardempnum As New OleDbCommand(updatescardempnum, conn)
       conn.Open()
       cmdupdatescardempnum.ExecuteNonQuery()
       conn.Close()



The code above shows this error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Additional information: Syntax error in UPDATE statement.

This post has been edited by dpointer: 17 November 2019 - 07:45 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How to update value from one database to another database in vb.net?

#2 LadyCynthia   User is offline

  • D.I.C Head

Reputation: 26
  • View blog
  • Posts: 106
  • Joined: 29-December 16

Re: How to update value from one database to another database in vb.net?

Posted 17 November 2019 - 07:40 AM

JOIN is not valid syntax in UODATE. You will need to run multiple updates in order to achieve this. First run a AELECT query to get the data and then run the UOSATES on the second database.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1