Solved

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

Posted on 2004-09-08
21
547 Views
Last Modified: 2008-01-09
I have a stored procedure like this:

Procedure get_test

(i_C1         IN nm_D
,i_C2         IN nm_K
,i_param1     IN VARCHAR2
,i_param2     IN VARCHAR2
,o_curve      OUT curvattype_1)

IS
       
   lv_xp             xp.xp_id
   lv_end_dt         DATE;

--Here I have all my BEGING,OPEN,SELECT statments
------
---
--
END get_test

My question is how to call this stored procedure from my VB.NET and execute it and also pass
the argument variables to the procedure like above o_curve i have to pass this value from
vb.net to the stored procedure. How to do this. Give me some examples.

Thanks
vm
0
Comment
Question by:vmandem
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 8

Expert Comment

by:razo
ID: 12006272
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
0
 

Expert Comment

by:Ilianam
ID: 12006301
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()
0
 

Expert Comment

by:Ilianam
ID: 12006351
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vmandem
ID: 12006701
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

0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12006826
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..
0
 

Expert Comment

by:Ilianam
ID: 12006875
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()
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12007116
Ilianam, that's true aswell
0
 

Author Comment

by:vmandem
ID: 12007236
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
0
 

Expert Comment

by:Ilianam
ID: 12007293
Dim cmd As New oracleclient.oracleCommand"DAT_Q_VIEW.get_test", your connection)
mycmd.CommandType = CommandType.StoredProcedure
0
 

Author Comment

by:vmandem
ID: 12007385
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
0
 

Expert Comment

by:Ilianam
ID: 12007559
mycmd.Parameters.Add(output, SqlDbType.Int)
            cmd.Parameters(output).Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()

Also is the datagrid binded?
0
 

Author Comment

by:vmandem
ID: 12007863
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
0
 

Expert Comment

by:Ilianam
ID: 12008341
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?
0
 

Author Comment

by:vmandem
ID: 12008386
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.
0
 

Expert Comment

by:Ilianam
ID: 12008449
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
0
 

Author Comment

by:vmandem
ID: 12009446
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
0
 

Expert Comment

by:Ilianam
ID: 12009505
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
0
 

Author Comment

by:vmandem
ID: 12010488
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
0
 

Accepted Solution

by:
Ilianam earned 50 total points
ID: 12010606
how is oraclecon declared?
0
 

Author Comment

by:vmandem
ID: 12010674
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
0
 

Expert Comment

by:Ilianam
ID: 12016247
vmandem....
Please test yous sp in query analyzer and let me know
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

724 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