3 Replies Latest reply on Feb 8, 2013 4:09 PM by Johnsung-Oracle

    Update problem

    479780
      Hi All,

      Can someone please tell me what is wrong with this code. There is no error but it isn't updating the database.

      Private Sub tsbUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbUpdate.Click
      Dim oraConnect As New OracleConnection("Data Source=TRAINING; User id=XXXXX; Password=XXXXXX;")
      Dim myAdapter As New OracleDataAdapter("SELECT * FROM ITEMS", oraConnect)
      Dim Update As New OracleCommand("UPDATE ITEMS SET ITEM_TYPE, = @ItemType, IMEI_NO = @imeino, BRAND = @Brand, WHERE ITEM_ID = @ItemNo", oraConnect)

      Dim myTable As New DataTable

      Try
      oraConnect.Open()

      Update.Parameters.Add("@ItemType", OracleDbType.Varchar2, 100, "ItemType")
      Update.Parameters.Add("@IMEI_NO", OracleDbType.Varchar2, 100, "imeino")
      Update.Parameters.Add("@BRAND", OracleDbType.Varchar2, 100, "Brand")


      myAdapter.UpdateCommand = Update
      myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

      myAdapter.Fill(myTable)

      'save changes
      myAdapter.Update(myTable)
      MessageBox.Show("Record updated successfully", "Visual Sales Master [Record Update]", MessageBoxButtons.OK, MessageBoxIcon.Information)

      Catch ex As Exception
      Admin.stbMain.Text = "Error While Updating Record..."
      MessageBox.Show(ex.Message, "Visual Sales Master [Update Error]")
      Admin.stbMain.Text = "Ready"
      End Try

      oraConnect.Close()


      End Sub
        • 1. Re: Update problem
          572474
          Try to fit in Update.ExecuteNonQuery such as:
          http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

          Ming Man
          • 2. Re: Update problem
            ozz232
            Hello,
            I am trying to do something similar in c# and it doesnt work. What is the issue here?
            Thanks.
            O
            • 3. Re: Update problem
              Johnsung-Oracle
              Hello ozz232,

              it might be better to start a new thread on this with your code used.
              As this thread is 4.5 years old and I'm not sure what the code shown is trying to do exactly - it doesn't look quite right.

              Here is an example of using an OracleDataAdapter to do an update that I just ran and tested. I created some objects similar to the
              above code.
                  create table items
                  (ITEM_TYPE varchar2(100)
                  , IMEI_NO varchar2(100)
                  , BRAND   varchar2(100)
                  , ITEM_ID  number
                 );

              insert into  items
                values ('dvd','12345','Oracle',1);

              commit;
              Imports Oracle.DataAccess.Client

              Module Module1

                  Sub Main()

                      Dim oraConnect As New OracleConnection("Data Source=ORCL; User id=scott; Password=tiger;")
                      Dim myAdapter As New OracleDataAdapter("SELECT * FROM ITEMS", oraConnect)
                      Dim Cmb As New OracleCommandBuilder(myAdapter)

                      Dim myTable As New DataTable

                      Try
                          oraConnect.Open()

                          myAdapter.Fill(myTable)

                          ' make a change to a row in  the DataTable
                          If myTable.Rows.Count > 0 Then
                              myTable.Rows(0)("ITEM_TYPE") = "test"
                          End If

                          'save changes
                          myAdapter.Update(myTable)
                          Console.Write("Record updated successfully")

                      Catch ex As Exception
                          Console.Write("Error While Updating Record...")
                          Console.Write(ex.Message, "Update Error")
                      End Try

                      oraConnect.Close()


                  End Sub

              End Module
              OUTPUT
              --------
              ITEM_TYPE
              ----------------------------------------
              IMEI_NO
              ----------------------------------------
              BRAND
              ----------------------------------------
              ITEM_ID
              ----------
              dvd
              12345
              Oracle
              1


              After running the code


              SQL> /

              ITEM_TYPE
              ----------------------------------------
              IMEI_NO
              ----------------------------------------
              BRAND
              ----------------------------------------
              ITEM_ID
              ----------
              test <-- updated row
              12345
              Oracle
              1