Problem with parameters in stored procedure

[kappa12]

I've got a sp that originally took two params, but has been modified to to use three. It worked fine before, but now seems to give me the error that it's expecting a certain param but that it's not being supplied. I know for a fact that all three params are declared correctly and are all being initialized with the correct values, but the sp still doesn't seem to recognize that one of them is there. I don't know if this is relevant but right before I changed the sp we cleared the database and added a few tables but that's all that's changed.

Here's the code I use for creating the parameters. Like I said before, this worked earlier when only two parameters were being used, but it doesn't seem to work with two params now when I change it back to test it. Code:

'Call stored procedure for creating Tracking ID Number and adding Route Users to Tracking List
cmd.ActiveConnection = conn
cmd.CommandText = "getNextTrackingNumber"

'Add necessary parameters
Set paramRouteID = cmd.CreateParameter("@RouteID", adInteger, adParamInput, , routeid)
cmd.Parameters.Append paramRouteID
Set paramUser = cmd.CreateParameter("@CurrentUser", adVarChar, adParamInput, 255, UCase(Trim(CurrentUser)))
cmd.Parameters.Append paramUser
Set paramRouteType = cmd.CreateParameter("@RouteType", adVarChar, adParamInput, 255, UCase(Trim(ROUTE_APPROVAL_SHEET)))
cmd.Parameters.Append paramRouteType

I've debugged it and I'm 100% positive that there are values for these parameters after they've been created, so I'm at a complete loss as to what the problem here is. Any help would be appreciated.

Thanks,
Josh

[skhanal]

Can you post the top part of your stored procedure.

create ...


as

[kappa12]

Sure, here's the sp exactly as it is in the db:


CREATE PROCEDURE getNextTrackingNumber
@RouteID as INT,
@CurrentUser as VARCHAR(255),
@RouteType as VARCHAR(255)

AS

DECLARE @trackingID as int
SELECT @trackingID=max(tracking_ID) from tracking_ids
IF @trackingID is null
SET @trackingID=1
ELSE
SET @trackingID=@trackingID+1

INSERT INTO TRACKING_IDS (
Tracking_ID,
Route_Type,
Change_User,
Change_Time
)
VALUES (
@trackingID,
@RouteType,
@CurrentUser,
current_timestamp
)

INSERT INTO ROUTE_TRACKING (
TRACKING_ID,
[USER],
[SEQUENCE],
APPROVED,
APPROVED_TIME,
CHANGE_USER,
CHANGE_TIME
)
SELECT
@trackingID,
[USER],
[SEQUENCE],
NULL,
NULL,
@CurrentUser,
CURRENT_TIMESTAMP
FROM ROUTE_LISTS
WHERE ROUTE = @RouteID

SELECT @trackingID as TRACKING_ID


GO

[skhanal]

Your procedure looks fine.

Are you able to run the stored procedure from Query Anayzer?

[kappa12]

Yes, I was able to use the sp in Query Analyzer. So, instead of using the ADODB Parameter syntax, I just entered it as a string into the CommandText. This seems to work for some reason so I'll just stick to this method. However, if anyone has any idea as to why it wouldn't work the other way, feel free to enlighten me. Thanks for the reply skhanal.

Josh