-
August 25th, 2012, 09:13 AM
#1
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,
-
September 9th, 2012, 06:56 PM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|