[RESOLVED] Parameters in stored procedure - BLOW UP

[DebbieInFlorida]

Hey Gurus,
I have a stored procedure that has worked perfect, but now that I have changed the DB schema, it blows up when setting the Parameters. This doesnt' make sense to me, since it doesnt' even get as far as executing the procedure, so it can't be DB related.. One more weird thing is that I have an errohandler, but when this error happens, it just leaves the aborts DLL,
Any ideas??

Thanks

Dim SP_Error As String
Dim SP_ErrorMsg As String
Dim CN As New ADODB.Connection
Set CN = Connect_NonSpatial_Tables
Dim CMD As New ADODB.Command
With CMD
.ActiveConnection = CN
.CommandText = "gdb.pkg_location_planning"
.CommandType = adCmdStoredProc

.Parameters("p_type").Direction = adParamInput ' BLows here
.Parameters("p_type").Value = "R"
.Parameters("p_location_id").Direction = adParamInput
.Parameters("p_location_id").Value = lLocID
.Parameters("p_ring_id").Direction = adParamInput
.Parameters("p_ring_id").Value = 12345
.Parameters("p_radius").Direction = adParamInput
.Parameters("p_radius").Value = .5
.Parameters("p_user_id").Direction = adParamInput
.Parameters("p_user_id").Value = 99999
.Parameters("p_status_code").Direction = adParamOutput
.Parameters("p_status_desc").Direction = adParamOutput

.Execute

SP_Error = .Parameters("p_status_code").Value
SP_ErrorMsg = .Parameters("p_status_desc").Value
End With


'
'========== this is the structure

Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TYPE VARCHAR2 IN
P_LOCATION_ID NUMBER IN
P_RING_ID NUMBER IN
P_RADIUS NUMBER IN
P_USER_ID NUMBER IN
P_STATUS_CODE VARCHAR2 OUT
P_STATUS_DESC VARCHAR2 OUT

[techgnome]

Hmmm.... My Firefox "Mind Reader" Addin must not be working: It doesn't know what "blows up" means....

-tg

[RhinoBull]

Hmmm.... My Firefox "Mind Reader" Addin must not be working: It doesn't know what "blows up" means....

-tg

...but I agree with TG's sentiments - you should use more technical terminolgy, Debbie (from Florida)...

Anyway, you need to create ado parameter object and then append it to parameters collection - and that is for each parameter individually:

Dim param As ADODB.Parameter
 
Set param = cmd.CreateParameter("p_location_id", _
                                adNumeric, _
                                adParamInput, _
                                20, _
                                lLocID)
cmd.Parameters.Append param

[leinad31]

If the parameters are being retrieved automatically then the direction property ought to have been already set... try commenting out the lines which set the direction property.

[techgnome]

leinad31 - but the parameters aren't being retrieved automatically or otherwise.

-tg