Executing Oracle Stored Procedure in VB.net

[simdan42]

I Have a Stored Procedure "AddStudent" It takes as parameters: StudentEmail, Lastname, FirstName, Middle, Address, City, State, ZipCode, Phone , DOB) All Are VarChar2
I need to know how to execute the stored procedure when the Sumbit button on the form is pressed. Please help!!!

 

[RiverGuy]

You need to make an OleDBCommand object


cmd1 = New System.Data.SqlClient.OleDbCommand()
cmd1.CommandType = CommandType.Text
cmd1.CommandText = "exec AddStudent @StudentEmail = 'Whatever', etc, etc."
cmd1.Connection = yourConnection



I'm not sure if the exec statement is exactly the same for Oracle. This is the syntax for SQL Server. I would think it would be the same or very similar.

 

[RiverGuy]

Oh.....then when you want to execute it, its:

cmd1.ExecuteNonQuery

 

[simdan42]

Here is my code I get an runtime error on this line OCmd.ExecuteNonQuery()

Dim Ocn As New OracleConnection()
Dim OCmd As New OracleCommand("AddStudent&quot
Dim OParam As OracleParameter
Dim IRowsAffected As Int32
Dim sConnectionString As String
Dim sString As String
sConnectionString = "server = ;Uid =scott; pwd = tiger"
sString = "AddStudent"
Ocn = New OracleConnection(sConnectionString)
Ocn.Open()
OCmd = Ocn.CreateCommand()
OCmd.CommandText = sString
OCmd.CommandType = CommandType.StoredProcedure

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 40
OParam.ParameterName = "StudentEmail"
OParam.Value = TxtEmail.Text
OCmd.Parameters.Add(OParam)

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 20
OParam.ParameterName = "LastName"
OParam.Value = TxtLastName.Text
OCmd.Parameters.Add(OParam)

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 20
OParam.ParameterName = "FirstName"
OParam.Value = TxtFirstName.Text
OCmd.Parameters.Add(OParam)

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 20
OParam.ParameterName = "MiddleName"
OParam.Value = TxtMiddle.Text
OCmd.Parameters.Add(OParam)

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 50
OParam.ParameterName = "Address"
OParam.Value = TxtAddress.Text
OCmd.Parameters.Add(OParam)

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 20
OParam.ParameterName = "City"
OParam.Value = TxtCity.Text
OCmd.Parameters.Add(OParam)

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 2
OParam.ParameterName = "State"
OParam.Value = TxtState.Text
OCmd.Parameters.Add(OParam)

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 10
OParam.ParameterName = "ZipCode"
OParam.Value = TxtZipCode.Text
OCmd.Parameters.Add(OParam)

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 10
OParam.ParameterName = "Phone"
OParam.Value = TxtPhone.Text
OCmd.Parameters.Add(OParam)

OParam = New OracleParameter()
OParam.OracleType = OracleType.VarChar
OParam.Size = 10
OParam.ParameterName = "DOB"
OParam.Value = TxtDOB.Text
OCmd.Parameters.Add(OParam)

OCmd.ExecuteNonQuery()

'If IRowsAffected > 0 Then
'MessageBox.Show("Success&quot

'End If

Ocn.Close()

 

[RiverGuy]

What driver are you using for this? An Oracle ADO driver? Is it possible that its not .Net compliant? Are your text boxes filled when you execute this? Is it possible that Oracle needs to explicitly see a single quote...such as :

"'" & TxtAddress.Text & "'".

Maybe if you are using an older driver, you should use ADO instead of ADO.Net

 

[simdan42]

Microsoft .net OLE oracle
Text boxes are filled
Could be I will try

 

[woyler]

What error are you getting?

 

[simdan42]

error
An unspecified exception of type 'System.Data.OracleClient.OracleException' occured in system..data.oracleclient.dll

 

[woyler]

Have you accomplished any other database transactions using this provider, or is this your first attempt at hitting the database

 

[simdan42]

First attempt. If you have any other suggestions I am open to them.

 

[woyler]

try creating a connection, and doing a simple "SELECT * FROM table" type of query. A stored procedure has a lot involved compared to a SELECT SQl statement. This way, if the simple query works, we know that your provider is up to par and we should concentrate our efforts on the syntax of the SP, otherwise, we know we should move to the OLEDB library

 

[simdan42]

I ran the same code but with an insert statement form the table and if work so the problem lies with the calling on the stored procedure. Any Ideas?

 

[woyler]

First , check and makes sure all parameter data types match the expected parameters in the stored procedure. Check that you have the same number of incoming and outgoing arguments in the procedure. Also check and make sure that all SQl statements in the SP are correct. If possible run each one outside of the SP in a query window.
Lets rule out these possible problems then we wil step forward.

 

[RiverGuy]

Also, try executing the stored procedure with just a string like I layed out up there....Then you can see if it has a problem dealing with stored procedures as a .Net object, or if the Oracle end is having trouble deciphering it.

 

[simdan42]

Did all that, Nothing. I noticed on thing is that I have the variables in the stored procedure as VarChar2 but in VB.net only VarChar is an option. However I did use those variables when using the Input statement above, so i dunno.

 

[bobhagan]

I'm struggling through the same problem and haven't made an insert or update work,

but one thing I think you're missing is a parameter direction, in this case IN. If you're trying to get rowsaffected out you also need to specify that as a parameter with a direction of OUT.

Bob Hagan