Using DBNull for Strings, Dates and Numbers

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
illegal name/number woes

Using DBNull for Strings, Dates and Numbers

Post by illegal name/number woes » Fri 23 Jun 2006 22:34

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
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 26 Jun 2006 06:34

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

same problem

Post by illegal variable name » Fri 14 Jul 2006 21:50

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
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Mon 17 Jul 2006 06:42

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.

Post Reply