Probably simple question on DataView/Dataset updating

[Ginolard]

I'm sure this is blindingly simple and I'm just being a nonce.

I have a Dataset containing a single table. During my code's execution (this is a console application) I create a DataView to select rows meeting certain criteria as so

 Dim dv As DataView = DS.Tables(0).DefaultView
        dv.AllowEdit = True
        dv.RowFilter = String.Format("IMPORTED_DATE <= '" + DeletionDate.ToString("yyyy/MM/dd") & "'" + _
                                    " AND IMPORTED ='Y' AND FILE_NAME LIKE '" + mb & "%'")

Depending on the success of the next piece of code I want to set the value of the IMPORTED field to "E" for the relevant row and then write those changes back to the database.

This is what I'm trying but nothing happens

                            drv.BeginEdit()
                            drv.Item("IMPORTED") = "E"
                            drv.EndEdit()
                            DS.AcceptChanges()

Is this possible and, if so, how?

[jmcilhinney]

AcceptChanges doesn't save anything. It just accepts the changes in the DataSet. If you want to save the changes then you need to execute the appropriate SQL code, which you do with the same DataAdapter you used to get the data in the first place. I suggest that you follow the Database FAQ link in my signature and check out some of the ADO.NET resources.

[Ginolard]

Hi, sorry, I should've made it clear that I do the update right before my application ends.

If DS.HasChanges Then
        Dim cb As OracleCommandBuilder = New OracleCommandBuilder(da)

        WriteToLog("Writing changes to database", LogTypes.Log)
        Try
            da.Update(ds)
        Catch ex As OracleException
            WriteToLog("WriteChangesToDb:  Error writing changes to database.  Error msg = " + ex.Message, LogTypes.Err)
        Finally
            ds.Dispose()
            cb.Dispose()
            da.Dispose()
        End Try
 End If

[jmcilhinney]

Calling Update after AcceptChanges is useless because there are no changes to save. Get rid of the AcceptChnages call altogether. Calling Update on a DataAdapter implicitly calls AcceptChanges so it serves no useful purpose anyway.

[Ginolard]

Duh. Might help if I put the code in the write Try Catch block so that even executes!

Of course, that has now thrown a different error.

Unhandled Exception: System.InvalidOperationException: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information

[jmcilhinney]

There's also no point calling BeginEdit and EndEdit when editing your data. If there's no possibility of calling CancelEdit then the other two are pointless too. Just go ahead and edit and the changes are committed immediately instead of waiting for the EndEdit call.

As for the question, have you removed the call to AcceptChanges before testing HasChanges?

[Ginolard]

Yes I have removed the AcceptChanges call. Here is the current code.

  

  Dim dv As DataView = DS.Tables(0).DefaultView
        dv.AllowEdit = True
        dv.RowFilter = String.Format("IMPORTED_DATE <= '" + DeletionDate.ToString("yyyy/MM/dd") & "'" + _
                                    " AND IMPORTED ='Y' AND FILE_NAME LIKE '" + mb & "&#37;'")

For Each drv As DataRowView In dv

            MessageID = drv("MESSAGE_ID").ToString
            Try
                item = ObjSession.GetItemFromID(MessageID)
            Catch ex As Exception
                drv.Item("IMPORTED") = "E"
            End Try
Next

If DS.HasChanges Then

        Dim cb As OracleCommandBuilder = New OracleCommandBuilder(da)
        
        WriteToLog("Writing changes to database", LogTypes.Log)
        Try
            da.Update(ds)
        Catch ex As OracleException
            WriteToLog("WriteChangesToDb:  Error writing changes to database.  Error msg = " + ex.Message, LogTypes.Err)
        Finally
            ds.Dispose()
            cb.Dispose()
            da.Dispose()
        End Try
End If

Edit: After a bit of searching it seems that this can be caused if the DB table doesn't have a primary key (which it doesn't). Great. I don't have the rights to modify the DB schema......

[jmcilhinney]

Are you sure that that line that sets the IMPORTED column is actually being executed? Have you set a breakpoint on it?

Also, you have yet another superfluous line of code. DataViews allow editing by default, so setting AllowEdit to True is of no value.

[Ginolard]

Oh, yes it's being executed. DS.HasChanges is False before it and True After it

It looks like it's this primary key issue. Not sure how I can get round that other than by directly running an SQL Update query on that particular record. I wanted to avoid that though and do everything via the Dataset.

[jmcilhinney]

If the table doesn't have a primary key then you can't use a CommandBuilder, that's all. It's not a big deal. I haven't used one for a long, long time and lots of people never use them. Just write your own SQL code to delete, insert and update. Presumably you have some way to uniquely identify a record even without a PK.

That said, I'm guessing that whether the database table has a primary key is irrelevant as long as the DataTable has one. If that's true then you can set the PrimaryKey property of the DataTable after populating it and the CommandBuilder should still work.