CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 1999
    Location
    Ohio, USA
    Posts
    163

    loop through a data tables

    I have a stored procedure that creates barcodes. Here is the stored procedure:

    Code:
    USE [T0AMolding]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    CREATE PROCEDURE [dbo].[spGenerateCupHolderBarcodes]
    	@ShortNumber	 VARCHAR(10), 
    	@NumberOfBarcodes	 INT
    	
    AS
    BEGIN
    
    	DECLARE @Date            DATETIME,
    			@StartDate            DATETIME,
    			@Number          INTEGER,
    			@Count           INTEGER,
    			@Barcode         VARCHAR(20),
    			@ClockNumber     VARCHAR(10),
    			@PartNumber      VARCHAR(50),
    			@PartDescription VARCHAR(50)
    
    	SET @StartDate = GETDATE()	
    	SET @Date = GETDATE()
    	SET @Count = 1
    	SET @ClockNumber = '9595'
    
    
    	WHILE @Count <= @NumberOfBarcodes
    	   BEGIN
    		  SET @Count = @Count + 1
    
    		  SELECT @PartNumber = [IMLITM],
    				 @PartDescription = [IMDSC1]
    		  FROM [AS400].dbo.f4101
    		  WHERE [IMITM] = @ShortNumber
    
    		  EXECUTE [T0AMolding].[dbo].[spMoldingBarcodeCreate] 
    			 @ShortNumber
    			,@ClockNumber
    			,DEFAULT
    			,@Barcode OUTPUT
    
    		  INSERT INTO [T0AMolding].[dbo].[tbCupHolderHeatInj]
    			   ([fdClockNumber]
    			   ,[fdSerialNumber]
    			   ,[fdBarcode]
    			   ,[fdShortNumber]
    			   ,[fdPartNumber]
    			   ,[fdPartDescription]
    			   ,[fdQuantity]
    			   ,[fdSNP]
    			   ,[fdContainerSN]
    			   ,[fdShiftNumber]
    			   ,[fdEnterDate]
    			   ,[fdStartTime]
    			   ,[fdLotData])
    		 VALUES
    			   (@ClockNumber
    			   ,RIGHT(@Barcode, 3)
    			   ,@Barcode
    			   ,@ShortNumber
    			   ,@PartNumber
    			   ,@PartDescription
    			   ,1
    			   ,1
    			   ,'99999999'
    			   ,'1'
    			   ,@Date
    			   ,@Date
    			   ,'NONE')
    	   END
    
    		SELECT pok.fdBarcode as Barcode,  sht.fdBarcodeCode as PartMarking, sht.fdColorCode as ColorCode
    		FROM [T0AMolding].dbo.tbCupHolderHeatInj pok, [T0AMolding].dbo.tbMoldingShortNumbers sht
    		WHERE sht.fdShortNumber = pok.fdShortnumber and sht.fdWorkstation = 'CupHolderHeatInj' 
    		AND pok.fdEnterDate >= @StartDate AND pok.fdClockNumber = 9595
    
       
    END
    How do I iterate through in my visual basic program and get the result set from the select statement? If I pass 3 for the NumberOfBarcodes I get 4 tables. 3 tables from the 3 instert statements and 1 table from the select statement. I want to ignore the 3 tables from the insert statement and read only the select statement in my vb.net program. How do I do this?

    Thanks,

  2. #2
    Join Date
    Feb 2001
    Location
    Stamford CT USA
    Posts
    2,167

    Re: loop through a data tables

    Hi,

    Not sure how you get 4 tables since there is only one SELECT. You maybe getting the "messages" (usually the row affected count) from the INSERTs (unless your spMoldingBarcodeCreate actually returns recordset). Try putting "SET NOCOUNT ON" at the beginning of your SP which will suppress the affected count message. Then your SP will only return one recordset and you don't have to iterate through DataTables. Also, post your current code in VB that you have so that we can check it out.
    Good Luck,
    -Cool Bizs

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

Featured