Using PL/SQL OUT parameters in VB.NET code


ftm

Joined
Jan 9, 2008
Messages
1
Reaction score
0
Does anybody have a simple example of retrieving and using a value, output from a PL/SQL stored procedure called from a SQLDataSource control, back in the VB.NET code of an ASP.NET application? If I can just get the basic mechanism working then this will be really useful for my application.

So far I have a simple test web page (I know there are easier ways to accomplish this, but I just want to get the mechanism working):

HTML:
<form id="form1" runat="server">
    <div>
    
    <asp:Label ID="Label1" runat="server" Text="Enter Value: "></asp:Label>
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
    <asp:Label ID="Label2" runat="server" Text="Result: "></asp:Label>
    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
    <asp:Button ID="Button1" runat="server" Text="Run" /><br />
           
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
    SelectCommand="TESTPARAM"
    SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter Name="VINPUT" ControlID="TextBox1"  PropertyName="Text" Type="String" />
        <asp:Parameter Name="VOUTPUT" Type="Double" Direction="Output" DefaultValue="0" />
    </SelectParameters>    
</asp:SqlDataSource>
       
    </div>
    </form>

and some VB.NET code behind it:

Code:
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient

Partial Class testoutput
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        ' Call the PL/SQL proc
        SqlDataSource1.Select(DataSourceSelectArguments.Empty)

    End Sub

    Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected

        Dim cmd As IDbCommand
        Dim param As SqlParameter

        cmd = e.Command

        For Each param In cmd.Parameters
            ' Extract the name and value of the parameter, store in label
            Label2.Text = Label2.Text & param.ParameterName & " - " & param.Value.ToString()
        Next

    End Sub

End Class

With a stored procedure that is called from the database. This I want to return the OUT parameter value and be able to use it in the VB.NET code.

Code:
create or replace procedure TestParam (vInput in number, vOutput out number) is

begin

vOutput := vInput * 2;

insert into testoutput
(id, input_number, result_number)
values(seq_testoutput_id.nextval, vInput, vOutput);
  
end TestParam;

However I get an error message of
Unable to cast object of type 'System.Data.OracleClient.OracleParameter' to type 'System.Data.SqlClient.SqlParameter'.
pointing to the For Each param In cmd.Parameters line in the VB.NET code.

Can anybody help and let me know what I'm doing wrong? Many thanks in advance.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top