[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3091
  • Last Modified:

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

0
pbfonline
Asked:
pbfonline
  • 2
1 Solution
 
CodeCruiserCommented:
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?
0
 
pbfonlineAuthor Commented:
When the table has a primary key, it works fine.  So, I took a look at the SQL Update Command built by the CommandBuilder and created my own function to do that with my internal knowledge of the data table (ie, I told my function what to use as primary keys).  The resulting SQL I generated looked something like this:

UPDATE CPT4 SET code = ?, descrip = ?, status = ? WHERE ((? = 1 AND id IS NULL) OR (id = ?))

It seems to work fine - provided I am correct in my assumption about the unique keys I configure the update to work with - which is not defined on the database as a primary key.
0
 
pbfonlineAuthor Commented:
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

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now