Member Avatar for Hannesvz

Hi, I want to copy the data in the PayJour table to the ARCPayJour Table using the Clients Table where the StatusCo = 108. The problem is that the Clients table stop at the first 108 it gets and then do the copy prossecc, but do not go through the rest of the Clients Table to the next 108, and then copy the other data that is equal to 108 in clients table.

Any help please.

Public Function ArchivePayJour()
    Try

        con = New SqlConnection(cs)
        con.Open()
        cmd = New SqlCommand("SELECT RTRIM(FileNumber), RTRIM(StatusCo) from Clients where StatusCo = '" & "108" & "'", con)
        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        With (rdr.Read()) = True
            con = New SqlConnection(cs)
            con.Open()
            Dim cb As String = ("INSERT INTO ArcPayJour (CLFileNumber, FullName, SurName, PayDate, ItemCode, RefNumber, Description, Amount, BranchCode, Branch, RecNumber)") &
             "Select RTRIM(CLFileNumber), RTRIM(FullName), RTRIM(SurName), RTRIM(PayDate), RTRIM(ItemCode), RTRIM(RefNumber), RTRIM(Description), RTRIM(Amount), RTRIM(BranchCode), RTRIM(Branch), RTRIM(RecNumber) from PayJour WHERE CLFileNumber=@d1"
            cmd = New SqlCommand(cb)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", (rdr.GetTextReader(0)))
            cmd.ExecuteReader()
            For Each row In rdr.Read().ToString
            Next row
            con.Close()
            cmd.Dispose()
        End With
        con.Close()
        cmd.Dispose()

    Catch ex As System.Exception
        MessageBox.Show("Error: Copy " + ex.Message)
    End Try
    Return 0
End Function
Edited by Reverend Jim because: Moved to programming forum
  • 2 Contributors
  • 2 Replies
  • 533 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Hannesvz
Member Avatar for AndreRet

That is because your SELECT statement is only asking to return the FileNumber field value and that is the only value inserted into your table. You need to select all to return all data -

cmd = New SqlCommand("SELECT * from Clients where StatusCo = '" & "108" & "'", con)

If you need to trim some of the returned values, do that after the data is returnd.

Edited by AndreRet
Member Avatar for Hannesvz

AndreRet. Thanks for the help. I haven't keep that in mind. Again, thanks a million..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.