ERROR WHEN USING DBADAPTER DYNAMIC TO GENERATE DELETE,UPDATE COMMAND

Question

ERROR WHEN USING DBADAPTER DYNAMIC TO GENERATE DELETE,UPDATE COMMAND

EXCEPTIN：

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not contain a row version column.”

PLATFORM：

windows 10

.net 5

vs2019 community

DATABASE：达梦(DAMENG)8

SQL DODE：

string sqlString="select * from DATABASE where ID=3";

TABLE CREATE SQL LIKE THIS :ID INT IDENTITY PRIMARY KEY,,

BUT DDL LIKE THAT:ID INT NOT NULL AUTO_INCREMENT,

C#代码：using (DmConnection conn = new DmConnection(strConnMobile))

{

   using (DmDataAdapter ap = new DmDataAdapter(sqlString, conn))

  {

         ap.Fill(myDatatable);

         DmCommandBuilder builder = new DmCommandBuilder(ap);

         

        //some code to chang myDatatable

       builder.GetUpdateCommand();

//throw System.InvalidOperationException:Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not contain a row version column.

  }

}

WHY AND HOW TO SOLVE IT ,THANKS FOR ANY ANSWER

Answer 1

Hi @Mark Knopfler , Welcome to Microsoft Q&A,

The error you're encountering is due to the way DmCommandBuilder (or any other DbCommandBuilder) generates SQL commands for updating or deleting records. The error message indicates that the SelectCommand does not contain a row version column, which is necessary for optimistic concurrency control. Without a row version column, the command builder cannot generate a safe UpdateCommand or DeleteCommand because it can't determine whether a row has changed since it was retrieved. If you can't add a row version column, you'll need to manually create the UpdateCommand and DeleteCommand for the DmDataAdapter. You can use the DmCommandBuilder to get the base command and then modify it as needed:

DmCommandBuilder builder = new DmCommandBuilder(ap);

// Manually create the UpdateCommand
DmCommand updateCommand = builder.GetUpdateCommand();
updateCommand.CommandText = "UPDATE YourTable SET Column1 = @Column1 WHERE ID = @ID";

// Add parameters as needed
updateCommand.Parameters.Add(new DmParameter("@Column1", SqlDbType.YourType) { Value = value });
updateCommand.Parameters.Add(new DmParameter("@ID", SqlDbType.YourType) { Value = id });

ap.UpdateCommand = updateCommand;

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.