4 Replies Latest reply: Aug 7, 2006 9:31 PM by 527058 RSS

    ORA-00936 Missing Expression???

    503770
      I get this error...

      ORA-00936 Missing Expression???

      when I execute this code (the parameters have values in them. i suspect it is a type thing)...

      ---------------------------------------------------------------------------------
      Dim sSQL As String = _
      "UPDATE TBLICPMETHODS SET Frequency = @Frequency, " & _
      "Calibration = @Calibration, QCValue = @QCVAlue" & _
      " WHERE Frequency = '" & Session("EditFrequency") & "' AND MethodNumber = " & ddlMethods.SelectedValue

      Dim connConnection As New OleDbConnection(Application("ERSconnectionStr"))

      connConnection.Open()

      Dim cmdCommand As New OleDbCommand(sSQL, connConnection)

      Dim prmFrequency As OleDbParameter = _
      New OleDbParameter("@Frequency", OleDbType.VarChar, 75)
      prmFrequency.Value = sFrequency
      cmdCommand.Parameters.Add(prmFrequency)

      Dim prmCalibration As OleDbParameter = _
      New OleDbParameter("@Calibration", OleDbType.Double, 75)
      prmCalibration.Value = CDbl(sCalibration)
      cmdCommand.Parameters.Add(prmCalibration)

      Dim prmQCValue As OleDbParameter = _
      New OleDbParameter("@QCValue", OleDbType.Double, 75)
      prmQCValue.Value = CDbl(sQCValue)

      cmdCommand.Parameters.Add(prmQCValue)
      ' MessageBox.show(cmdCommand.CommandText + ":Frequency=" + e.Item.Cells(2).Text + ":Calibration=" + e.Item.Cells(3).Text + ":QCValue=" + e.Item.Cells(4).Text)
      Try
      cmdCommand.ExecuteNonQuery()
      Catch ex As OleDbException
      MessageBox.show(ex.Message)
      End Try
      ----------------------------------------------------------------------------------------------
        • 1. Re: ORA-00936 Missing Expression???
          503770
          When I run the query...

          UPDATE TBLICPMETHODS SET Frequency = 'Ag 328.068', Calibration = .5, QCValue = .5
          WHERE Frequency = 'Ag 328.068' AND MethodNumber = 1

          in SQL Navigator is works fine. I'm thinking the data types aren't correct. Can someone help me line up my OLEDB data types to Oracle data types...

          Oracle Table Types...
          VARCHAR2
          NUMBER

          OLEDB Data Types...
          OleDbType.VarChar
          OleDbType.Double

          Are these the wrong OLEDB data types to use for those oracle data types???

          Thanks.
          • 2. Re: ORA-00936 Missing Expression???
            503770
            Incidentally, if I just execute my sql like this...

            Dim sSQL As String = _
            "UPDATE TBLICPMETHODS SET Frequency = '" & sFrequency & "', " & _
            "Calibration = " & sCalibration & ", QCValue = " & sQCValue & "" & _
            " WHERE Frequency = '" & Session("EditFrequency") & "' AND MethodNumber = " & ddlMethods.SelectedValue

            instead of using the cmdCommand.Parameters.Add method IT WORKS!!

            So I'll go ahead and use that but I really would like to know what I was doing wrong. I tried quite a few different data type permutations to no avail.

            Cheers,
            Craig
            • 3. Re: ORA-00936 Missing Expression???
              521084
              I am having the same problem. I am trying to call Update(dset, "tablname")

              When I call dset.HasChanges() it is true. If I call GetChanges() I get a row that needs to be updated. I have changed my update query to only one value. Since I need to supply the update parameters map to the dataset, I can't just build a string with the values. The same code works connecting to Access. What is the deal with Oracle?

              The program is vb.net 1.1, the Oracle is 9i.
              • 4. Re: ORA-00936 Missing Expression???
                527058
                The problem is that OLEDB doesn't support named parameters.

                If you replace the @parameter names with question marks in the SQL statement

                eg

                "UPDATE TBLICPMETHODS SET Frequency = @Frequency, " & _
                "Calibration = @Calibration, QCValue = @QCVAlue"

                with

                "UPDATE TBLICPMETHODS SET Frequency = ?, " & _
                "Calibration = ?, QCValue = ?"

                and add the parameters in the correct order, it should work. The parameter declaration are still the same (@ symbol optional)

                --

                Otherwise you could try switching to the Oracle.NET provider.

                The trick with it though is that you have to replace the @ with a : in the sql statement and leave it out of the parameter name

                eg

                "UPDATE TBLICPMETHODS SET Frequency = :Frequency, " & _
                "Calibration = :Calibration, QCValue = :QCVAlue" & _

                ....

                Dim prmQCValue As OracleParameter = _
                New OracleParameter("QCValue", OracleType.Double, 75)

                HTH
                :-)