Solved

vb.net call oracle stored procedure, not work

Posted on 2010-08-26
28
851 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
  • 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
 
LVL 142

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 142

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

947 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now