DbCommand against Oracle fails with string parameters

  • Thread starter Thread starter doug.setzer
  • Start date Start date
D

doug.setzer

I have this code:
Dim cmd As DbCommand = Me.Factory.CreateCommand() '** this is a valid
OracleFactory object
Dim param As DbParameter = Nothing
Dim sql As String = ""

sql = "SELECT ard.*" & Environment.NewLine & _
"FROM MyTable ard" & Environment.NewLine & _
"WHERE ard.Client_Nbr = :ClientNbr" & Environment.NewLine

param = Me.Factory.CreateParameter()
param.DbType = DbType.String
param.Direction = ParameterDirection.Input
param.ParameterName = "Client_Nbr"
param.Value = "Yaba"

cmd.Parameters.Add(param)

cmd.Connection = Me.Connection '** this is a valid DbConnection to an
Oracle database

cmd.CommandText = sql

dr = cmd.ExecuteReader()

When I call the cmd.Execute reader, it fails with:
ORA-12571: TNS:packet writer failure

If I use a numeric parameter or remove the parameter and hard-code the
SQL statement's value for Client_Nbr, everything works fine.
Additionally, this works fine on my local box and fails on the server
(as far as I'm aware, they should be the same).

Any help is appreciated.

-Doug
 
I changed this:
param.DbType = DbType.String

to:
param.DbType = DbType.AnsiString

It works; I don't know why. (sarcastically...) Great.

Thanks for reading. Input or insight is appreciated.

-Doug
 
I changed this:
param.DbType = DbType.String

to:
param.DbType = DbType.AnsiString

It works; I don't know why. (sarcastically...) Great.

Thanks for reading. Input or insight is appreciated.

-Doug
Click to expand...

You didn't post a CREATE TABLE script, so I assume you are using VARCHAR2
for your column datatype. This is an ANSI string, so you are correct that
you must set the parameter DbType to AnsiString. If it was NVARCHAR then you
would use DbType.String.

HTH
 
Back
Top