Executing Stored Procedure Error

[lowbk]

Dear reader,
i am new to asp. currently i have a oracle stored procedure (chkpassword) and i need to call that procedure to verify the login. I have the below code but keep getting an error.

Dim rs, cn, result
Set cn = Server.CreateObject("ADODB.Connection&quot
cn.open "DRIVER={Microsoft ODBC for Oracle};SERVER=admd;UID=???;PWD=???"

Set rs = Server.CreateObject("ADODB.Command&quot
Set rs.ActiveConnection = cn
rs.CommandText = "CHKPASSWORD"
rs.CommandType = 4

rs.parameters.append rs.createparameter("V_LOGINID", 200, 1, 10, UCase(stdnum))

rs.parameters.append rs.createparameter("V_PASSWORD", 200, 1, 6, stdpin)

rs.parameters.append rs.createparameter("V_returnvalue", 11, 2, 30) 'return boolean

rs.Execute()

result = rs("V_returnvalue&quot.Value

If result Then
....
else
....
End If



The error is as follows:



Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC driver for Oracle]Syntax error or access violation

The error is at this line: rs.Execute()

Any idea what is causing this error?
could i have missed out any syntax error? i looked through the code again and again, cannot see anything wrong...
i searched msdn, couldn't find anything on this error...

Thanks.

 

[Genimuse]

The code you're using looks like the way I call stored procedures in SQL Server, but the couple of examples I've seen for Oracle look very different (the parameters in particular):

http://support.microsoft.com/defaul...port/kb/articles/Q229/9/19.asp&NoWebContent=1

http://www.activeserverpages.com/learn/FAQOraclestoredproc.asp

A quick Google search turns up dozens of more examples:

http://www.google.com/search?sourceid=navclient&ie=UTF-8&oe=UTF-8&q=asp+oracle+"stored+procedure"

 

[DreXor]

i see several things wrong in this code :

connection is opened, no problem

Recordset is created no problem

recordset is NOT opened at any given point

recordset is not assigned to an object variable at any given point, other than assigning a result variable

.Execute(Param) is not a method of a recordset object, it's a connection object method



hopefully pointing these items out will help in the correction of the code, unfortunately i'm not familiar with use of Oracle connections, but the ADO structure and logics need to be followed after any connection

 

[Genimuse]

D'oh! Yep, I didn't even do more than glance at the connection and the recordset, I was too busy looking at the parameters. All good points.

I recommend the Microsoft link I provided above -- it's really quite clear code.

 

[lowbk]

below is the desc of the procedure, do i still need to use recordset?

SQL> desc chkpassword
FUNCTION chkpin RETURNS BOOLEAN
Argument Name Type In/Out Default?

V_userid VARCHAR2 IN
V_passowrd VARCHAR2 IN

 

[Genimuse]

If you don't want anything back then you won't need a recordset, but I'm pretty certain a procedure called "chkpassword" would be useless if it didn't return a result.

See the Microsoft link I provided above for exactly how to do this correctly. The code is very, very clear.