bigboy1122
asked on
ADODB OraOLEDB.Oracle and NULL value in Prepared Statement
I have an old VB6 app that was converted over to .NET. Most of the code still uses ADODB. We have upgraded our Oracle server to 10g which MSDAORA doesn't support without SP2.
I am finding that inserting a NULL value is causing the .execute to blowup.
Example
If IsDBNull(sMissing_Item) Or sMissing_Item = Nothing Then
gobjaPrm(5).Value = DBNull.Value
Else
gobjaPrm(5).Value = vTemp
End If
So when it NULL value is inserted I get a weird error
System.Runtime.InteropServ ices.COMEx ception (0x80004005):
at ADODB.CommandClass.Execute (Object& RecordsAffected, Object& Parameters, Int32 Options)
at Correspondence.ClassMassMa il.InsertI ntoTables_ Mass(Strin g& sPath, String& sCocode, String& sComp_Name, String& sSt_of_Dom, Int32& lUse_Code_ID, String& sMissing_Item, String& sCompany_Contact, Int32& sFiling_Period) in C:\Data\VB_NET\CORR.NET\Cl assMassMai l.vb:line 4140
The program '[2232] Corr.exe' has exited with code 0 (0x0).
The blocks are actually the error. If you change the code to populate the field with a value instead of a NULL, everything works.
So the question is, is there another way to insert a NULL value with OraOLEDB.Oracle ?
I am finding that inserting a NULL value is causing the .execute to blowup.
Example
If IsDBNull(sMissing_Item) Or sMissing_Item = Nothing Then
gobjaPrm(5).Value = DBNull.Value
Else
gobjaPrm(5).Value = vTemp
End If
So when it NULL value is inserted I get a weird error
System.Runtime.InteropServ
at ADODB.CommandClass.Execute
at Correspondence.ClassMassMa
The program '[2232] Corr.exe' has exited with code 0 (0x0).
The blocks are actually the error. If you change the code to populate the field with a value instead of a NULL, everything works.
So the question is, is there another way to insert a NULL value with OraOLEDB.Oracle ?
ASKER
I found the solution. The problem is this
manPrmTyp(x)=12
What that is saying is that that index of parameters is going to be an adVariant type. The adVariant type would accept the null but when it got the .execute(), the program would throw an exception which was unicode (in otherwards just squares).
Since the parameters that used 12 were all strings we switched them to 8 which is a adBSTR (Indicates a null-terminated character string)
That seem to fix the problem and all the .execute to run the Insert Statement.
manPrmTyp(x)=12
What that is saying is that that index of parameters is going to be an adVariant type. The adVariant type would accept the null but when it got the .execute(), the program would throw an exception which was unicode (in otherwards just squares).
Since the parameters that used 12 were all strings we switched them to 8 which is a adBSTR (Indicates a null-terminated character string)
That seem to fix the problem and all the .execute to run the Insert Statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
manPrmTyp(0) = 131 : manPrmTyp(1) = 131 : manPrmTyp(2) = 12
manPrmTyp(3) = 12 : manPrmTyp(4) = 131
manPrmTyp(5) = 12 : manPrmTyp(6) = 12
The paarameters that are adNumeric can contain a null value. The variants are the ones throwing fits.
I could fix teh problem by check the data before hand and if it meets a condition, use a different function with a different insert statement. Or I could just redo this function in ADO.NET. I am just trying to avoid that.