locked
Insert works with System.Data.OracleClient but not with Oracle.DataAccess.Client RRS feed

  • Question

  • User-1499877221 posted

    Does anyone know why the below insert works using System.Data.OracleClient Parameters but not with the Oracle.DataAccess.Client Paramenters?

    Try
                Using dbConnection As New OracleConnection(ConfigurationManager.ConnectionStrings("MaintenanceConnection").ConnectionString)
                    Dim dbCommand As New OracleCommand("ADDMAINTENANCE", dbConnection)
                    dbCommand.CommandType = CommandType.StoredProcedure

                    'dbCommand.Parameters.Add("i_system", OracleDbType.Varchar2, 255).Value = txtSystem.Text
                    'dbCommand.Parameters.Add("i_location", OracleDbType.Varchar2, 255).Value = ddlLocation.SelectedItem.Text
                    'dbCommand.Parameters.Add("i_start_date", OracleDbType.Date).Value = Selected_Date & " " & Selected_Time
                    'dbCommand.Parameters.Add("i_start_time", OracleDbType.Date).Value = Selected_Date & " " & Selected_Time
                    'dbCommand.Parameters.Add("i_sched_duration", OracleDbType.Varchar2, 10).Value = txtSched.Text & " " & ddlSpan.SelectedItem.Text
                    'dbCommand.Parameters.Add("i_poc_name", OracleDbType.Varchar2, 50).Value = txtPOC.Text
                    'dbCommand.Parameters.Add("i_poc_email", OracleDbType.Varchar2, 100).Value = txtEmail.Text
                    'dbCommand.Parameters.Add("i_poc_phone", OracleDbType.Varchar2, 20).Value = txtPhone.Text
                    'dbCommand.Parameters.Add("i_completed", OracleDbType.Varchar2, 5).Value = "No"
                    'dbCommand.Parameters.Add("i_purpose", OracleDbType.Varchar2, 20).Value = ddlPurpose.SelectedItem.Text
                    'dbCommand.Parameters.Add("i_notes", OracleDbType.Clob).Value = txtNotes.Text
                    'dbCommand.Parameters.Add("i_issues", OracleDbType.Varchar2, 1000).Value = txtIssues.Text

                    'If ddlSpan.SelectedItem.Text = "hours" Then
                    '    dbCommand.Parameters.Add("i_sched_time", OracleDbType.Date).Value = Scheduled_Date.AddHours(Ceiling(Double.Parse(txtSched.Text)))
                    'ElseIf ddlSpan.SelectedItem.Text = "minutes" Then
                    '    dbCommand.Parameters.Add("i_sched_time", OracleDbType.Date).Value = Scheduled_Date.AddMinutes(Ceiling(Double.Parse(txtSched.Text)))
                    'End If

                    dbCommand.Parameters.Add("i_system", OracleType.VarChar, 255).Value = txtSystem.Text
                    dbCommand.Parameters.Add("i_location", OracleType.VarChar, 255).Value = ddlLocation.SelectedItem.Text
                    dbCommand.Parameters.Add("i_start_date", OracleType.DateTime).Value = Selected_Date & " " & Selected_Time
                    dbCommand.Parameters.Add("i_start_time", OracleType.DateTime).Value = Selected_Date & " " & Selected_Time
                    dbCommand.Parameters.Add("i_sched_duration", OracleType.VarChar, 10).Value = txtSched.Text & " " & ddlSpan.SelectedItem.Text
                    dbCommand.Parameters.Add("i_poc_name", OracleType.VarChar, 50).Value = txtPOC.Text
                    dbCommand.Parameters.Add("i_poc_email", OracleType.VarChar, 100).Value = txtEmail.Text
                    dbCommand.Parameters.Add("i_poc_phone", OracleType.VarChar, 20).Value = txtPhone.Text
                    dbCommand.Parameters.Add("i_completed", OracleType.VarChar, 5).Value = "No"
                    dbCommand.Parameters.Add("i_purpose", OracleType.VarChar, 20).Value = ddlPurpose.SelectedItem.Text
                    dbCommand.Parameters.Add("i_notes", OracleType.Clob).Value = txtNotes.Text
                    dbCommand.Parameters.Add("i_issues", OracleType.VarChar, 1000).Value = txtIssues.Text

                    If ddlSpan.SelectedItem.Text = "hours" Then
                        dbCommand.Parameters.Add("i_sched_time", OracleType.DateTime).Value = Scheduled_Date.AddHours(Ceiling(Double.Parse(txtSched.Text)))
                    ElseIf ddlSpan.SelectedItem.Text = "minutes" Then
                        dbCommand.Parameters.Add("i_sched_time", OracleType.DateTime).Value = Scheduled_Date.AddMinutes(Ceiling(Double.Parse(txtSched.Text)))
                    End If


                    dbConnection.Open()
                    dbCommand.ExecuteNonQuery()
                    dbConnection.Close()
                End Using
            Catch ex As OracleException
                lblMessage.Text = ex.ErrorCode
            Catch ex As Exception
                lblMessage.Text = ex.Message
            Finally
                Response.Redirect(String.Format("default.aspx"))
            End Try

    Wednesday, October 12, 2011 5:19 PM

Answers

  • User-1499877221 posted

    I finally got around to looking at this code again and figured this out. Oracle.DataAccess.Client is pretty sensitive when it comes to dates. I did the following and the insert works like a charm.

    'dbCommand.Parameters.Add("i_start_date", OracleDbType.Date).Value = Selected_Date & " " & Selected_Time
    'dbCommand.Parameters.Add("i_start_time", OracleDbType.Date).Value = Selected_Date & " " & Selected_Time

    Changed by wrapping a DateTime.Parse() around the values.

    'dbCommand.Parameters.Add("i_start_date", OracleDbType.Date).Value = DateTime.Parse(Selected_Date & " " & Selected_Time)
    'dbCommand.Parameters.Add("i_start_time", OracleDbType.Date).Value = DateTime.Parse(Selected_Date & " " & Selected_Time)

    System.Data.OracleClient did not have an issue with this but Oracle.DataAccess.Client does.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 4, 2012 7:49 PM

All replies

  • User269602965 posted
        Try
          Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
          Using conn As New OracleConnection(connstr)
            Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcSelectCountContracts", conn)
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Clear()
              cmd.Parameters.Add("CountContracts", OracleDbType.RefCursor, ParameterDirection.Output)
              conn.Open()
    

    Clear parameters

    dbCommnad.Parameters.Clear()

    then Add parameters.

    It is a good idea to explicitly state your parameter direction with Oracle. especially when calling a stored proc.

    You might be missing reference to Oracle.DataAccess.dll in application configuration... often overlooked.

    A couple times when I had some minor issue between the old System.Data.OracleClient and ODP.NET,

    putting IMPORTS statement in the code for Oracle seemed then to allow Intellitype to help me find

    that nitpicky little difference in punctuation, etc.

    Glad I am done transitioning out of System.Data.OracleClient.

     

     

    Wednesday, October 12, 2011 6:42 PM
  • User-1499877221 posted

    No, Oracle.DataAccess is referenced properly becasue I can do selects using stored procedures using other forms in the application. I am also able to insert with a stored procedure else where in the application. It is just this particular function that does not insert data using Oracle.DataAccess.

    Wednesday, October 12, 2011 7:26 PM
  • User269602965 posted

    Try using Varchar2 instead of Varchar..

    are you certain it is Varchar and not Varchar2 on the Oracle end??

    Wednesday, October 12, 2011 10:58 PM
  • User-1499877221 posted

    Varchar is for System.Data.OracleClient and Varchar2 is for Oracle.DataAccess.Client. The function works when using System,Data,OracleClient but does nothing when using Oracle.DataAccess.Client.

    Wednesday, October 19, 2011 10:04 AM
  • User-1499877221 posted

    I finally got around to looking at this code again and figured this out. Oracle.DataAccess.Client is pretty sensitive when it comes to dates. I did the following and the insert works like a charm.

    'dbCommand.Parameters.Add("i_start_date", OracleDbType.Date).Value = Selected_Date & " " & Selected_Time
    'dbCommand.Parameters.Add("i_start_time", OracleDbType.Date).Value = Selected_Date & " " & Selected_Time

    Changed by wrapping a DateTime.Parse() around the values.

    'dbCommand.Parameters.Add("i_start_date", OracleDbType.Date).Value = DateTime.Parse(Selected_Date & " " & Selected_Time)
    'dbCommand.Parameters.Add("i_start_time", OracleDbType.Date).Value = DateTime.Parse(Selected_Date & " " & Selected_Time)

    System.Data.OracleClient did not have an issue with this but Oracle.DataAccess.Client does.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 4, 2012 7:49 PM
  • User269602965 posted

    I found some querky things with NUMBERs as well, like Decimal works and Int32 does not, but the number is an integer.

    Oh well. At least ODAC is fast!

    Friday, January 6, 2012 6:37 PM