Problem with stored procedure using OLEDB

<i><b>Originally posted by : Lynn (Lynnkuo2000@yahoo.com)</b></i><br /><br />I have problem of using OLEDB for calling any stored procedure which has any update or insert statement and then return with select statement, but with the same stored procedure using DSN then it's okay. <br />This is the asp code. I got the error at line oRS(0). For some reason, it thinks that the recordset is already closed.<br /><br />DSN_Extranet = "PROVIDER=SQLOLEDB;SERVER=intrainfo;DATABASE=info;U ID=lynn;PWD=lynn;"<br />oConn.open DSN_EXTRANET<br /><br />strSql = "chkLoginPassword " <br />strSql = strSql & "'" & replace(rtrim(Request.Form("loginname")), "'", "''") & "', " <br />strSql = strSql & "'" & replace(rtrim(Request.Form("password")), "'", "''") & "' "<br /><br />Set oCmd = Server.CreateObject("ADODB.Command")<br />Set oCmd.ActiveConnection = oConn<br />oCmd.CommandType = adCmdStoredProc<br />oCmd.CommandText = strSql<br />Set oRS = oCmd.Execute <br /><br />strName = oRS(0)<br /><br /><br /><br />Error Type:<br />ADODB.Recordset (0x800A0CC1)<br />Item cannot be found in the collection corresponding to the requested name or ordinal.<br /><br /><br />Here is my stored procedure. If I comment it out the update statement then everything is fine.<br /><br />Thanks for any help.<br /><br /><br /><br />create procedure dbo.chkLoginPassword ( @pUserid char(20), @pPassword char(20) )<br />as <br />begin<br />declare @xxx char(20), @rand char(30), @userid char(20), @cid int<br /><br />if exists ( select userid from users<br /> where userid = @pUserid<br /> and password = @pPassword )<br />begin<br /><br /><br />select @xxx = convert(char(20), getdate(), 14)<br />select @rand = substring(convert( char(30), rand(), 2),3,12) + substring(@xxx, 4,2) + substring(@xxx, 7,2) + substring(@xxx, 10,3) + substring(convert( char(30), rand(), 2),3,12)<br /><br />update Users<br />set EncryptUserid = @rand,<br />LastLoginTime = getdate()<br />where userid = @pUserid<br />and password = @pPassword <br /><br />select @rand<br /><br />end <br />else <br />begin<br />select -1<br />end <br /><br />end