How to copy records from one database table to another database table?

This my code:

Code:

---

Dim con1 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=AMT.accdb ")
        Dim con2 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ATS.accdb")

        'Create the data adapter with a SelectCommand using the first connection.
        Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, MiddleName, LastName , salrate FROM apen ", con1)
        'Add the InsertCommand with the second connection.                                                             
        da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO ATS (FirstName, MiddleName, LastName, salary) VALUES (@FirstName, MiddleName, @LastName, @salrate)", con2)

        'Add the insert parameters.                                                                                               
     
        da.InsertCommand.Parameters.Add("@FirstName", OleDb.OleDbType.VarChar, 50, "FirstName")
        da.InsertCommand.Parameters.Add("@MiddleName", OleDb.OleDbType.VarChar, 50, "MiddleName")
        da.InsertCommand.Parameters.Add("@LastName", OleDb.OleDbType.VarChar, 50, "LastName")
        da.InsertCommand.Parameters.Add("@salrate", OleDb.OleDbType.VarChar, 50, "salary")
        'Keep the records in a state where they can be inserted into the destination table.
        da.AcceptChangesDuringFill = False
        Dim dt As New DataTable
        'Get the data from the source database.
        da.Fill(dt)
        'Save the data to the destination database.
        da.Update(dt)
        MsgBox("Data Added!")
     
        con1.Dispose()
        con2.Dispose()

---

The code above does not work and it displays error: @salrate has no default value.
Any idea on how to fix the error?

The problem is here:

Code:

---

da.InsertCommand.Parameters.Add("@salrate", OleDb.OleDbType.VarChar, 50, "salary")

---

You are telling the adapter to draw the data from a column named "salary" but no such column exists:

Code:

---

Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, MiddleName, LastName , salrate FROM apen ", con1)

---

You need to use the same column name in both places.

Quote:

---

Originally Posted by jmcilhinney

The problem is here:

Code:

---

da.InsertCommand.Parameters.Add("@salrate", OleDb.OleDbType.VarChar, 50, "salary")

---

You are telling the adapter to draw the data from a column named "salary" but no such column exists:

Code:

---

Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, MiddleName, LastName , salrate FROM apen ", con1)

---

You need to use the same column name in both places.

---

Thank you jmcilhinney. It works now. You're the man👍💪.

Just a stupid question maybe but Why not just do this in one SQL statement?

Code:

---

INSERT INTO  ATS (FirstName, MiddleName, LastName, salary)
  SELECT  FirstName, MiddleName, LastName , salrate FROM apen;

---