-
Aug 26th, 2019, 02:14 AM
#1
Thread Starter
Member
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?
-
Aug 26th, 2019, 02:22 AM
#2
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.
-
Aug 26th, 2019, 09:30 AM
#3
Thread Starter
Member
Re: How to copy records from one database table to another database table?
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👍💪.
-
Aug 26th, 2019, 10:33 AM
#4
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;
Sometimes the Programmer
Sometimes the DBA
Mazz1
Tags for this Thread
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
|