How to call and execute an oracle stored Procedure from VB.NET

assuming u have an active connection this is a simple example
   Dim c As New OleDb.OleDbCommand()
c.connection=conn
c.commandtext="procedurename"
  c.CommandType = CommandType.StoredProcedure
   c.Parameters.Add(paramtername, OleDb.OleDbType.VarChar, 20)
  c.Parameters(parametername).Direction = ParameterDirection.Input
 c.Parameters.Add(paramtername1, OleDb.OleDbType.VarChar, 20)
  c.Parameters(parametername1).Direction = ParameterDirection.output
 c.Parameters(paramtername).Value ="hi"
   c.ExecuteNonQuery()
dim s as string= c.Parameters(paramtername1).Value

Dim constr As String
        constr = "server=idnsql;integrated security=true;database=Iliana"
        Dim conn As New SqlClient.SqlConnection(constr)
        Dim cmd As New SqlClient.SqlCommand("OwnersSearch", conn)
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet


        Dim flag As Boolean
        Try

            flag = False

            txtContact.Text = ""
            cmd.CommandType = CommandType.StoredProcedure
            conn.Open()

In my example "OwnersSearch " is my stored procedure....
Then I add my parameters like:

 cmd.Parameters.Add("@Contact", SqlDbType.Int)
 cmd.Parameters("@Contact").Value = CInt(txtContact.Text)

                SqlDataAdapter1.SelectCommand = cmd
                SqlDataAdapter1.Fill(DsViewContacts)

My SqlDaaAdapter1 and DsviewContacts were created previously

razo

Since I'm used to ADO, what is the Namespace that I have to use to
get the OLED.

I get "c" is not declared error

Thanks
vm

Sorry, but neither razo or Ilianam method will work, you need the oracleclient not sqlclient nor oledb

 Dim con  As New system.data.oracleclient.oracleconnection("yourconnectionstring")
 dim oda as new system.data.oracleclient.oraclecommand("yourstoredprocedure")
oda.commandtype = commandtype.storedprocedure

etc..

Well, that's true but no matter what kind of adapter you are using the results are the same....
You have to create a connection, a comand, and execute the comand
oda.execNonquery()

Ilianam, that's true aswell

Ronal & Ilianam

I get this error when I run the application with Oracle Client

Wrong number of arguments in line1, column7.
I know it refers to line1 in stored procedure but column 7 is
parameter that I'm passing.

And also when I gave the stored procedure name I gave the package name also like this:
  myCMD.CommandText = "DAT_Q_VIEW.get_test"
I don't know why it is causing this problem.

VM

Dim cmd As New oracleclient.oracleCommand"DAT_Q_VIEW.get_test", your connection)
mycmd.CommandType = CommandType.StoredProcedure

Ilianam

I passed all those statements untill I get to Catch x in the Try statment I get the Message box with error. Here is the code I did:

Dim myCMD As New OracleCommand
        myCMD.Connection = Oraclecon
        myCMD.CommandText = "DAT_Q_VIEW.get_demand_award_list"
        myCMD.CommandType = CommandType.StoredProcedure
        myCMD.Parameters.Add(New OracleParameter("i_ptcpt_cd", OracleType.Char)).Value = "AEMN"
        myCMD.Parameters.Add(New OracleParameter("i_mkt_cd", OracleType.Char)).Value = "L"
        myCMD.Parameters.Add(New OracleParameter("i_user_id", OracleType.Char)).Value = "ETEST"
        myCMD.Parameters.Add(New OracleParameter("i_start_dt_gmt", OracleType.DateTime)).Value = strdate
        myCMD.Parameters.Add(New OracleParameter("i_end_dt_gmt", OracleType.DateTime)).Value = strdate1
        myCMD.Parameters.Add(New OracleParameter("i_parm01_num", OracleType.NVarChar)).Value = "DEC"
        myCMD.Parameters.Add(New OracleParameter("i_param02_txt", OracleType.NVarChar)).Value = "DEM"
        myCMD.Parameters.Add(New OracleParameter("o_curvar_dem_awd_list", OracleType.Cursor)).Direction = ParameterDirection.Output


        Dim MyDA As New OracleDataAdapter(myCMD)

        Try
            MyDA.Fill(Ds)
        Catch x
            MessageBox.Show(x.Message.ToString)
        End Try

I GET PASSED ALL THE ABOVE AND IN THE TRY STATEMENT I READ
THE ERROR IN MSGBOX, I'M TRYING TO FILL THE DATASET AND POPULATE THE RESULTS ON TO THE GRID.

thanks
vm

mycmd.Parameters.Add(output, SqlDbType.Int)
            cmd.Parameters(output).Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()

Also is the datagrid binded?

Illianam
I don't understand exactly what you stated there.

Do you want me to add this to the existing code or replace something. When I pasted this code in my app, i get
output is not declared

Yes my datagrid is binded like this:
 DataGrid1.DataSource = Ds.Tables(0)

Please explain me what to do, I can increase the points though.

Thanks and appreciate your response.

vm

You have this
myCMD.Parameters.Add(New OracleParameter("o_curvar_dem_awd_list", OracleType.Cursor)).Direction = ParameterDirection.Output

Isn't this a return value from the stored procedure?

Yes exactly that is a return value from the stored procedure. What
I don't understand from your previous comment that execute statement, I get the system.error when I come to that point.

Look I have a function Insert data like this
I pass two arrays, the name of the parameter and the value
Try
            cmd.CommandType = System.Data.CommandType.StoredProcedure
            cmd.Connection = conn
            conn.Open()
            For i = 0 To UBound(param)
                kind = param(i).GetType
                cmd.Parameters.Add(param(i), kind)
            Next
            For i = 0 To UBound(values)

                cmd.Parameters(i).Value = values(i)
            Next
            cmd.Parameters.Add(output, SqlDbType.Int)
            cmd.Parameters(output).Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()
            InsertData = cmd.Parameters(output).Value
        Catch ex As Exception
            DisplayError("Insert Data", ex)
        End Try

Ilianam

Well, now I think I have real problem here:

looking at my previous code that I pasted, Since I'm new to both .NET and stored Procedures I do not know excatly how to use the cursor for out put.

Do I have to replace the last cursorstatment with yours. I tried but still I get system.error. Do I have to declare the output.

Thanks
vm

why you need a return value? You have to declare your output in your store procedure

ALTER   PROCEDURE [dbo].InsertnHotels
      @hotel_code varchar(5),
      @hotel_name varchar(50),
      @hotel_address varchar(75),
      @hotel_city varchar(30),
      @hotel_state char(2),
      @hotel_zip varchar(10),
      @hotel_phone varchar(15),
      @hotel_directions ntext,
      @hotel_map_link varchar(1000),
      @hotel_id int OUTPUT

Ilianam

I got this code and when i execute I get input string is not formatted,
I checked the datatypes and everything still I get the error:

 temp1 = "test"
        temp2 = "hello"
        temp3 = "34833"
        temp4 = "DEC"
        temp5 = "DEM"
        Oraclecon.Open()

        Dim myCMD As New OracleCommand
        myCMD.Connection = Oraclecon
        myCMD.CommandText = "DAT_TEST_VIEW.get_demand_test_list"
        myCMD.CommandType = System.Data.CommandType.StoredProcedure
        myCMD.Parameters.Add(New OracleParameter("i_ptcpt_cd", OracleType.Char)).Value = "'& temp1 &'"
        myCMD.Parameters.Add(New OracleParameter("i_mkt_cd", OracleType.Char)).Value = "'& temp2 &'"
        myCMD.Parameters.Add(New OracleParameter("i_user_id", OracleType.Number)).Value = "34833"
        myCMD.Parameters.Add(New OracleParameter("i_start_dt_gmt", OracleType.DateTime)).Value = "'& strdate &'"
        myCMD.Parameters.Add(New OracleParameter("i_end_dt_gmt", OracleType.DateTime)).Value = "'& strdate1 &'"
        myCMD.Parameters.Add(New OracleParameter("i_parm01_num", OracleType.Char)).Value = "'& temp4 &'"
        myCMD.Parameters.Add(New OracleParameter("i_param02_txt", OracleType.Char)).Value = "'& temp5 &'"
        myCMD.Parameters.Add(New OracleParameter("o_curvar_dem_awd_list", OracleType.Cursor)).Direction = ParameterDirection.Output

myCMD.ExecuteNonQuery()

Thanks
vm

Dim Oraclecon As New OracleConnection("Server=ntest;Uid=dag;Pwd=dah")

Is there anything I have to format the date because I get an error also not valid datemonth at index 3.

I formatted like this: "08-Sep-2004" which oracle accepts but I don't know is it passing like a string or something else.

Thanks
vm

vmandem....
Please test yous sp in query analyzer and let me know