edfreels
asked on
Moving data to and from Stored Procedures
I have just started working with Stored Proc's and have run into a problem that has me unsure what to do next. The original straight SQL mined what I wanted perfectly and now that I have placed it in a proc I cant get it to work. Any suggestions would be wonderful. Thank you.
**************Stored Proc********************** ********** ********** ********** *
CREATE PROCEDURE SP_ExceptionCheck(
@DateSelected datetime,
@StoreNum bigint OUT,
@Location varchar(50) OUT,
@TrendarNum varchar(50) OUT)
AS
SET NOCOUNT ON
SELECT StoreNum, Location, TrendarNum FROM Trendar
WHERE TrendarNum NOT IN (SELECT TSID FROM C3DATA WHERE INVDTE = @DateSelected)
GO
************************** ********** ********** ********** ********** ********** *
***************VB6 Code********************** ********** ********** ********** ***
Do Until DateSelected = (frmMain.dtpEndDate.Value + 1)
Dim rsEx As ADODB.Recordset
With cmd
Set .ActiveConnection = cn
.CommandText = "SP_ExceptionCheck"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
' Define stored procedure params and append to command.
params.Append cmd.CreateParameter("@Wilc oStoreNum" , adBigInt, adParamOutput, 0)
params.Append cmd.CreateParameter("@Loca tion", adVarChar, adParamOutput, 50)
params.Append cmd.CreateParameter("@Tren darNum", adVarChar, adParamOutput, 50)
params.Append cmd.CreateParameter("@Date Selected", adDBTimeStamp, adParamInput, 0)
' Specify input parameter values
params("@DateSelected") = DateSelected
' Execute the command
Set rsEx = cmd.Execute
Dim CmdInsert As ADODB.Command
Set CmdInsert = New ADODB.Command
Set CmdInsert.ActiveConnection = conC3V
With rsEx
Do While Not rsEx.EOF
CmdInsert.CommandText = "INSERT INTO Exceptions(WilcoStoreNum,L ocation,Tr endarNum,E xcDate) " & _
"Values ('" & rsEx![WilcoStoreNum] & "','" & rsEx![Location] & "','" & rsEx![TrendarNum] & "','" & DateSelected & "')"
CmdInsert.Execute
.MoveNext
Loop
End With
rsEx.Close
cn.Close
DateSelected = (DateSelected + 1)
Loop
************************** ********** ********** ********** ********** ********** ********
The original SQL was:
rstDateExceptionCheck2.Ope n "SELECT WilcoStoreNum,Location,Tre ndarNum FROM Trendar " & _
'"WHERE TrendarNum NOT IN (SELECT TSID FROM C3DATA WHERE INVDTE = '" & DateSelected & "')", conC3V, adOpenKeyset, adLockOptimistic
************************** ********** ********** ********** ********** ********** ********
The Error I'm getting states: Formal parameter '@DateSelected' was defined as OUTPUT but the actual parameter not declared OUTPUT.
**************Stored Proc**********************
CREATE PROCEDURE SP_ExceptionCheck(
@DateSelected datetime,
@StoreNum bigint OUT,
@Location varchar(50) OUT,
@TrendarNum varchar(50) OUT)
AS
SET NOCOUNT ON
SELECT StoreNum, Location, TrendarNum FROM Trendar
WHERE TrendarNum NOT IN (SELECT TSID FROM C3DATA WHERE INVDTE = @DateSelected)
GO
**************************
***************VB6 Code**********************
Do Until DateSelected = (frmMain.dtpEndDate.Value + 1)
Dim rsEx As ADODB.Recordset
With cmd
Set .ActiveConnection = cn
.CommandText = "SP_ExceptionCheck"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
' Define stored procedure params and append to command.
params.Append cmd.CreateParameter("@Wilc
params.Append cmd.CreateParameter("@Loca
params.Append cmd.CreateParameter("@Tren
params.Append cmd.CreateParameter("@Date
' Specify input parameter values
params("@DateSelected") = DateSelected
' Execute the command
Set rsEx = cmd.Execute
Dim CmdInsert As ADODB.Command
Set CmdInsert = New ADODB.Command
Set CmdInsert.ActiveConnection
With rsEx
Do While Not rsEx.EOF
CmdInsert.CommandText = "INSERT INTO Exceptions(WilcoStoreNum,L
"Values ('" & rsEx![WilcoStoreNum] & "','" & rsEx![Location] & "','" & rsEx![TrendarNum] & "','" & DateSelected & "')"
CmdInsert.Execute
.MoveNext
Loop
End With
rsEx.Close
cn.Close
DateSelected = (DateSelected + 1)
Loop
**************************
The original SQL was:
rstDateExceptionCheck2.Ope
'"WHERE TrendarNum NOT IN (SELECT TSID FROM C3DATA WHERE INVDTE = '" & DateSelected & "')", conC3V, adOpenKeyset, adLockOptimistic
**************************
The Error I'm getting states: Formal parameter '@DateSelected' was defined as OUTPUT but the actual parameter not declared OUTPUT.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Jim, that did the trick. Talk about not paying attention!
ASKER
params.Append cmd.CreateParameter("@Date
to adParamInputOutput but I still get the same error