Solved

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

Posted on 2004-09-08
21
528 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
  • 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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

11 Experts available now in Live!

Get 1:1 Help Now