DbDataAdapter.Update() not working?

[TheMan1]

I'm having some trouble updating retrieved data in a DataSet, in C#. Here's the code:

(I'm using the Firebird Client btw)

static void Main(string[] args)
        {
 
            DbProviderFactory f = DbProviderFactories.GetFactory("FirebirdSql.Data.FirebirdClient");
            DbConnection conn = f.CreateConnection();
            conn.ConnectionString = "user=admin;password=password;database=TEST.FDB;ServerType=1";    
            DbCommand command = f.CreateCommand();
            command.Connection = conn;
            DbDataAdapter adapter = f.CreateDataAdapter();
 
            //Query database. Get the first row of the result.
            command.CommandText = "SELECT * FROM testtable WHERE testid = 2";
            adapter.SelectCommand = command;
            DataSet data = new DataSet();
            adapter.Fill(data);
            DataRow row = data.Tables[0].Rows[0];
 
            //Modify that row.
            row.BeginEdit();
            row.ItemArray[1] = "TEST";  //change the value in the second column.
            row.EndEdit();
 
            //Apparently the PK needs to be set prior to updates... shouldn't matter.
            DataColumn[] columns = new DataColumn[1];
            columns[0] = data.Tables[0].Columns[0];     //first column is PK.
            data.Tables[0].PrimaryKey = columns;
 
            //Perform the update.
            command.CommandText = "";
            adapter.UpdateCommand = command;
            adapter.Update(data);
}

Line 29 generates an exception, because the CommandText is null. What would I put as the CommandText to allow my small row change above in the code to be executed and committed? If I try to do adapter.Update(data) by itself it says I need to define an UpdateCommand, so I need to have it.

Any help would be appreciated, since this is driving me nuts.

[Curtis Rutland]

You need to write a SQL UPDATE command. Write one that will update all values in the query, and use parameters.

[TheMan1]

You need to write a SQL UPDATE command. Write one that will update all values in the query, and use parameters.

But doesn't that defeat the purpose of modifying the row at line 20, if I'm just going to write SQL that does the same thing? What's the point of having Update() then?

[mldisibio]

insertAlias is correct, and often writing your own UPDATE command is the most efficient. You can also specify a stored procedure for the UPDATE command.
However, there is also the possibility of letting the Framework generate the commands for you. If you ever look at the final output, it basically writes a sql statement that reads like a congressional bailout plan...covers every single row and column but you can't tell what it does!

In any case (MSDN):

"If INSERT, UPDATE, or DELETE statements have not been specified, the Update method generates an exception. However, you can create a SqlCommandBuilder or OleDbCommandBuilder object to automatically generate SQL statements for single-table updates if you set the SelectCommand property of a .NET Framework data provider. Then, any additional SQL statements that you do not set are generated by the CommandBuilder. This generation logic requires key column information to be present in the DataSet. For more information see
Automatically Generating Commands ."

[Curtis Rutland]

But doesn't that defeat the purpose of modifying the row at line 20, if I'm just going to write SQL that does the same thing? What's the point of having Update() then?

No, because you use parameters in your statement:

UPDATE table SET field1=@field1

So that it is a generic update statement.

The adapter uses that statement to do the update for you.

[mldisibio]

The DataSet is a container for your data. It knows nothing about the source of that data.
The DataAdapater facilitates communication between the DataSet and the actual database.

When you 'update' a field in a DataSet, the DataSet tracks those changes for you in a way that makes you think you are dealing directly with a database: it keeps track of the RowState with states such as "Modified, Deleted, Inserted".
So when you change a field, it knows it is "updated", and if somethings asks it for "give me all your updated fields" it knows what to return.

The DataAdapter is the one asking "Give me all your updated fields." However, the DataAdapter doesn't necessarily know what to do with it. If something asks it to "run the update" it knows what to do IF it has been configured.

Your code is the one asking the DataAdapter to run its update. It has its connection configured, but it also needs the actual UPDATE statement configured, just as you configured the SELECT statement.

What confuses many coders at first is that if you drag and drop Adapters and DataSets into the designer, much of that 'configuration' is auto-generated.
As I mentioned in the previous post, you can even have the actual sql update statements auto-generated, and some tutorials lead developers down that mudslide.

[wgale025]

try DbCommandBuilder!!
DbCommandBuilder can autocreate insertCommand,updateCommand,deleteCommand

[wgale025]

DbProviderFactory f = DbProviderFactories.GetFactory("FirebirdSql.Data.FirebirdClient");
            DbConnection conn = f.CreateConnection();
            conn.ConnectionString = "user=admin;password=password;database=TEST.FDB;Ser  verType=1";
            DbCommand command = f.CreateCommand();
            command.Connection = conn;
            DbDataAdapter adapter = f.CreateDataAdapter();
 
            //Query database. Get the first row of the result.
            command.CommandText = "SELECT * FROM testtable WHERE testid = 2";
            adapter.SelectCommand = command;
            DataSet data = new DataSet();
            adapter.Fill(data);
            DataRow row = data.Tables[0].Rows[0];
 
            //Modify that row.
            row.BeginEdit();
            row.ItemArray[1] = "TEST";  //change the value in the second column.
            row.EndEdit();
 
            //Apparently the PK needs to be set prior to updates... shouldn't matter.
            DataColumn[] columns = new DataColumn[1];
            columns[0] = data.Tables[0].Columns[0];     //first column is PK.
            data.Tables[0].PrimaryKey = columns;
 
            //Perform the update.
            //command.CommandText = "";
            //adapter.UpdateCommand = command;
            //dbCommandBuilder can autocreate updatecommand
            DbCommandBuilder dbCmdBuilder = f.CreateCommandBuilder();
            dbCmdBuilder.DataAdapter = adapter;
            adapter.Update(data);
 

Bold font is revised


//Perform the update.
//command.CommandText = "";
//adapter.UpdateCommand = command;
//dbCommandBuilder can autocreate updatecommand
DbCommandBuilder dbCmdBuilder = f.CreateCommandBuilder();
dbCmdBuilder.DataAdapter = adapter;
adapter.Update(data);


sorry my english is poor!!