locked
Oracle Update with Parameters.. RRS feed

  • Question

  • Should be pretty simple...   pass some parameters to a SQL update statement for Oracle....

    Dim strQuery_UpdateUserData As String = "Update Prod_Reports_Comments Set ShiftSupervisor = :Parm1, SafetyInfo = :Parm2, Equipment = :Parm3, DailyUpdates = :Parm4, Modified_By = :Parm5 WHERE Prod_Date = :Parm6 and Shift = :Parm7"

            Dim x As clsOracle = New clsOracle
    
            Dim fn As String() = {"ShiftSupervisor", "SafetyInfo", "Equipment", "DailyUpdates", "Modified_By", "Prod_Date", "Shift"}
            Dim val As String() = {cboShiftSupervisiors.Text, rtbSafetyInfo.Text, rtbEquipmentIssues.Text, rtbDailyUpdates.Text, My.User.Name.ToString, Utilities.ConvertTimeForORACLE(CDate(txtDate.Text)), txtShift.Text}
            
            x.UpdateField(strQuery_UpdateUserData, fn, val)
            x.Dispose()
            Dim x As clsOracle = New clsOracle
    
            Dim fn As String() = {"ShiftSupervisor", "SafetyInfo", "Equipment", "DailyUpdates", "Modified_By", "Prod_Date", "Shift"}
            Dim val As String() = {cboShiftSupervisiors.Text, rtbSafetyInfo.Text, rtbEquipmentIssues.Text, rtbDailyUpdates.Text, My.User.Name.ToString, Utilities.ConvertTimeForORACLE(CDate(txtDate.Text)), txtShift.Text}
            'Dim val As String() = {cboShiftSupervisiors.Text, rtbSafetyInfo.Text, rtbEquipmentIssues.Text, rtbDailyUpdates.Text, My.User.Name.ToString, "04/26/2016", txtShift.Text}
    
            x.UpdateField(strQuery_UpdateUserData, fn, val)
            x.Dispose()
        Public Function ConvertTimeForORACLE(dt As DateTime) As String
            Dim x As String = dt.ToShortDateString 
                    Dim ConvertedDate As String = String.Format("to_date('{0}', 'mm/dd/yyyy')", x)
            
            Console.WriteLine("converted date : {0}", ConvertedDate)
            Return ConvertedDate
    
        End Function

    returns this...    to_date('4/20/2016', 'mm/dd/yyyy')


        Public Sub UpdateField(ByVal strQueryString As String, ByVal strFieldIDName As String(), ByVal strParmeter() As Object)
            Dim da As Ora.OracleDataAdapter = New Ora.OracleDataAdapter
            Dim cmd As Ora.OracleCommand = New Ora.OracleCommand
            Dim para As Ora.OracleParameter = New Ora.OracleParameter
    
    
            For i = 0 To strFieldIDName.Length - 1
                cmd.Parameters.Add(New Ora.OracleParameter(":Parm" + (i + 1).ToString, strParmeter(i)))
                            Console.WriteLine("Parameters {0} Value {1}", cmd.Parameters.Item(i).ToString, cmd.Parameters.Item(i).Value.ToString)
            Next
    
            Try
                Open_OraDBRML2()
                cmd.CommandType = CommandType.Text
    
                'cmd.BindByName = True
                cmd.Connection = _cnDBRML2
                cmd.CommandText = strQueryString
                Console.WriteLine("SQL Statement: {0}", strQueryString)
    
                Console.WriteLine("Command {0}", cmd.CommandText.ToString)
                cmd.ExecuteNonQuery()
    
    
            Catch ex As Ora.OracleException
                Console.WriteLine("RMReports.clsOracle.UpdateField:{0}", ex.Message)
            Catch ex As Exception
                Console.WriteLine("RMReports.clsOracle.UpdateField:{0}", ex.Message)
            Finally
    
            End Try
            Close_OraDBRML2()
            da = Nothing
            cmd = Nothing
    
        End Sub

    Command Update Prod_Reports_Comments Set ShiftSupervisor = :Parm1, SafetyInfo = :Parm2, Equipment = :Parm3, DailyUpdates = :Parm4, Modified_By = :Parm5 WHERE Prod_Date = :Parm6 and Shift = :Parm7

    I get this error;

    RMReports.clsOracle.UpdateField:ORA-01858: a non-numeric character was found where a numeric was expected

    I look at the command properities for parameters.. and all the parameters have the right values, but one thing I did notice is their data type is NvarChar2(126).   so I am not sure when the sql executes, that the date field(:Para6) would be evaluated correctly. 

    Any suggestions on what I need to be doing?

    Friday, May 6, 2016 6:27 PM

Answers

  • Can you post the code that assigns the values to the Command Parameters? I'm assuming the Add method specifies a Date data type and the value assigned is stored in a DateTime variable?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by LandLord324 Tuesday, May 10, 2016 7:23 PM
    Tuesday, May 10, 2016 2:37 PM

All replies

  • PS: Oracle does have their own community at https://community.oracle.com/welcome and the docs that support your call are available at Oracle's site as well, for example http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDataAdapterClass.htm
    Friday, May 6, 2016 7:45 PM
  • Your code looks strange, what do you want to achieve?

    Be aware if the name of a parameter starts with ":" or "@" is just a convention. It is just a prefix and could have been also a "$".

     


    Success
    Cor

    Friday, May 6, 2016 8:12 PM
  • I had just copied parts of the code that were revilement to the question.
    Friday, May 6, 2016 8:25 PM
  • You should use AddWithValue and see what happens

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, May 6, 2016 8:47 PM
  • I had just copied parts of the code that were revilement to the question.

    Revilement (Revilement definition, to assail with contemptuous or opprobrious language; address or speak of abusively.) should probably be relevant. Or not.

    La vida loca

    Saturday, May 7, 2016 3:41 AM
  • If you are using Command Parameters then the PL/SQL to_date function would not be used. You only use to_date if the values are embedded within the PL/SQL statement.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 9, 2016 12:18 PM
  • Thanks for the Reply Paul.. when I just pass the date, I get "ORA-01843: not a valid month"
    Monday, May 9, 2016 6:59 PM
  • Thanks for the reply Karen.

    There is no option for AddwithValue.. There is only Add and AddRange.

    I am using the Oracle.ManagedDataAccess.Client.

    I was using the system.Data.Oracle     but was getting errors indicating that I needed to be using the Managed DataAccess

    Monday, May 9, 2016 7:03 PM
  • Thanks for the Reply Paul.. when I just pass the date, I get "ORA-01843: not a valid month"
    I'm not really sure what your SQL statement looks like at this point? Are you still using the to_date function or are you using Command Parameters? You have to use one or the other.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 9, 2016 7:07 PM
  • when I look at the out of the code this is what I get...

    Parameters :Parm1 Value UserName
    Parameters :Parm2 Value Safetyasdfasdfasd
    Parameters :Parm3 Value
    Parameters :Parm4 Value
    Parameters :Parm5 Value Domain\Username
    Parameters :Parm6 Value 4/20/2016
    Parameters :Parm7 Value Night
    SQL Statement: Update Prod_Reports_Comments Set ShiftSupervisor = :Parm1, SafetyInfo = :Parm2, Equipment = :Parm3, DailyUpdates = :Parm4, Modified_By = :Parm5 WHERE Prod_Date = :Parm6 and Shift = :Parm7

    When I examine the contents of the Parm?'s  they are defined as type of

    Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2{126}

    Oh.. to answer your question...  I removed the to_date and just used the date value into the parameter.

    Tuesday, May 10, 2016 1:05 PM
  • Can you post the code that assigns the values to the Command Parameters? I'm assuming the Add method specifies a Date data type and the value assigned is stored in a DateTime variable?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by LandLord324 Tuesday, May 10, 2016 7:23 PM
    Tuesday, May 10, 2016 2:37 PM
  • Thanks Paul... looked at the overloads and saw where I could set the type...  all variables are strings with the exception of the date... and I ended up with the following and it works for me :)

                If i = 5 Then
                    cmd.Parameters.Add(":Parm" + (i + 1).ToString, Ora.OracleDbType.Date).Value = strParmeter(i)
    
                Else
                    cmd.Parameters.Add(":Parm" + (i + 1).ToString, Ora.OracleDbType.Varchar2).Value = strParmeter(i)
                End If

    Tuesday, May 10, 2016 7:26 PM
  • That does lead me to another question though...

    I was hoping to just have generic procedures in my class, in this example just to update fields in a table.  The user would pass the querry string, the name of the columns to be update and the values... I am wondering if I can tell what the column types might be before adding a parameter, as I need to set the datatypes when adding a parameter.  i can do a query on the field name from the table and then determine the datatype????

    Any ideas or suggestions?

    Tuesday, May 10, 2016 7:35 PM
  • What you are doing is creating dynamic SQL and there would be no way to know what the data types are from the column names. You would need to fetch the schema from the underlying table in order to determine the data types.

    With stored procedures you can use a CommandBuilder and DeriveParameters to handle it automatically but that will not work with SQL statements.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 10, 2016 8:19 PM