[RESOLVED] Stored Procedures with Parameters using ADO

[just_a_me]

I have a stored procedure that is like this

CREATE PROCEDURE usp_insertPatternData
@DateTime datetime,
@laserNumber tinyint,
@Operator varchar(10),
@LotID varchar(10),
@WaferNumber    tinyint,
@ExposureTime tinyint,
@TimeDelay tinyint,
@LeftDiode int,
@RightDiode int,
@TopDiode int,
@BottomDiode int,
@CenterDiode int,
@LeftOffset int,
@RightOffset int,
@TopOffset int,
@BottomOffset int,
@PowerTrackStatus tinyint,
@Current float,
@TubeHours float,
@AperturePosition float,
@TEMoo tinyint,
@EtalonMode tinyint,
@PTDACA tinyint,
@PTDACB tinyint,
@AutofillDelta float,
@HeadHours float,
@CathodeVoltage float,
@CathodeCurrent float,
@MagnetCurrent float
 
AS
 
set nocount on
 
INSERT INTO Patterning ( dateTime, laserNumber, Operator, LotID, WaferNumber, ExposureNumber, TimeDelay, LeftDiode, RightDiode, TopDiode, BottomDiode, CenterDiode, LeftOffset, RightOffset, TopOffset, BottomOffset, PowerTrackStatus, [Current], TubeHours, AperturePosition, TEMoo, EtalonMode, PTDACA, PTDACB, AutofillDelta, HeadHours, CathodeVoltage, CathodeCurrent, MagnetCurrent)
VALUES (@dateTime, @laserNumber, @Operator, @LotID, @WaferNumber, @ExposureTime, @TimeDelay, @LeftDiode, @RightDiode, @TopDiode, @BottomDiode, @CenterDiode, @LeftOffset, @RightOffset, @TopOffset, @BottomOffset, @PowerTrackStatus, @Current, @TubeHours, @AperturePosition, @TEMoo, @EtalonMode, @PTDACA, @PTDACB, @AutofillDelta, @HeadHours, @CathodeVoltage, @CathodeCurrent, @MagnetCurrent );
GO

and I am trying to pass these parameters in my VB6 code like this:

Private dbConnPatterning As ADODB.Connection
Private dbCommand As ADODB.Command
 
 
Private Sub Form_Load()
 
    Set dbConnPatterning = New ADODB.Connection
    dbConnPatterning.Provider = "sqloledb"
    Set dbCommand = New ADODB.Command
    dbCommand.CommandText = "usp_insertPatternData"
    dbCommand.CommandType = adCmdStoredProc
    
    dbConnPatterning.Open "server=esp\opt;uid=tyler1;pwd=password;database=Patterning"
    dbCommand.Parameters.Item(0).Value = Now
    dbCommand.Parameters.Item(1).Value = 1
    dbCommand.Parameters.Item(2).Value = "abc"
    dbCommand.Parameters.Item(3).Value = "abc"
    dbCommand.Parameters.Item(4).Value = 1
    dbCommand.Parameters.Item(5).Value = 10
    dbCommand.Parameters.Item(6).Value = 2
    dbCommand.Parameters.Item(7).Value = 10
    dbCommand.Parameters.Item(8).Value = 10
    dbCommand.Parameters.Item(9).Value = 10
    dbCommand.Parameters.Item(10).Value = 10
    dbCommand.Parameters.Item(11).Value = 10
    dbCommand.Parameters.Item(12).Value = 5
    dbCommand.Parameters.Item(13).Value = 5
    dbCommand.Parameters.Item(14).Value = 5
    dbCommand.Parameters.Item(15).Value = 5
    dbCommand.Parameters.Item(16).Value = 1
    dbCommand.Parameters.Item(17).Value = 2.2
    dbCommand.Parameters.Item(18).Value = 1200
    dbCommand.Parameters.Item(19).Value = 4
    dbCommand.Parameters.Item(20).Value = 4
    dbCommand.Parameters.Item(21).Value = 4
    dbCommand.Parameters.Item(22).Value = 1
    dbCommand.Parameters.Item(23).Value = 1
    dbCommand.Parameters.Item(24).Value = 10.2
    dbCommand.Parameters.Item(25).Value = 1200
    dbCommand.Parameters.Item(26).Value = 12.2
    dbCommand.Parameters.Item(27).Value = 4.5
    dbCommand.Parameters.Item(28).Value = 6.8
    dbCommand.Execute
    dbConnPatterning.Close
    
    
End Sub

I thought that might work, but it doesn't. I came across some code that does something like this

' Set up a new parameter for the stored procedure.
Set prm = Cmd.CreateParameter("CategoryID", adInteger, adParamInput, 4, 7)
Cmd.Parameters.Append prm

Is this what I should be doing? I'm accustomed to using stored procedures with ADO.NET and this seems to be quite different than

mySqlComm.parameters.add("@LotID", me.txtLotID.text)

which is what you do in ADO.NET. Could that be right?