Problem with insert into Oracle from vb 6

[trc]

Hi.

I am having problems with an insert statement that I build in VB6 and send to an oracle DB. Here is the sql insert

"into tempGeounitDBData(select distinct DRLHL_EX_REF_NO, GEO_UNT_INTR_ID, INTR_ST_DP, INTR_END_DP from GEO_UNT_INTR, DRLHL_EX where DRLHL_EX.DRLHL_EX_ID = GEO_UNT_INTR.DRLHL_ID and trim(upper(drlhl_ex_ref_no)) in ( ' 7080', ' 7081’,...));"

I can write the sql out to a file and then run it in Toad after I remove the double quotes from either end. But when I run it in my app I get the following error: “Requested operation requires an OLE DB session object, which is not supported by the current provider.”

Here is my code for the DB connection:

strCnn = "dsn=OP;" & mstrConnectionString
strCnn = "dsn=" & mstrDB & ";" & mstrConnectionString
Acommand.ActiveConnection = cnnADODB

cnnADODB.Open strCnn
Acommand.CommandText = lsSql
Acommand.CommandType = adCmdText
Acommand.Execute

I am trying to write several thousand rows to the db. Is this the problem? If so why can I run it in Toad and not from my app? I assume that the error message is telling me the correct error. If so what type of connection to the db do I use.

Thanks,
TRC

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[fredericofonseca]

Active connection of the Acommand object must be set AFTER the conneciton object is opened, not before

strCnn = "dsn=OP;" & mstrConnectionString
strCnn = "dsn=" & mstrDB & ";" & mstrConnectionString

cnnADODB.Open strCnn

Acommand.ActiveConnection = cnnADODB


Acommand.CommandText = lsSql
Acommand.CommandType = adCmdText
Acommand.Execute

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[trc]

Doh!

Thanks.

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->