asked on

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information

I'm aware that if I don't have a primary key defined, the commandbuilder cannot generate the Update command. This makes sense... But how can I override this so I can do a dataAdapter.Update(_dataTable) when I don't have access to the database?

The situation is that I'm trying to update an existing database with new data, and I can't change the structure of the tables. The table currently doesn't have any primary keys. I am doing a racetrack comparison of the existing data vs. the new data and applying changes and adding rows where necessary. This all works fine in memory, when when I try to Update the datatable back to the database, it fails *1 (obviously because it can't create the update statement dynamically.

So, my initial queistion is: Can I tell the commandbuilder what columns to use as the primary key? If so, How?

Also, I tried to generate the SQL myself (which was easy enough *2) and I added it to the dataAdapter. But, it is telling me that I don't have the scalar variables for the sql defined *3. How can I build the sql so the parameters are just taken "order specific" so the datatable can use my SQL?

Here is the code I'm trying to get working *4. You can assume that there are pending changes to the datatable, which was populated from a table without a primary key defined. You can also assume that the GenSqlUpdate() statement generates valid SQL... The SQL just doesn't have the parameters defined...

*1
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
 
*2
UPDATE tablename SET code = @code, full_code = @full_code WHERE  id = @id AND type = @type 
 
*3  
"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@code"."
 
*4
        Public Sub ApplyChanges()
            Try
                Dim builder As New Odbc.OdbcCommandBuilder(_oda)
                Debug.Print(builder.GetUpdateCommand.CommandText)
                _oda.Update(_dt)
            Catch ex As Exception
                _oda.UpdateCommand = New OdbcCommand(GenSqlUpdate())
                _oda.Update(_dt)
            End Try
        End Sub

Open in new window

Nasir Razzaq

The trouble is that the TableAdapter uses the primary key field in the UpdateCommand. If there is a primary key, it ensures that there would not be any duplicates. If there is no primary key, even though there are no duplicates in the column, tableadapter wont have it as it could result in errors. Is it an option to go through the datatable and run the update, insert and delete commands yourself depending on the rowstatus?

ASKER CERTIFIED SOLUTION

pbfonline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pbfonline

ASKER

FYI - the code I used to get around this problem is included:


        Public Sub ApplyChanges()
            Try
                'If the table has a primary key, the command builder creates all statements (insert, update, delete)
                Dim builder As New Odbc.OdbcCommandBuilder(_oda)
 
                'But if not, and we know what the keys are, we can override it with a positional based parameterized update statement
                If Not _configFile.PrimaryKeyDefined And _configFile.RawKeyString.Length > 0 Then
                    _oda.UpdateCommand = GenSqlUpdateCommand()
                End If
 
                _oda.Update(_dt)
 
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
 
 
        Public Function GenSqlUpdateCommand() As OdbcCommand
            Dim oc As New OdbcCommand()
            Dim sSQL As String = "UPDATE " & _configFile.TableName()
 
            Dim tmpSQL As String = ""
            For Each tmpColumn As GeneralColumn In _destColumns
                If Not isKey(tmpColumn.Name) Then
                    tmpSQL &= ", " & tmpColumn.Name & " = ?"
                    oc.Parameters.Add("@" & tmpColumn.Name, tmpColumn.Type, tmpColumn.Size, tmpColumn.Name)
                End If
            Next
            sSQL &= " SET " & tmpSQL.Substring(2)
 
            tmpSQL = ""
            For Each tmpColumn As GeneralColumn In _destColumns
                If isKey(tmpColumn.Name) Then
                    tmpSQL &= " AND (" & tmpColumn.Name & " IS NULL OR " & tmpColumn.Name & " = ?) "
                    oc.Parameters.Add("@" & tmpColumn.Name, tmpColumn.Type, tmpColumn.Size, tmpColumn.Name)
                End If
            Next
            sSQL &= " WHERE " & tmpSQL.Substring(4)
            oc.CommandText = sSQL
            Return oc
        End Function

Open in new window