Solved

vb.net call oracle stored procedure, not work

Posted on 2010-08-26
28
848 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

744 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

14 Experts available now in Live!

Get 1:1 Help Now