Hi, I'm new in Visual Studio to, but I'm using C#
when I want retrieve a output parameter from a stored procedure I use this command SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "insert ..."; cmd.Parameters.AddWithValue(...); conn.Open(); //this is te command parametertoretriev = cmd.ExecuteScalar(); hope help you Atenciosamente, A.Fernando Oliveira "Life short is. Play hard." - @mestre_yoda 2010/2/8 Will <[email protected]> > I am new to VB.NET development and would like to know how to execute a > Stored Function. > > We are using Visual Studio 2005, MS.Net framework version 2.0. > > The SF takes in 6 parameters and returns a number, noted below is the > beginnig of the SF. I need to check the return value from the SF. > > The application bombs when it gets to cmd.ExecuteNonQuery(), I receive > message: > > Additional information: ORA-06550: line 1, column 7: > PLS-00221: 'SF_VERIFY_USERID_PASSWORD' is not a procedure or is > undefined. > > All DB objects are visible to the schema login account. > > I've done my research on the web and come up with the code below. > In addition I have noticed when adding New OracleParameter() that it > already has the parameter direction (ParameterDirection.Input), do I > need to add it again as noted below in my code(oraParameter.Direction > = ParameterDirection.Input) ? > > > //Oracle SF > CREATE OR REPLACE FUNCTION SF_VERIFY_USERID_PASSWORD ( > USR_ID_IN IN VARCHAR2, > CURR_PW_IN IN VARCHAR2, > NEW_PW_IN IN VARCHAR2, > CONFIRM_PW_IN IN VARCHAR2, > PW_CHANGE_IN IN VARCHAR2, > SECURITY_LEVEL_IN IN VARCHAR2) > > RETURN NUMBER > AS > ret_val NUMBER := 1; > > ...code > > RETURN ret_val; > > > Noted below is the VB.net code that I have: > > Function execute_SF_VERIFY_USERID_PASSWORD > (ByVal InUserID As String, ByVal InPassWord As String, ByVal new_pw_in > As String, ByVal confirm_pw_in As String, ByVal pw_change_in As > String, ByVal security_lvl_in As String) As Integer > > Dim sf_rtn_value As Integer = 0 > Dim conn As OracleConnection = New > OracleConnection(SQLHelper.ConnectionString()) > > Dim da As System.Data.OracleClient.OracleDataAdapter > Dim ds As System.Data.DataSet > > Dim oraParameter As OracleParameter > > conn.Open() > Dim cmd As New OracleCommand > Dim rtn_value As Integer = 0 > > cmd.Connection = conn > cmd.CommandType = CommandType.StoredProcedure > cmd.CommandText = "SF_VERIFY_USERID_PASSWORD" > > oraParameter = New OracleParameter("USR_ID_IN", > OracleType.VarChar, ParameterDirection.Input) > oraParameter.Value = InUserID > oraParameter.Direction = ParameterDirection.Input > cmd.Parameters.Add(oraParameter) > > oraParameter = New OracleParameter("CURR_PW_IN", > OracleType.VarChar, ParameterDirection.Input) > oraParameter.Value = InPassWord > oraParameter.Direction = ParameterDirection.Input > cmd.Parameters.Add(oraParameter) > > oraParameter = New OracleParameter("NEW_PW_IN", > OracleType.VarChar, ParameterDirection.Input) > oraParameter.Value = new_pw_in > oraParameter.Direction = ParameterDirection.Input > cmd.Parameters.Add(oraParameter) > > oraParameter = New OracleParameter("CONFIRM_PW_IN", > OracleType.VarChar, ParameterDirection.Input) > oraParameter.Value = confirm_pw_in > oraParameter.Direction = ParameterDirection.Input > cmd.Parameters.Add(oraParameter) > > oraParameter = New OracleParameter("PW_CHANGE_IN", > OracleType.VarChar, ParameterDirection.Input) > oraParameter.Value = pw_change_in > oraParameter.Direction = ParameterDirection.Input > cmd.Parameters.Add(oraParameter) > > oraParameter = New OracleParameter("SECURITY_LEVEL_IN", > OracleType.VarChar, ParameterDirection.Input) > oraParameter.Value = security_lvl_in > oraParameter.Direction = ParameterDirection.Input > cmd.Parameters.Add(oraParameter) > > oraParameter = New OracleParameter() 'Return value > oraParameter.Direction = ParameterDirection.ReturnValue > > cmd.ExecuteNonQuery() > rtn_value = oraParameter.Value > > > conn.Close() > > Return rtn_value > > End Function > > Thank you, > > William. >
