3 Replies Latest reply: Feb 8, 2013 10:09 AM by Johnsung-Oracle RSS

    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