Raghupathi, This is an existing SF in our DB that we use. I am currently migrating the classic ASP application to VB.NET. It would not make sense to me if Visual Studio doesn't support the ability to call SF's.
Thanks On Feb 9, 12:49 am, Raghupathi Kamuni <[email protected]> wrote: > You created a FUNCTION instead of a stored procedure........ > > > > On Tue, Feb 9, 2010 at 12:23 AM, Will <[email protected]> wrote: > > I am new to VB.NET <http://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.- Hide quoted text - > > - Show quoted text -
