Home > Net >  Removing duplicate header in vBA script?
Removing duplicate header in vBA script?

Time:04-27

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

CodePudding user response:

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>'
                ...

CodePudding user response:

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

  •  Tags:  
  • sql
  • Related