Oracle stored procedures problem

[shireenrao]

Hello

 

I am trying to implement stored procedures with my vb.net application. In one part my stored procedure returns -1 if login fails. My code is -

 

 

CREATE OR REPLACE PROCEDURE LOGIN_SP

( USRNAME IN VARCHAR2, PASSWD IN VARCHAR2, ret_value out number)

AS

cnt number := 0;

BEGIN

ret_value := 0;

SELECT count(*) into cnt

FROM USERS WHERE USERNAME = USRNAME AND PASSWORD = PASSWD;

exception

when NO_DATA_FOUND then

ret_value := 1;

END;

 

 

now from vb.net how should I invoke this procedure?

I have some idea....

This is what I think I need to do..

 

 

Dim myConnString As String = "SERVER=myserver;USER ID=me;PASSWORD=myself"

Dim myConnection As New OracleConnection(myConnString)

Dim myCommand As New OracleCommand("LOGIN_SP", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

 

 

 

Now how do I pass the parameters to myCommand, and how do I get the result?

 

Thanks

 

Srini

[shireenrao]

I figured out how to do this. The code is below -

 

Try

Dim myConnection As New OracleConnection(myConnString)

Dim myCommand As New OracleCommand("LOGIN_SP", myConnection)

myConnection.Open()

myCommand.CommandType = CommandType.StoredProcedure

Dim prm1 As OracleParameter = New OracleParameter("USRNAME", OracleType.VarChar, 5)

prm1.Value = TextBox1.Text

prm1.Direction = ParameterDirection.Input

Dim prm2 As OracleParameter = New OracleParameter("PASSWD", OracleType.VarChar, 5)

prm2.Value = TextBox2.Text

prm2.Direction = ParameterDirection.Input

Dim prm3 As OracleParameter = New OracleParameter("ret_value", OracleType.Number, 1)

prm3.Direction = ParameterDirection.Output

myCommand.Parameters.Add(prm1)

myCommand.Parameters.Add(prm2)

myCommand.Parameters.Add(prm3)

myCommand.ExecuteNonQuery()

 

Dim strOut As String = "The value from Db is " & prm3.Value

 

 

MessageBox.Show(strOut)

 

myConnection.Close()

 

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

 

 

Let me know, if anybody needs an explaination.

Hope this helps somebody

 

Srinivas