• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 909
  • Last Modified:

vb.net call oracle stored procedure, not work

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
fsze88
Asked:
fsze88
  • 12
  • 9
  • 3
1 Solution
 
DhaestCommented:
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
 
fsze88Author Commented:
no error , but return nothing
yes, I work on pl/sql
0
 
DhaestCommented:
Can you try it with changing the parameter-type
ADODB.ParameterDirectionEnum.adParamReturnValue instead of
ADODB.ParameterDirectionEnum.adParamOutput
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
fsze88Author Commented:
sorry, same
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>it return nothing.....
just to clarify: where exactly does it return "nothing" ?
0
 
fsze88Author Commented:
messagebox.show cmdObj.Parameters("invoicenum").Value

a messagebox contain blank
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
fsze88Author Commented:
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
 
fsze88Author Commented:
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
 
fsze88Author Commented:
also, I tried system dsn
Microsoft ODBC FOR Oracle
and Oracle in OraClient 11g_home1

same result please help
0
 
DhaestCommented:
>> 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
 
fsze88Author Commented:
And does it fail for each stored procedure that you are calling ?
yes, all of stored procedure with return only
0
 
DhaestCommented:
If you trace your application (and database), can you see the call to the stored procedure ?
0
 
fsze88Author Commented:
should worked, it works all the stored procedure(s) that no need to return values
0
 
DhaestCommented:
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
 
fsze88Author Commented:
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
 
DhaestCommented:
@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
 
DhaestCommented:
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
 
fsze88Author Commented:
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
 
DhaestCommented:
@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
 
DhaestCommented:
Did you try the comment 33531093 ?
0
 
fsze88Author Commented:
tried same
0
 
fsze88Author Commented:
accept as solution again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 12
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now