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?
Re: How to copy records from one database table to another database table?
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.
Re: How to copy records from one database table to another database table?
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👍💪.
Re: How to copy records from one database table to another database table?
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;