problem with Oracle and ole auto generate update

maryam saboor

hi every body! I use Oracle and for connection management I use OLEDB, the problem is where I want to do Update. the Update command is generated by an OleDBDataAdapter as the code below:

Public Sub UpdateData(ByVal oiDataSet As DataSet, ByVal siSQL As String, Optional ByVal siTableName As String = Nothing)

    Dim oDataAdapter As New OleDbDataAdapter

    Try
        If mbPiDisposed = True Then
            Throw New ObjectDisposedException(msPiModuleName, "This object has already been disposed. You cannot reuse it.")
        End If

        oDataAdapter.SelectCommand = New OleDbCommand(siSQL, mcnPiConnection, mtrPiTransaction)

        Dim oCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(oDataAdapter)

        If siTableName = Nothing Then
            oDataAdapter.Update(oiDataSet)
        Else
            oDataAdapter.Update(oiDataSet, siTableName)
        End If
    Catch ex As Exception
        Throw New Exception(msPiExceptionMessage, ex)
    Finally
    End Try
End Sub

when I call this Sub (I pass a simple "SELECT * FROM TABLE_NAME" and Updated data as "oiDataSet" to the OleDBDataAdapter as shown above) this error is shown: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information" I searched the Internet But every one said that you may have not define a primary key, but my table has one!! I should say the INSERT commands are OK, the problem is with UPDATE and DELETE commands. every suggestion would be appreciated

Mycroft Holmes

At a guess I would say that your select statement does not include the primary key for the table. As a general recommendation I suggest you learn about Data Access Layer (DAL) and not rely on the adapter auto generation tools, you will be a better developer faster if you understand the tools you are using.

maryam saboor

thank you for reply, yes I have not the Where Clause in my statement. if it was my choice, yes! as you said, it's the worst way to let the tools do the job!! but this code was a part of an old, big and critical project which my boss prefers not to change the code unless it's necessary!!! so I want to be sure there is not any other way except using "Where" and pass primary keys!! so I can content the boss to change the code!! :doh: by the way thanks for your kind recommendation, ANOTHER HELP EQUEST: do you know any other way??;) (my boss is so hard!)

Mycroft Holmes

Sorry, I would look at adding the primary key to the select statement as the minimal intervention required. Alternatively I would look if there is a combination of existing fields that could be used as a primary key and write my own update method. How can it be "old and critical" if your update does not work?

maryam saboor

thanks, about your question: we are moving the data base from SQL Server to Oracle. every thing goes OK by SQL, but this problem is by Oracle ;)

Mycroft Holmes

Ok then it is the different databases, you may have not set a primary key on the table to be updated in Oracle

Never underestimate the power of human stupidity RAH

maryam saboor

I found where is the problem. after all discussions, about how can sombody send an Update command to data base!!! the key is here: Every thing was ok in code and table difinitions. just in connection string a property should be defined: "OLEDB.NET=True" was easy?? :-\

ExploreLife

:-\ That's a perfect answer!!!!How did you know that? hard work for me ;P