Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vb.net call oracle stored procedure, not work

Posted on 2010-08-26
28
Medium Priority
?
877 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
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!

 
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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

670 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