stored procs, asp, and tearing my hair out

not that i find ASP unreliable or anything

[natty]

hi all,

i know this will be because of my somewhat minimal understanding of how asp , ADO 'works' together..

let me explain

i have a sp that takes a param
like so

q="EXEC usp_HUK_getPermsRecordset"
set oRS=Server.CreateObject("ADODB.RecordSet")
oRS.Open q, oConn, adOpenDynamic, adLockOptimistic

then within a loop i do afew

oRS.AddNew array("Code", "CV_ID", "PermKey", "IsDeleted"), array(theCode, session("custid"), permCode, isDeleted)

and finally a
oRS.UpdateBatch adAffectAll
oRS.Close
set oRS=nothing

now this was working fine until i made a nother similar page and changed the SP to take an argument (bad choice of words lol)
so now it read like so

q="EXEC usp_HUK_getPermsRecordset @permType='RECORD'"

now the script that had worked before now failed..
it seems simply because i am now passing a param to the SP.

i was told that this mayt be because i am using plain old "EXEC blah @arg=val"

and i should be doing something more along the lines of

dim objCmd
Set objCmd = server.CreateObject("ADODB.Command")
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = oConn
objCmd.CommandText = "usp_HUK_getPermsRecordset"

dim objParam
Set objParam = objCmd.CreateParameter("@permType",adVarChar,adParamInput,10,myVar)

objCmd.Parameters.Append objParam

set oRS=Server.CreateObject("ADODB.RecordSet")
oRS.CursorType = adOpenDynamic
oRS.LockType = adLockBatchOptimistic

then continue with the

oRS.AddNew array("Code", "CV_ID", "PermKey", "IsDeleted"), array(theCode, session("custid"), permCode, isDeleted)

and the
oRS.UpdateBatch adAffectAll
oRS.Close
set oRS=nothing

but when trying this i am greeted with the error

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

but..
but..
it was working a minute ago.. and i have tried all the cursor types and everything else i can think of
what gives?

can anyone explain the correct , possibly using commands way of doing this..

what i cant fathom is how the lovely ASP can fall over on the same 'code' simply because of passing a param.
so like i said maybe this is because i am attaching the parameter wrongly.

cheers,

nathan

[aspdaddy]

If its a single input parameter just use an adodb connection & an implicit recordset, no need for command objects

set objRS = objConn.Execute ("exec dbo.spname " & strParam )

Where strParam is just the value - SQL expects thenm in the right order so you diont need to name them. If you have more than 1 parameter, just separate them with commas and put strings in single quotes;

exec dbo.spname 1,'Test',2