Solved

vb.net call oracle stored procedure, not work

Posted on 2010-08-26
28
855 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
cannot connect to sqlserver 8 29
VB.Net/LINQ: ".Contains" condition not finding existing result in MSSQL 16 36
write xml in vb.net 2 24
vb.net 1 month apart 11 30
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

809 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