[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?

[techgnome]

YEs, in ADO... it's *slighly* different....

Set prm = Cmd.CreateParameter("CategoryID", adInteger, adParamInput, 4, 7)
    Cmd.Parameters.Append prm

Is one way.... or it can still be done all on one line....

Cmd.Parameters.Append Cmd.CreateParameter("CategoryID", adInteger, adParamInput, 4, 7)

-tg

[just_a_me]

Ok. I guess you're right- That is more *slightly* different than *quite* different. Thanks.

[brucevde]

You could call Command.Parameters.Refresh to load the parameter collection rather than creating the collection in code. Make sure the ActiveConnection property is set before calling Refresh method.

Also, Paramters.Item(0) will be the parameter for the Return Value.

[just_a_me]

I am having trouble on the second time that I push the command button on the form. I get an error that says 'Procedure or function "usp_insertPatternData" has too many arguments specified.' I think that it is because I'm appending more parameters. I think I need to clear all of the parameters first. Is that what I'm missing?

Here's my code:

Private dbConnPatterning As ADODB.Connection
Private dbCommand As ADODB.Command
 
Private Sub Form_Load()
 
    Set dbConnPatterning = New ADODB.Connection
    Set dbCommand = New ADODB.Command
    dbCommand.CommandText = "usp_insertPatternData"
    dbCommand.CommandType = adCmdStoredProc
    dbConnPatterning.Provider = "sqloledb"
    
End Sub
 
Private Sub Command1_Click()
    dbConnPatterning.Open "server=esp\opt;uid=tyler1;pwd=password;database=Patterning"
    dbCommand.ActiveConnection = dbConnPatterning
    
    dbCommand.Parameters.Append dbCommand.CreateParameter("@DateTime", adDBTime, adParamInput, 8, Now)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@laserNumber", adTinyInt, adParamInput, 1, 1)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@Operator", adVarChar, adParamInput, 10, "tc")
    dbCommand.Parameters.Append dbCommand.CreateParameter("@LotID", adVarChar, adParamInput, 10, "123")
    dbCommand.Parameters.Append dbCommand.CreateParameter("@WaferNumber", adTinyInt, adParamInput, 1, 1)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@ExposureTime", adTinyInt, adParamInput, 1, 121)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@TimeDelay", adTinyInt, adParamInput, 1, 12)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@LeftDiode", adInteger, adParamInput, 4, 100)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@RightDiode", adInteger, adParamInput, 4, 100)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@TopDiode", adInteger, adParamInput, 4, 100)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@BottomDiode", adInteger, adParamInput, 4, 100)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@CenterDiode", adInteger, adParamInput, 4, 100)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@LeftOffset", adInteger, adParamInput, 4, 0)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@RightOffset", adInteger, adParamInput, 4, 0)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@TopOffset", adInteger, adParamInput, 4, 0)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@BottomOffset", adInteger, adParamInput, 4, 0)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@PowerTrackStatus", adTinyInt, adParamInput, 1, 1)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@Current", adDouble, adParamInput, 8, 12.2)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@TubeHours", adDouble, adParamInput, 8, 1390.1)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@AperturePosition", adDouble, adParamInput, 8, 12.4)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@TEMoo", adTinyInt, adParamInput, 1, 1)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@EtalonMode", adTinyInt, adParamInput, 1, 4)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@PTDACA", adTinyInt, adParamInput, 1, 2)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@PTDACB", adTinyInt, adParamInput, 1, 3)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@AutofillDelta", adDouble, adParamInput, 8, 1.2)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@HeadHours", adDouble, adParamInput, 8, 1390.1)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@CathodeVoltage", adDouble, adParamInput, 8, 63.2)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@CathodeCurrent", adDouble, adParamInput, 8, 12.4)
    dbCommand.Parameters.Append dbCommand.CreateParameter("@MagnetCurrent", adDouble, adParamInput, 8, 6.8)
    
    dbCommand.Execute
    dbConnPatterning.Close
End Sub

[brucevde]

Yes, there is no need to populate the parameters again.

Are you using the dbCommand variable to execute other commands as well?

Personally, I find it better to separate the creation of the parameters and the setting of their values.

Private Sub Command1_Click()
   dbConnPatterning.Open "server=esp\opt;uid=tyler1;pwd=password;database=Patterning"
  dbCommand.ActiveConnection = dbConnPatterning
 
  If dbCommand.Parameters.Count = 0 Then
     'Code to create parameters
  End If
 
  'Code to set parameter values
 
  dbCommand.Execute
  dbConnPatterning.Close
End Sub

[just_a_me]

Thanks Brucevde for your suggestions.

So, could I create the parameters in the form load sub using the .refresh method and then change the values in the command1_click? How do you change just the values of the parameters in ADO?

[techgnome]

Personaly I wouldn't set the command object in the Form_Load event....there's no reason to create it at that point....

-tg

[just_a_me]

Should I create a command object each time I have a command1_click event?

[just_a_me]

I think I found the answer to one of my questions on how to change just the values of the parameters. I'm going to do this:

dbCommand.Parameters.Item("@DateTime").Value = Now

[just_a_me]

That worked beautifully and so did the .refesh method that was suggested above by Brucevde. Thanks guys I think I'm totally good now.