View Single Post
  #1 (permalink)  
Old December 18th, 2003, 03:25 PM
learnvb learnvb is offline
Registered User
Join Date: Dec 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default VB arises an error when dealing with database ..

Hi experts!
I'm new to VB and oracle stored procedure. I had created a
VB code to execute a stored procedure in Oracle,, and in the stored procedure, a link to another oracle database is created.
I created a stored procedure in Oracle database "A" and want to get some data information from oracle database "EVF16" which I don't have any right to create a store procedure or nothing...
Here is my VB code:
'I had something like database connection here...

Dim Cmd As New ADODB.Command
' Use a connection string or a Connection object.
Cmd.ActiveConnection = "Provider=MSDASQL;DSN=" & strDBNAME & ";UID=" & strID & ";PWD=" & strPW & ";DBQ=" & strDBNAME & ";ASY=OFF;"
Cmd.CommandText = "test2"
Cmd.CommandTimeout = 30
Cmd.CommandType = adCmdStoredProc

and here's my stored procedure:
/* Declare string to store SQL Statement*/
v_sql varchar2(1000);

/* Declare string to store SQL Statement*/
v_sql varchar2(1000);

delete from AV_project where proj in (Select proj from av_project@evf16 where proj='xxxx' );
v_sql := 'execute test2';
execute immediate v_sql;

The error's something like : Oracle transaction is not open..or somethign like that...

Please help me..thanks

ANd even I've to add BeginTrans and CommitTrans but still not work.