Using DBNull for Strings, Dates and Numbers

[illegal name/number woes]

I'm getting the illegal name/number problem on an INSERT statement. I think i've track it down to where I insert a null value for one of my parameters.

I was wondering what the correct format of using DBNull for identifying the parameter.

Code: Select all

		private OracleCommand CreateCommand(String szQuery, Parameter[] aParameters)
		{
			OracleParameter l_oOracleParameter; 
			OracleCommand l_oOracleCommand = new OracleCommand(szQuery, m_oOracleConnection, m_oOracleTransaction);
			l_oOracleCommand.CommandType = CommandType.Text;
			
			for (int i = 0; i < aParameters.Length; i++) 
			{
				l_oOracleParameter = new OracleParameter(":" + aParameters[i].Name, ConvertType(aParameters[i]), aParameters[i].Size);
				l_oOracleParameter.Direction = ParameterDirection.Input;						

				if (aParameters[i].Data is DateTime &&
					aParameters[i].Data.Equals(DateTime.MaxValue)) 
				{
					l_oOracleParameter.Value = DBNull.Value;
				}
				else
				{
					if (IsEmptyData(aParameters[i])) 
					{
							l_oOracleParameter.IsNullable = true;
							l_oOracleParameter.Value = DBNull.Value;
					}
					else
						l_oOracleParameter.Value = aParameters[i].Data;
				}

				l_oOracleCommand.Parameters.Add(l_oOracleParameter);
				log.Warn("'" + l_oOracleParameter.Value + "'");
			}

			return l_oOracleCommand;
		}

ConvertType checks my parameter type and figures out the equivalent type to use in Oracle. For instance, if my paramter is a VarChar and since I'm not using multibyte - i return OracleDBType.VarChar

IsEmpty checks if my parameter is null, and if it is, I try setting the new parameter I'm creating to DbNull. Still getting some problems though...

Do I also need to do that IsNullable every time?

any ideas?

[Alexey]

Omit adding a colon to a parameter name in the following line:

Code: Select all

l_oOracleParameter = new OracleParameter(":" + aParameters[i].Name, ConvertType(aParameters[i]), aParameters[i].Size); 

[illegal variable name]

Hi,

Thanks for your suggestion about removing the : in that line.
It has fixed my problem while using OraDirect with the Oracle Client Tools installed.

However, when I change my connection string to use direct mode as per your documentation file on a system that doesn't have the Oracle Client Tools installed it doesn't work for any queries that pass parameters:

My connection string:

databaseConnection="Data Source=172.23.45.34;Direct=true;Port=1521;SID=XE;User Id=myuser;Password=mypass;"

The application still connects, but it doesn't seem to want to run any queries that pass parameters. All the normal SELECT, INSERT queries in our application work great, it's just ones like this that have issues. From our log:

7022: SQL statement 'SELECT PersonKey, FirstName, LastName FROM Person WHERE PersonKey = :PersonKey AND DeletionDate IS NULL'
3011: Connection 'Data Source=172.23.45.34;Direct=true;Port=1521;SID=XE;User Id=myuser;'
3010: Connection State 'Open'
3017: Transaction exists
Parameters: 1
3015: Para 'PersonKey' set to 'c119695f-8880-48d6-b672-12765468de23'
Code: 1036
CoreLab.Oracle.OracleException
at CoreLab.Oracle.a.a(az[] A_0, Byte[] A_1)
at CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at CoreLab.Common.DbCommandBase.b(CommandBehavior A_0)
at CoreLab.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at CoreLab.Oracle.OracleCommand.ExecuteReader()


Any ideas? Thanks

[Paul]

Please check the number of parameters in your SQL statement and the number of parameters in Parameters collection (duplicate parameters in SQL statement must be present once in Parameters). Check that order of parameters in SQL statement corresponds to the order of parameters in Parameters collection. Check that names of parameters are the same.