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