none
Copying selected records from one access table to another table RRS feed

  • Question

  • Hi,

    I am trying to copy selected records from one access table to another, but each time only first record get inserted in to the destination table.  I have tried as follows:

    Sub DataTrans()
            Dbconnection.Open()

            Dim StrArr(2) As String 

            MasterReader = MasterCmd.ExecuteReader()

            Try

                While MasterReader.Read()

                    StrArr(0) = (MasterReader("CPF_No".ToString))
                    StrArr(1) = (MasterReader("Name".ToString))
                    StrArr(2) = (MasterReader("Designation".ToString))

                    Try
                        LvMasterCmd.CommandText = "INSERT INTO LeaveMaster" &
                                                                      "(CPF_No, Name, Designation,  MNT, YR, LeaveStatus)" &
                                                                       "VALUES ( @CPF_No, @Name, @Designation,  @MNT, @YR, @LeaveStatus) ;"

                        LvMasterCmd.Parameters.AddWithValue("@CPF_No", StrArr(0))
                        LvMasterCmd.Parameters.AddWithValue("@Name", StrArr(1))
                        LvMasterCmd.Parameters.AddWithValue("@Designation", StrArr(2))

                        LvMasterCmd.Parameters.AddWithValue("@MNT", CmbMonth.Text)
                        LvMasterCmd.Parameters.AddWithValue("@YR", CmbYear.Text)
                        LvMasterCmd.Parameters.AddWithValue("@LeaveStatus", "N")


                        LvMasterCmd.ExecuteNonQuery()
                    Catch ex As Exception

                    End Try

                End While

            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try

            Dbconnection.Close()
        End Sub 
    Sunday, July 15, 2018 2:42 PM

All replies

  • Try finding the possible problem inside the Catch block, which is currently empty. For example, show a message box.

    Maybe you should execute LvMasterCmd.Parameters.Clear() before adding the parameters.


    Sunday, July 15, 2018 3:36 PM
  •  Hi Friends,

    I am trying to copy selected fields from one access table to another access table, but each time only first record of the from tale copied to another to the Len of the from table. I have tried :

    Sub DataTrans()
            Dbconnection.Open()

            Len = MasterAdapter.Fill(MasterDataSet, "Emp_Master")
            MasterReader = MasterCmd.ExecuteReader()

            Try

                While MasterReader.Read()

                    StrArr(0) = (MasterReader("CPF_No".ToString))
                    StrArr(1) = (MasterReader("Name".ToString))
                    StrArr(2) = (MasterReader("Designation".ToString))


                    'CurrYear,Sap_No,DesigIndex,
                    '@CurrYear, @Sap_No, @DesigIndex,

                    Call DataUPdt()

                    'Next

                End While

            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try

            Dbconnection.Close()
        End Sub

    Sub DataUPdt()
            '*********************************************************


            LvMasterCmd.CommandText = "INSERT INTO LeaveMaster  (CPF_No, Name, Designation,  MNT, YR, LeaveStatus)" &
                 "VALUES (@CPF_No, @Name, @Designation,  @MNT, @YR, @LeaveStatus) ;  "


            Try

                LvMasterCmd.Parameters.AddWithValue("@CPF_No", StrArr(0).ToString)
                LvMasterCmd.Parameters.AddWithValue("@Name", StrArr(1).ToString)
                LvMasterCmd.Parameters.AddWithValue("@Designation", StrArr(2).ToString)

                LvMasterCmd.Parameters.AddWithValue("@MNT", CmbMonth.Text)
                LvMasterCmd.Parameters.AddWithValue("@YR", CmbYear.Text)
                LvMasterCmd.Parameters.AddWithValue("@LeaveStatus", "N")


                LvMasterCmd.ExecuteNonQuery()
                
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

    Sunday, July 15, 2018 4:53 PM