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

Posted on 2009-04-28
Last Modified: 2013-11-25
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...


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



UPDATE tablename SET code = @code, full_code = @full_code WHERE  id = @id AND type = @type 



"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@code"."



        Public Sub ApplyChanges()


                Dim builder As New Odbc.OdbcCommandBuilder(_oda)



            Catch ex As Exception

                _oda.UpdateCommand = New OdbcCommand(GenSqlUpdate())


            End Try

        End Sub

Open in new window

Question by:pbfonline
    LVL 83

    Expert Comment

    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?

    Accepted Solution

    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.

    Author Comment

    FYI - the code I used to get around this problem is included:
            Public Sub ApplyChanges()
                    '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
                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
                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
                sSQL &= " WHERE " & tmpSQL.Substring(4)
                oc.CommandText = sSQL
                Return oc
            End Function

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now