Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    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?

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    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.

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Resolved Re: How to copy records from one database table to another database table?

    Quote Originally Posted by jmcilhinney View Post
    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👍💪.

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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
  •  



Click Here to Expand Forum to Full Width