PLEASE HELP WITH ORACLE!

[Karl Piper]

Hello,

I'm putting the entire sub below and asking if anyone can help answer what
is wrong with it. I am receiving an error at the first parameter line:

OraCommand.Parameters.Add("v_ACT_EMAIL_ADDRESS1_IN",
OleDb.OleDbType.VarWChar, 60)

saying:

'BEGIN ERROR DESCRIPTION
Description: An error occurred during the compilation of a resource required
to service this request. Please review the following specific error details
and modify your source code appropriately.

Compiler Error Message: BC30451: Name 'OleDb' is not declared.

Source Error:

Line 82: 'OraCommand.CommandType = CommandType.StoredProcedure
Line 83: 'INPUTS
Line 84: OraCommand.Parameters.Add("v_ACT_EMAIL_ADDRESS1_IN",
OleDb.OleDbType.VarWChar, 60)
Line 85: OraCommand.Parameters("v_ACT_EMAIL_ADDRESS1_IN").Direction
= ParameterDirection.Input
Line 86: OraCommand.Parameters("v_ACT_EMAIL_ADDRESS1_IN").Value =
Email.Text

'END ERROR DESCRIPTION

Can anyone please let me know what I am omitting here...this is driving me
crazy!


'THIS IS THE CODE THIS IS THE CODE
'FIRST I AM SETTING THE CONNECTION
dim OraConn
OraConn = New System.Data.OleDb.OleDbConnection
'
'OraConn
'
OraConn.ConnectionString = "Provider=MSDAORA.1;Password=mypass;User
ID=theuser;Data=emcu.myplace"

Dim OraCommand As New
System.Data.OleDb.OleDbCommand("CU_MARKETING.IMPORT_PROCESS.OPT_IN",
OraConn)
Dim retVal As Integer
OraCommand.CommandType = 4 'used instead of below
'OraCommand.CommandType = CommandType.StoredProcedure

'INPUTS
OraCommand.Parameters.Add("v_ACT_EMAIL_ADDRESS1_IN",
OleDb.OleDbType.VarWChar, 60) 'GET ERROR ON THIS LINE
OraCommand.Parameters("v_ACT_EMAIL_ADDRESS1_IN").Direction =
ParameterDirection.Input
OraCommand.Parameters("v_ACT_EMAIL_ADDRESS1_IN").Value = Email.Text

OraCommand.Parameters.Add("v_ACT_EMAIL_ADDRESS2_IN",
OleDb.OleDbType.VarWChar, 60)
OraCommand.Parameters("v_ACT_EMAIL_ADDRESS2_IN").Direction =
ParameterDirection.Input
OraCommand.Parameters("v_ACT_EMAIL_ADDRESS2_IN").Value = Email.Text

OraCommand.Parameters.Add("v_FIRST_NAME_IN",
OleDb.OleDbType.VarWChar, 50)
OraCommand.Parameters("v_FIRST_NAME_IN").Direction =
ParameterDirection.Input
OraCommand.Parameters("v_FIRST_NAME_IN").Value = FName.Text

OraCommand.Parameters.Add("v_LAST_NAME_IN",
OleDb.OleDbType.VarWChar, 50)
OraCommand.Parameters("v_LAST_NAME_IN").Direction =
ParameterDirection.Input
OraCommand.Parameters("v_LAST_NAME_IN").Value = LName.Text

OraCommand.Parameters.Add("v_ZIP_CODE_IN", OleDb.OleDbType.VarWChar,
10)
OraCommand.Parameters("v_ZIP_CODE_IN").Direction =
ParameterDirection.Input
OraCommand.Parameters("v_ZIP_CODE_IN").Value = Zip.Text

OraCommand.Parameters.Add("v_REMOVE_FLAG_IN",
OleDb.OleDbType.Numeric, 4)
OraCommand.Parameters("v_ZIP_CODE_IN").Direction =
ParameterDirection.Input
OraCommand.Parameters("v_ZIP_CODE_IN").Value = 0

'OUTPUTS
OraCommand.Parameters.Add("v_SUCCESS_OUT", OleDb.OleDbType.Numeric)
OraCommand.Parameters("v_SUCCESS_OUT").Direction =
ParameterDirection.Output
OraConn.Open()
OraCommand.ExecuteNonQuery()
retVal = OraCommand.Parameters("v_SUCCESS_FLAG_OUT").Value
OraConn.Close()
MsgOut.Text = "Returned Code: " & retVal

 

[Bruce Whitehouse]

Karl

On line 84 you seem to be referencing the object 'OleDb.OleDbType.VarWChar',
forgive me if I'm wrong, but shouldn't this be
'System.Data.OleDb.OleDbType.VarWChar'?

Wouldn't it be quicker to use the OracleClient instead of the standard OleDB
client?

Bruce

 

[Karl Piper]

Bruce,

Thanks for the quick reply. Can you please show me in a small example of
how I would use the OracleClient in this situation?

Karl

 

[Cowboy \(Gregory A. Beamer\)]

Here is an article that helped me with .NET and Oracle. Pay attention to the
OleDb sections, if you are sticking with the OleDb client.

http://msdn.microsoft.com/library/en-us/dndotnet/html/manprooracperf.asp

In general, I set up the parameter direction, et al, on the object when it
is created, like so:

OleDbParameter param1 = new OleDbParameter("USER_NUMBER",OleDbType.VarChar ,
250, ParameterDirection.Input, false, 0, 0,
"USER_NUMBER", DataRowVersion.Default, userNumber);

I also end up with a parameter object that is explicitly created, rather
than have to go at the parameter, by name, later on. This gives me the
ability to use:

param1.Direction = ParameterDirection.Input

The article mentioned is written in C#, so you will have to watch a bit to
get the gist.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Bruce Whitehouse]

Yup,

Just did this out yesterday!

DATABASE CLASS
Imports System

Imports System.Data

Imports System.Data.OracleClient

Public Class clsDatabase

Private _connection As OracleConnection

Private _connection_string As String

Private _SQL As String

Private _error As String

Public Property ConnectionString() As String

Get

Return _connection_string

End Get

Set(ByVal value As String)

_connection_string = value

End Set

End Property

Public Property strError() As String

Get

Return _error

End Get

Set(ByVal value As String)

_error = value

End Set

End Property

Public Property strSQL() As String

Get

Return _SQL

End Get

Set(ByVal value As String)

_SQL = value

End Set

End Property

Public Property objConnection() As Object

Get

Return _connection

End Get

Set(ByVal value As Object)

_connection = value

End Set

End Property

Public Function createConnection()

_connection = New OracleConnection(_connection_string)

' _connection = New OdbcConnection(_connection_string)

End Function

Public Function closeConnection()

_connection.Close()

_connection = Nothing

End Function

Public Function addUnallocatedIDs(ByRef arrUnallocated As ArrayList, ByVal
intID As Integer, ByVal boolAtStart As Boolean)

If boolAtStart Then

arrUnallocated.Insert(0, intID)

Else

arrUnallocated.Add(intID)

End If

End Function

Public Function removeUnallocatedIDs(ByRef arrUnallocated, ByVal intID)

arrUnallocated.Remove(intID)

End Function

End Class

/DATABASE CLASS

CALLING PACKAGE
Public Class clsCallPackage : Inherits clsDatabase

Private Function boolClearTable(ByVal strIdsConnectionString As String,
ByVal objTerminal As clsTerminal, ByVal strTable As String) As Boolean

Dim boolReturn As Boolean

Dim objCommand As OracleCommand

Dim strPackage As String

boolReturn = True

strPackage = getPackageName(strTable) & ".PRC_DELETE"

objCommand = New OracleCommand(strPackage, objConnection)

objCommand.CommandType = CommandType.StoredProcedure

objCommand.Parameters.Add(New OracleParameter("IN_TERMINAL_ID",
OracleType.Int32)).Direction = ParameterDirection.Input

objCommand.Parameters("IN_TERMINAL_ID").Value = objTerminal.TerminalID

objCommand.Parameters.Add(New OracleParameter("IN_DELETE_ALL",
OracleType.Int32)).Direction = ParameterDirection.Input

objCommand.Parameters("IN_DELETE_ALL").Value = 1

objCommand.Parameters.Add(New OracleParameter("out_errMsg",
OracleType.VarChar)).Direction = ParameterDirection.Output

objCommand.Parameters("out_errMsg").Size = 50

Try

objConnection.Open()

boolReturn = objCommand.ExecuteNonQuery

objConnection.close()

Catch e As Exception

boolReturn = False

strError = CStr(e.ToString()) & " " &
CStr(objCommand.Parameters("out_errMsg").Value)

End Try

Return boolReturn

End Function

/CALLING PACKAGE


Hope this helps,
Bruce