Solved

vb.net call oracle stored procedure, not work

Posted on 2010-08-26
28
867 Views
Last Modified: 2013-11-27
Hi,
I just try to upgrade my program from vb6 to vb.net .
issue about call oracle stored procedure from vb.net
it return nothing.....
*I don't want to change ado.net, because it will big chance on my new vb.net project*
somebody could help me?

Thank you!
Francis SZE
procedure nextinvoicenum
( invoicenum out number
) as
nextinvoiceid number;
begin
--  select invoice_seq.currval +1 into nextinvoiceid from dual;
  select max(tid) + 1 into invoicenum from invoice;
  invoicenum := NVL (invoicenum,1);
  DBMS_OUTPUT.PUT_LINE('INVOICENUM = ' || INVOICENUM);
end nextinvoicenum;


==================================================================

connObj = getConnObj
cmdObj = New ADODB.Command

With cmdObj

.let_ActiveConnection(connObj)
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = "invoicepackage.nextinvoicenum"
.Parameters.Append(.CreateParameter("invoicenum", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamOutput, 255, 0))
End With
		
cmdObj.Execute()

getNextInvoiceIndex = cmdObj.Parameters("invoicenum").Value
MessageBox.Show(getNextInvoiceIndex )

Open in new window

0
Comment
Question by:fsze88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
  • 3
28 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 33530177
Do you get an error back ?
If you call it directly on oracle, do you get a return ?


try
   With cmdObj
       .ActiveConnection = connObj
       .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
       .CommandText = "invoicepackage.nextinvoicenum"
       .Parameters.Append(.CreateParameter("invoicenum", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamOutput, 255, 0))
   End With

   getNextInvoiceIndex = cmd.Parameters(0).Value
Catch x As Exception
   messagebox.Show(ex.ToString())
end try 

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33530393
no error , but return nothing
yes, I work on pl/sql
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33530420
Can you try it with changing the parameter-type
ADODB.ParameterDirectionEnum.adParamReturnValue instead of
ADODB.ParameterDirectionEnum.adParamOutput
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33530422
please try this:_
try
   Dim p as ADODB.Paramter;
   With cmdObj
       .ActiveConnection = connObj
       .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
       .CommandText = "invoicepackage.nextinvoicenum"
       p = .CreateParameter("invoicenum", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamOutput)
       .Parameters.Append(p)
   End With

   getNextInvoiceIndex = p.Value
Catch x As Exception
   messagebox.Show(ex.ToString())
end try 

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33530505
sorry, same
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33530609
>it return nothing.....
just to clarify: where exactly does it return "nothing" ?
0
 
LVL 15

Author Comment

by:fsze88
ID: 33530618
messagebox.show cmdObj.Parameters("invoicenum").Value

a messagebox contain blank
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33530636
same if you do:

messagebox.show(p.Value)

after the .execute?
I don't see the issue... what is the connection string looking like?
0
 
LVL 15

Author Comment

by:fsze88
ID: 33530695
messagebox.show(p.Value) ,same
nothing, I means blank

if messagebox.show(p.Value.toString)
It give another err (see image)

the connection should done work, also please note I upgrade ado to 6.0 library

please help
Untitled.png
0
 
LVL 15

Author Comment

by:fsze88
ID: 33530715
it works on modify a module (small change)
but on my project, there many forms are using old ado method
so, I ask this again.
please understand.
0
 
LVL 15

Author Comment

by:fsze88
ID: 33530871
also, I tried system dsn
Microsoft ODBC FOR Oracle
and Oracle in OraClient 11g_home1

same result please help
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33530898
>> it works on modify a module (small change)

It will take an effort to convert it all to ado.net, but you'll benefit from it later if you need to modify your program

>> but on my project, there many forms are using old ado method

And does it fail for each stored procedure that you are calling ?
0
 
LVL 15

Author Comment

by:fsze88
ID: 33530929
And does it fail for each stored procedure that you are calling ?
yes, all of stored procedure with return only
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33530967
If you trace your application (and database), can you see the call to the stored procedure ?
0
 
LVL 15

Author Comment

by:fsze88
ID: 33530979
should worked, it works all the stored procedure(s) that no need to return values
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33531005
Are you sure that the database connection is open ?
connObj = getConnObj
connObj.Open


cmdObj = New ADODB.Command

With cmdObj

.let_ActiveConnection(connObj)
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = "invoicepackage.nextinvoicenum"
.Parameters.Append(.CreateParameter("invoicenum", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamOutput, 255, 0))
End With
		
cmdObj.Execute()

getNextInvoiceIndex = cmdObj.Parameters("invoicenum").Value
MessageBox.Show(getNextInvoiceIndex )

connObj.Close

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33531038
Moderator,
the different is
*I don't want to change ado.net, because it will big change on my new vb.net project*
on my project forms the calling is a bit different.. but very similar issue with previous one
With cmdObj
            .let_ActiveConnection(connObj)
            .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
            .CommandText = "invoicepackage.insertinvoiceproc"
            .Parameters.Append(.CreateParameter("comId_in", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , comId))
            .Parameters.Append(.CreateParameter("branchId_in", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , branchId))
            .Parameters.Append(.CreateParameter("invoicePoNum_in", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, Len(invoicePoNum) + 1, invoicePoNum))
            .Parameters.Append(.CreateParameter("invoiceOldNum_in", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, Len(invoiceOldNum) + 1, invoiceOldNum))
            .Parameters.Append(.CreateParameter("invoiceDate_in", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput, , invoiceDate))
            .Parameters.Append(.CreateParameter("invoiceTotal_in", ADODB.DataTypeEnum.adDouble, ADODB.ParameterDirectionEnum.adParamInput, , invoiceTotal))
            .Parameters.Append(.CreateParameter("transportId_in", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , transportId))

            p = .CreateParameter("invoiceid", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamOutput)

            .Parameters.Append(p)
        End With

        MessageBox.Show(p.Value)


        cmdObj.Execute()
        invoiceId = cmdObj.Parameters("invoiceid").Value

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33531041
@thermoduric: I answered the other question with a suggestion to convert the old vb6 code to the newest technology that's currently used in ado.net. I assumed that the asker did that because he accepted the answer (and according the comments in this question, he did it for a little part in the application). But now he considered that converting it to the .NET-methods would be too much work for him (too bad that he doesn't take the time to do it, he will benefit later on).  Because of that, I assume, that he's re-asking the question to see if there is a solution with the older adodb-technology !
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33531093
Try the following
With cmdObj

.let_ActiveConnection(connObj)
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = "invoicepackage.nextinvoicenum"
.Parameters.Append(.CreateParameter("invoicenum", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamOutput))
.NamedParamters = true
End With
		
cmdObj.Execute()

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33531149
Moderator,
I can/able to change one module,But If change all the forms (20+)
I will very trouble and headache

commect ID:33531093
same
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 33531164
@thermoduric: I understand, I'll repost here also the links I provided in the first question :)

@fsze88: I think that you lost already more time searching this issue than changing the code into the ado.net solution !


If I was u, try to convert the code and use the latest technology. By this I mean, use ADO.NET instead of ADODB

Calling Oracle stored procedures from Microsoft.NET
http://www.c-sharpcorner.com/UploadFile/john_charles/CallingOraclestoredproceduresfromMicrosoftdotNET06222007142805PM/CallingOraclestoredproceduresfromMicrosoftdotNET.aspx

HOW TO: Call Oracle Stored Procedures in Visual Basic .NET with the Microsoft Oracle Managed Provider (Q321718)  
http://www.devasp.net/net/search/res/r23507.html

Calling Stored Procedure Using ORACLE OLEDB Using VB.NET
http://forums.devshed.com/net-development-87/calling-stored-procedure-using-oracle-oledb-using-vb-net-110883.html
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33531605
Did you try the comment 33531093 ?
0
 
LVL 15

Author Comment

by:fsze88
ID: 33531930
tried same
0
 
LVL 15

Author Closing Comment

by:fsze88
ID: 33538485
accept as solution again
0

Featured Post

Technology Partners: 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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

738 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