This discussion is archived
3 Replies Latest reply: Feb 8, 2013 8:09 AM by Johnsung RSS

Update problem

479780 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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
    207466 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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