SQL statement in VB

[MikeA]

I have a SQL statement that I have been executing in the Query App in SQL manager. I am trying to put that in a small VB app so the user can enter a few variables found in the script. I cannot figure how to integrate this into VB using ADO. I tried to run the script using the Visual Data manager in VB 6 Enterprise but I get an error (Does not recognize the declare statement). I need to declare a cursor and I think thats where my first wall is. Below is the SQL script I am currently using. I would appreciate any ideas on how I can use this same script in VB with ADO or another connection type.

=====================
Original SQL statement
======================
DECLARE curr_budver_cursor SCROLL CURSOR
FOR SELECT sotrId,sotrStatus,jourId,trctId,trtpId,pdefId,curtId,accoId,unitId,unitIdCorr,unitIdRep,dim1Id,dim1I dCorr,dim2Id,dim3Id,dim4Id,curcId,curcId2,sotrAmount,sotrAmount2,sotrDCFlag,sotrRef,sotrDate,sotrSig n,eusrId,regDate,sotrTxt,sotrTxt2,sotrTxt3,sotrTxt4,formId
FROM sotr WHERE trtpId=3 AND
/*unitId=(select unitId from unit where unitCode='0343') AND*/
(pdefid>=(select pdefid from pdef where pdefPerNo='0001') OR
pdefid<=(select pdefid from pdef where pdefPerNo='0012'))
ORDER BY sotrid
GO
DECLARE
@sotrId integer,
@sotrStatus integer,
@jourId integer,
@trctId integer,
@trtpId integer,
@pdefId integer,
@curtId integer,
@accoId integer,
@unitId integer,
@unitIdCorr integer,
@unitIdRep integer,
@dim1Id integer,
@dim1IdCorr integer,
@dim2Id integer,
@dim3Id integer,
@dim4Id integer,
@curcId integer,
@curcId2 integer,
@sotrAmount numeric(18,4),
@sotrAmount2 numeric(18,4) ,
@sotrDCFlag integer,
@sotrRef integer,
@sotrDate datetime,
@sotrSign integer,
@eusrId integer,
@regDate datetime,
@sotrTxt varchar(255),
@sotrTxt2 varchar(255),
@sotrTxt3 varchar(255),
@sotrTxt4 varchar(255),
@formId integer,
@Date datetime

OPEN curr_budver_cursor
table_loop:
IF (@@fetch_status <> -2)
BEGIN
FETCH Relative 0 FROM curr_budver_cursor INTO @sotrId,@sotrStatus,@jourId,@trctId,@trtpId,@pdefId,@curtId,@accoId,@unitId,@unitIdCorr,@unitIdRep,@ dim1Id,@dim1IdCorr,@dim2Id,@dim3Id,@dim4Id,@curcId,@curcId2,@sotrAmount,@sotrAmount2,@sotrDCFlag,@so trRef,@sotrDate,@sotrSign,@eusrId,@regDate,@sotrTxt,@sotrTxt2,@sotrTxt3,@sotrTxt4,@formId
SELECT @sotrId=(SELECT nextId from idService WHERE tableNr=11000)
select @Date = GETDATE()
INSERT INTO sotr (sotrId, sotrStatus, jourId, trctId, trtpId, pdefId, curtId, accoId, unitId, unitIdCorr, unitIdRep, dim1Id, dim1IdCorr, dim2Id, dim3Id, dim4Id, curcId, curcId2, sotrAmount, sotrAmount2, sotrDCFlag, sotrRef, sotrDate, sotrSign, eusrId, regDate,sotrTxt,sotrTxt2,sotrTxt3,sotrTxt4,formId) VALUES
(@sotrId,0,19197,@trctId,4,@pdefId,@curtId,@accoId,@unitId,@unitIdCorr,@unitIdRep,@dim1Id,@dim1IdCor r,@dim2Id,@dim3Id,@dim4Id,@curcId,@curcId2,@sotrAmount,@sotrAmount2,@sotrDCFlag,@sotrRef,@Date,@sotr Sign,@eusrId,@Date,@sotrTxt,@sotrTxt2,@sotrTxt3,@sotrTxt4,@formId)
UPDATE idService SET nextId = (SELECT MAX(sotrId) + 1 FROM sotr) WHERE tableNr = 11000
END
FETCH NEXT FROM curr_budver_cursor
IF (@@fetch_status <> -1) GOTO table_loop

CLOSE curr_budver_cursor
DEALLOCATE curr_budver_cursor




This statement is quite complex - but works in ISQL on my SQL server. Any ideas on the syntax to make this work in VB are greatly appreciated.

Regards,
MIKE

[Clunietp]

why don't you just put all that into a stored procedure and have your vb client call that SP?