CODEWITHSUNDEEP
sql
majinvegito123
majinvegito123

Reputation: 25

Removing duplicate header in vBA script?

The following is vbscript that I use to generate an email that outputs specific information. My issue is that the email duplicates the headers and I don't know why. Below I have the code and also a screenshot of what is outputting. Any ideas? Is there something I need to delete / add? Or am I using incorrect syntax?

OPEN rpt_cursor

            FETCH NEXT FROM rpt_cursor 
            INTO 
                @VCustomer ,
                @VSalesOrder  ,
                @VMLineShipDate ,
                @VMStockCode ,
                @VMQtyToDispatch ,
                @VActualDeliveryDate ,
                @VBoxes ,  
                @VDispatchAddress1 ,
                @VDispatchAddress2 ,
                @VDispatchAddress3 ,
                @VDispatchAddress4 ,
                @VDispatchAddress5 ,
                @VDispatchPostalCode ,
                @VNComment ;
                
                    set @lbody = '<BR><BR><TABLE>'
                    set @lbody = @lbody + '<TR><TD>Order -- Dspatch : ' + '</TD><TD>' + @VSalesOrder         + '--' + @liDispatchNote    + '</TD></TR>'
                    set @lbody = @lbody + '<TR><TD>Customer    : '      + '</TD><TD>' + @VCustomer           + '</TD></TR>'
                    set @lbody = @lbody + '<TR><TD>Shipto      : '      + '</TD><TD>' + @VDispatchAddress1   + '</TD></TR>'
                    set @lbody = @lbody + '<TR><TD>              '      + '</TD><TD>' + @VDispatchAddress2   + '</TD></TR>'
                    set @lbody = @lbody + '<TR><TD>              '      + '</TD><TD>' + @VDispatchAddress3   + '</TD></TR>'
                    set @lbody = @lbody + '<TR><TD>              '      + '</TD><TD>' + @VDispatchAddress4   + '</TD></TR>'
                    set @lbody = @lbody + '<TR><TD>              '      + '</TD><TD>' + @VDispatchAddress5   + '</TD></TR>'
                    set @lbody = @lbody + '<TR><TD>              '      + '</TD><TD>' + @VDispatchPostalCode + '</TD></TR>'
        
                    set @lbody = @lbody + '</TABLE>'

                    set @lbody = @lbody + '<TABLE border=1>'

                WHILE @@FETCH_STATUS = 0
                BEGIN
                    
                    set @lbody = @lbody + '<TR><TD>Parts</TD><TD>Qty</TD><TD>Planned Ship</TD><TD>Act Delivery</TD><TD>Boxes</TD><TD>Tracking No.</TR>    '
                    set @lbody = @lbody + '<TD>' + @VMStockCode         + '</TD>    '
                    set @lbody = @lbody + '<TD>' + @VMQtyToDispatch     + '</TD>    '
                    set @lbody = @lbody + '<TD>' + @VMLineShipDate      + '</TD>    '
                    set @lbody = @lbody + '<TD>' + @VActualDeliveryDate + '</TD>    '
                    set @lbody = @lbody + '<TD>' + @VBoxes              + '</TD>    '
                    set @lbody = @lbody + '<TD>' + @VNComment           + '</TD></TR>'
                    
                    FETCH NEXT FROM rpt_cursor 
                    INTO
                        @VCustomer ,
                        @VSalesOrder  ,
                        @VMLineShipDate ,
                        @VMStockCode ,
                        @VMQtyToDispatch ,
                        @VActualDeliveryDate ,
                        @VBoxes ,  
                        @VDispatchAddress1 ,
                        @VDispatchAddress2 ,
                        @VDispatchAddress3 ,
                        @VDispatchAddress4 ,
                        @VDispatchAddress5 ,
                        @VDispatchPostalCode ,
                        @VNComment ;

                END  -- end fetch

            set @lbody = @lbody + '</TABLE>'

            CLOSE rpt_cursor;
            DEALLOCATE rpt_cursor;

enter image description here

Upvotes: 0

Views: 37

Answers (2)

Serg
Serg

Reputation: 22811

The row

set @lbody = @lbody + '<TR><TD>Parts</TD><TD>Qty</TD><TD>Planned Ship</TD><TD>Act Delivery</TD><TD>Boxes</TD><TD>Tracking No.</TR>    '

should be before WHILE loop. Also add TR tag in the loop

           ...
           set @lbody = @lbody + <TD>Qty</TD><TD>Planned Ship</TD><TD>Act Delivery</TD><TD>Boxes</TD><TD>Tracking No.</TR>    '
           WHILE @@FETCH_STATUS = 0
            BEGIN
             
                set @lbody = @lbody + '<TR><TD>' + @VMStockCode         + '</TD>    '
                set @lbody = @lbody + '<TD>' + @VMQtyToDispatch     + '</TD>    '
                set @lbody = @lbody + '<TD>' + @VMLineShipDate      + '</TD>    '
                set @lbody = @lbody + '<TD>' + @VActualDeliveryDate + '</TD>    '
                set @lbody = @lbody + '<TD>' + @VBoxes              + '</TD>    '
                set @lbody = @lbody + '<TD>' + @VNComment           + '</TD></TR>'
                ...

Upvotes: 1

JohanB
JohanB

Reputation: 376

Your are incrementing

set @lbody = @lbody + '<TR><TD>Parts</TD><TD>Qty</TD><TD>Planned Ship</TD><TD>Act Delivery</TD><TD>Boxes</TD><TD>Tracking No.</TR>

so everytime you go throught your loop, so your header will be duplicated.

You should move the part where you write the Header, outside (before) your WHILE

Upvotes: 1

Related Questions