OracleClient, parameters and string

[Sven Jacobs]

Dear newsgroup,

while developing an ASP.NET application I came across a problem. I am
collecting data from an Oracle 8i database using parameters. Unfortunately
I get an empty result set if the parameter is a string. It works with
integer!

Assuming conn is an OracleConnection object:

--snip--

OracleCommand cmd = new OracleCommand( "SELECT * FROM TEST " +
"WHERE ID = :name",
conn );

cmd.Parameters.Add( "name", "XY" );
OracleDataReader reader = cmd.ExecuteReader();

--snip--

I've tried to explicitly convert the parameter to OracleString:
cmd.Parameters.Add( "name", new OracleString( "XY" ) );

The affected column is of type CHAR(2). The .NET framework is version
1.1.4322 SP1.

 

[Sven Jacobs]

I've tried to explicitly convert the parameter to OracleString:

cmd.Parameters.Add( "name", new OracleString( "XY" ) );

OracleParameter param = new OracleParameter( "name",
OracleType.Char );

param.Value = "XY";
cmd.Parameters.Add( param );

does not work either with OracleType.Char or OracleType.VarChar.

 

[Ed Douglas]

try replacing this line:

cmd.Parameters.Add( "name", "XY" );

with

cmd.Parameters.Add( ":name", "XY" );

 

[Sven Jacobs]

try replacing this line:

with

cmd.Parameters.Add( ":name", "XY" );

Nope, doesn't work. My source code example was correct as it works with
numeric fields.

 

[Rogas69]

maybe you should wrap the parameter in apostrophes? just guess.
something like
OracleCommand cmd = new OracleCommand( "SELECT * FROM TEST " +
"WHERE ID = ':name'",
conn );

peter

 

[oraclevsmicrosoft]

Here is as sample code using the "?" syntax with Oledb objects
http://oraclevsmicrosoft.blogspot.com/2005/03/quotes-parameters.html

it works fine for strings and numbers.

HTH

http://oraclevsmicrosoft.blogspot.com

 

[Sven Jacobs]

while developing an ASP.NET application I came across a problem. I am

collecting data from an Oracle 8i database using parameters. Unfortunately
I get an empty result set if the parameter is a string. It works with
integer!

Well the problem seems to be related to Oracle 8i. I've installed an Oracle
9i server on my machine and now string parameters do work! I guess the
(ODBC?) driver of Oracle 8i has a bug or there's something wrong in the
OracleClient assembly when dealing with this version of Oracle.