using ExecuteNonQuery to return a return parameter
userLynxJan 25 2010 — edited Jan 25 2010I'm using ExecuteNonQuery in VB.NET to call a stored proc that returns a varchar value as follows:
function estimated_end_date (
CssAcctNo in acct.rat_full_css_acct_no%type,
ServiceType in marketer_account.mka_service_type%type,
RqstEndDt in varchar2,
DataSourceCd in de_enroll_que.deq_data_source_cd%type)
return varchar2;
I'm not sure how to code the .NET to return this varchar2.
If ConnectedToDatabase() Then
Dim mCommand = New OracleCommand()
Using mCommand
mCommand.Connection = mConnection
mCommand.CommandText = "SP_VALIDATION.ESTIMATED_END_DATE"
mCommand.CommandType = CommandType.StoredProcedure
mCommand.BindByName = True
Dim mAccountNumber As New OracleParameter
mAccountNumber.OracleDbType = OracleDbType.Varchar2
mAccountNumber.Direction = ParameterDirection.Input
mAccountNumber.ParameterName = "CssAcctNo"
mAccountNumber.Value = CSSAccountNumber
mCommand.Parameters.Add(mAccountNumber)
Dim mServiceType As New OracleParameter
mServiceType.OracleDbType = OracleDbType.Varchar2
mServiceType.Direction = ParameterDirection.Input
mServiceType.ParameterName = "ServiceType"
mServiceType.Value = ServiceType
mCommand.Parameters.Add(mServiceType)
Dim mRqstEndDt As New OracleParameter
mRqstEndDt.OracleDbType = OracleDbType.Varchar2
mRqstEndDt.Direction = ParameterDirection.Input
mRqstEndDt.ParameterName = "RqstEndDt"
mRqstEndDt.Value = requestedEndDt.ToShortDateString
mCommand.Parameters.Add(mRqstEndDt)
Dim mDataSource As New OracleParameter
mDataSource.OracleDbType = OracleDbType.Varchar2
mDataSource.Direction = ParameterDirection.Input
mDataSource.ParameterName = "DataSourceCd"
mDataSource.Value = dataSourceCd
mCommand.Parameters.Add(mDataSource)
'///// not sure how to use the ReturnValue parameter? /////
Dim cycleDate As New OracleParameter
cycleDate.OracleDbType = OracleDbType.Varchar2
cycleDate.Direction = ParameterDirection.ReturnValue
cycleDate = mCommand.ExecuteNonQuery() '???? ExecuteNonQuery is expecting an integer?????
End Using
End If
Can anyone help?