Solved

ADODB OraOLEDB.Oracle and NULL value in Prepared Statement

Posted on 2006-06-15
4
712 Views
Last Modified: 2008-02-01
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.InteropServices.COMException (0x80004005): 
   at ADODB.CommandClass.Execute(Object& RecordsAffected, Object& Parameters, Int32 Options)
   at Correspondence.ClassMassMail.InsertIntoTables_Mass(String& 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\ClassMassMail.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 ?
0
Comment
Question by:bigboy1122
  • 2
4 Comments
 
LVL 1

Author Comment

by:bigboy1122
ID: 16914055
As it turns out it seems to be the parameter itself.  It is being defined as an adVariant (12)

        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.
0
 
LVL 1

Author Comment

by:bigboy1122
ID: 16919804
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.
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 17110472
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Store results in vb.net 3 30
Help with exporting to excel 4 44
run a stored procedure from vb.net 1 30
vb.net datagrid point 4 16
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question