Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with insert into Oracle from vb 6

Status
Not open for further replies.

trc

MIS
Nov 27, 2000
126
CA
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.-->
 
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
 
Doh!

Thanks.

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

Part and Inventory Search

Sponsor

Back
Top