New User: request for Update Assistance

[rlessor]

Sample Visual Studio 2008 code attached/below works fine where the direct connection retrieves the Oracle records and writes to the console fine. Now the next step is to use the data retrieved and update the company number string within the Oracle table. What needs to be revised using the the Devart connector to make this happen? Note we do not have a lot of Oracle experience and have decided to try to use the Devart connection tools versus the Oracle connectors (last test before purchasing). Any and all help is greatly appreciated.

Code: Select all

Imports Devart.Data.Oracle

Module Module1
    
    Sub Main()
        Dim myConnString As String = "User Id=OraUser;Password=OraDB; Server=pclinux; Direct=True; sid=TEST"
        Dim mySelectQuery As String = "SELECT * FROM da.faasset WHERE fast_cat_code='CRANES' order by fast_cat_code"        
        Dim myConnection As New OracleConnection(myConnString)
        Dim myCommand As New OracleCommand(mySelectQuery, myConnection)

        myConnection.Open()
        Try
            Dim myReader As OracleDataReader = myCommand.ExecuteReader()
            While myReader.Read()
		  strAssetNumber = Trim(myReader.GetString(myReader.GetOrdinal("fast_code")))
		  Console.WriteLine(strAssetNumber; + ", " _
    + myReader.GetString(myReader.GetOrdinal("fast_comp_code")) + ", " _
                + myReader.GetString(myReader.GetOrdinal("fast_cat_code")) + ", " _
                + myReader.GetString(myReader.GetOrdinal("fast_code")) + ", " _
                + myReader.GetString(myReader.GetOrdinal("fast_desc")))
            End While
            myReader.Close()
        Finally
            myConnection.Close()
        End Try

    End Sub
    
End Module

[StanislavK]

You can execute the SQL update command via the OracleCommand class, for example:

Code: Select all

Dim myConnString As String = "User Id=OraUser;Password=OraDB; Server=pclinux; Direct=True; sid=TEST" 
Dim myUpdateQuery As String = "update da.faasset set company_number = 1000 where fast_cat_code='CRANES'"
Dim myConnection As New OracleConnection(myConnString) 
Dim myCommand As New OracleCommand(mySelectQuery, myConnection) 

myConnection.Open() 
Try 
	Dim affected As Integer = cmd.ExecuteNonQuery()
Catch
	MessageBox.Show("Error encountered during Update operation.")
Finally 
	myConnection.Close() 
End Try

If you need to perform several inserts or updates at once, this can be made more effectively by using Array Binding. For more information on the Array Binding, please see the "Working with Array Binding" topic in the "Using dotConnect for Oracle" section of our help:
http://www.devart.com/dotconnect/oracle/docs/

[rlessor]

I was probably not clear enough in my test scenario. I do not want to just update all records with a new value for a field but rather based upon a test (I did not include the test itself in the code as it really is unnecessary for the update code). So here goes again.

I want to read records within an Oracle table and if a test is true update one field within the record else read the next record and test once again. While this could be done outside of a Visual Studio application with Toad we are trying to understand the basics of reading records using the Devart connections and updating or inserting data as needed. Once again we are very raw when it comes to Oracle so my sample code is truly a basic starting point.

Am I clear enough in my desire to update records and the need for truly basic assistance to get us started?

[StanislavK]

Please try the OracleDataTable component. You can fill it with data from a table in the Oracle database, and then work with the data almost as with a simple two-dimensional array. To update the data, you can use the single OracleDataTable.Update() method.

Please see the "OracleDataTable class" and "OracleDataTable Advanced Features" (in the "Database Application Development Concepts" section) topics of our help:
http://www.devart.com/dotconnect/oracle/docs/