DataAdapter Update Query

[pfildes]

I am currently having problems updating a DataSet via a DataAdapter and CommandBuilder. The following code works when adding new records;

        Dim myOleDbConnection As OleDb.OleDbConnection
        Dim myOleDbCommand As OleDb.OleDbCommand
        Dim myOleDbCommandBuilder As OleDb.OleDbCommandBuilder
        Dim myOleDbDataAdapter As OleDb.OleDbDataAdapter
        Dim myDataSet As New System.Data.DataSet
        Dim myDataTable As New System.Data.DataTable
        Dim myRow As System.Data.DataRow
        Dim myColumn As System.Data.DataColumn
        Dim myConnectionString As String
        Dim Loop1 As Integer

        Try
            myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                 "Data Source=" & gProject.ProjectDatabase.Trim & ";" & _
                                 "User Id=admin;Password=;"

            'Instatiate the database connection
            myOLEDBConnection = New OleDb.OleDbConnection(myConnectionString)

            'Create the SQL command
            myOLEDBCommand = New OleDb.OleDbCommand("Select * From PHOTOS", _
                                                    myOLEDBConnection)

            'Open an OLEDB adapter based on the SQL command and use this to
            'create a dataset
            myOLEDBConnection.Open()
            myOleDbDataAdapter = New OleDb.OleDbDataAdapter(myOleDbCommand)
            myOleDbDataAdapter.Fill(myDataSet)

            'Add the new record
            myDataTable = myDataSet.Tables(0)

            'Add a new row to this table
            myRow = myDataTable.NewRow()

            'Populate the fields in the row
            For Each myColumn In myDataTable.Columns
                For Loop1 = 1 To theColumnsCollection.Count
                    If (myColumn.ColumnName.Trim = theColumnsCollection(Loop1)) Then
                        myRow.Item(myColumn.Ordinal) = theDataItems(Loop1 - 1)
                        Exit For
                    End If
                Next
            Next

            'Add the new row to the DataTable rows collection
            myDataTable.Rows.Add(myRow)

            'Create a command builder
            myOleDbCommandBuilder = New OleDb.OleDbCommandBuilder(myOleDbDataAdapter)

            'Now update the dataset
            myOleDbDataAdapter.Update(myDataSet, myDataTable.TableName)

However the following code which was slightly amended to allow an edit process fails with the message;
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

            myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                 "Data Source=" & mvarDatabaseFile.Trim & ";" & _
                                 "User Id=admin;Password=;"
            mySQLString = "Select * From PHOTOS Where PHOTOS.ID=0"

            'Instatiate the database connection
            myOleDbConnection = New OleDb.OleDbConnection(myConnectionString)

            'Create the SQL command
            myOleDbCommand = New OleDb.OleDbCommand(mySQLString.Trim, myOleDbConnection)

            'Open an OLEDB adapter based on the SQL command and use this to
            'create a dataset
            myOleDbConnection.Open()
            myOleDbDataAdapter = New OleDb.OleDbDataAdapter(myOleDbCommand)
            myOleDbDataAdapter.Fill(myDataSet)

            'Add the new record
            myDataTable = myDataSet.Tables(0)

            'Cycle through all of the returned rows
            If (myDataTable.Rows.Count > 0) Then
                For Each myRow In myDataTable.Rows
                    .. make amendments
                Next
            End If

            'Create a command builder
            myOleDbCommandBuilder = New OleDb.OleDbCommandBuilder(myOleDbDataAdapter)

            'Now update the dataset
            If (myDataSet.HasChanges) Then
                myOleDbDataAdapter.Update(myDataSet, myDataTable.TableName)
            End If

I must stress that the table programmed for updates does not contain a primary key. I have a feeling that this might be the cause of my problem. If this is the case can anybody please recommend an alternative way of programming this?.

 

[PRPhx]

You could always build your update manually. That way you have control over what's happening. Also, it never hurts to assign a primary key, but I don't understand why your update would fail, and your add new records works.

 

[phinoppix]

I must stress that the table programmed for updates does not contain a primary key. I have a feeling that this might be the cause of my problem. If this is the case can anybody please recommend an alternative way of programming this?.

Yes you need a PK. Well, actually, the CommandBuilder is the one that needs it.

Workarounds: add a PK on the DB table and continue using the CommandBuilder, or manually build the the DataAdapter with your own defined CRUD Command objects. The latter requires some serious typing especially if you're updating a lot of fields, but you'll gain better control on the DDL commands, especially if your SELECT statement is complex or from a stored procedure.

 

[pfildes]

You could always build your update manually. That way you have control over what's happening. Also, it never hurts to assign a primary key, but I don't understand why your update would fail, and your add new records works.

Could it be that the CommandBuilder works when adding new records because my dataset returns all records from the PHOTOS table whereas the edited records are a subset of the original table?.

 

[MaeJams]

I agree with alphanytz...you definitely need a primary key. If you plan to continue using the CRUD statements that were auto-generated, be sure to check these SQL statements in your DataSet.Designer.vb file. The Update Command that is autogenerated for a table is not one you should use.

Let's say you have Table1 with 5 fields (Field1, Field2, Field3, Field4, and Field5), with Field1 as the PK. The auto-generated update command will use ALL fields for your WHERE clause (i.e. WHERE Field1=? And Field2=? And Field3=? And Field4=? And Field5=?)...even though you have Field1 as your PK. This caused me some heartburn, as my .NET app would raise an error telling me that the underlying data had been updated while I was working with it (which it really hadn't). To remedy this, you would need to change the WHERE clause for your update command to only use your PK field (i.e. WHERE Field1=?).

Good Luck!

 

[phinoppix]

Could it be that the CommandBuilder works when adding new records because my dataset returns all records from the PHOTOS table whereas the edited records are a subset of the original table?.

One serious limitation of CommandBuilder is that the SELECT command can only accept a plain SELECT statement - no joins, no Sp. WHERE clause is okay so you can limit the dataset.
But this does not in any way related to your problem.

Since you're fully depending on CommandBuilder to build the CRUD statements for you, one of the things it needs to know is how can it perform table UPDATE and DELETE for each modified records. It's (only) solution: find a PK. And if you're table does not have one, it throws an exception. Even a UNIQUE index won't help. You just have to have a PK for the table.

Going on the other way around, it's totally impossible for the CommandBuilder to find the PK from a complex query and especially from an SP.

 

[pfildes]

By adding a Primary Key to my table I got the CommandBuilder & the DataAdapter.Update to work successfully.

Thanks to everyone who replied to my query.

 

[PRPhx]

Good! At least you got it to work. I build a number of apps that print barcodes pages from Access/SQL Server. Since these tables don't contain much data, and the data doesn't stay around very long, I almost never use a PK. And I build all my queries by hand.

 

[rjoubert]

PRPhx...you say the data doesn't stay around very long, but why can't the table structure stay around? You can have an empty table sitting out there, with a PK defined, and only populate when you need to print out your barcodes. Or do the fields you need change with each query?

 

[PRPhx]

Oh, the tables do stay around. In fact every time the user starts the program, the program asks if the user want to empty the table. It's just as easy (and less confusing) to delete the contents. This way the barcodes get printed once.

Otherwise there really isn't a need for a PK, as data gets printed in the order it appears in the table.