C# - Using DataAdapter to Update SQL table from a DataTable - SQL table not updating

Question

I select * from an Excel spreadsheet into DataTable dt. I want to take those values and update the SQL table. The SQL table exists because of a manual import to SQL from the original Excel spreadsheet, has a primary key set. The user updates the excel sheet and I need to update the SQL values. I am setting the dt.RowState to modified in an effort to invoke the update. I get no error but the SQL table does not update. Previous test show my SQL permissions and connection is good, I can modify the table.

connectionToSQL = new SqlConnection(SQLConnString);
connectionToSQL.Open();
var cmd = new SqlCommand("SELECT * FROM TAGS$",connectionToSQL);                 
var da = new SqlDataAdapter(cmd);
var b = new SqlCommandBuilder(da);
foreach (DataRow r in dt.Rows)
{
    r.SetModified();
}
da.Update(dt);   

Answer 1

Try this:

using System.Data;
using System.Data.SqlClient;
using System;
namespace Q308507 {
    class Class1 
    {
        static void Main(string[] args) 
        {
            SqlConnection cn = new SqlConnection();
            DataSet CustomersDataSet = new DataSet();
            SqlDataAdapter da;
            SqlCommandBuilder cmdBuilder;
            // Set the connection string of the SqlConnection object
            // to connect to the SQL Server database in which you
            // created the sample table.
            cn.ConnectionString =
            "Server=server;Database=northwind;UID=login;PWD=password;";
            cn.Open();      
            // Initialize the SqlDataAdapter object by specifying a
            // Select command that retrieves data from the sample table.
            da = new SqlDataAdapter("select * from CustTest order by CustId", cn);
            // Initialize the SqlCommandBuilder object to automatically
            // generate and initialize the UpdateCommand,
            // InsertCommand, and DeleteCommand properties
            // of the SqlDataAdapter.
            cmdBuilder = new SqlCommandBuilder(da);
            // Populate the DataSet by running the Fill method
            // of the SqlDataAdapter.
            da.Fill(CustomersDataSet, "Customers");
            // Display the Update, Insert, and Delete commands
            // that were automatically generated
            // by the SqlCommandBuilder object.
            Console.WriteLine(
                "Update command Generated by the Command Builder : ");
            Console.WriteLine(
                "==================================================");
            Console.WriteLine(
                cmdBuilder.GetUpdateCommand().CommandText);
            Console.WriteLine("         ");
            Console.WriteLine(
                "Insert command Generated by the Command Builder : ");
            Console.WriteLine(
                "==================================================");
            Console.WriteLine(cmdBuilder.GetInsertCommand().CommandText);
            Console.WriteLine("         ");        
            Console.WriteLine(
                "Delete command Generated by the Command Builder : ");
            Console.WriteLine(
                "==================================================");
            Console.WriteLine(cmdBuilder.GetDeleteCommand().CommandText);
            Console.WriteLine("         ");
            // Write out the value in the CustName field before
            // updating the data using the DataSet.
            Console.WriteLine("Customer Name before Update : " +
                CustomersDataSet.Tables["Customers"].Rows[0]["CustName"]);
    
            // Modify the value of the CustName field.
            CustomersDataSet.Tables["Customers"].Rows[0]["CustName"] = "Jack";
            // Post the data modification to the database.
            da.Update(CustomersDataSet, "Customers");        
            Console.WriteLine("Customer Name updated successfully");
            // Close the database connection.
            cn.Close();
            // Pause
            Console.ReadLine();
        }
    }
}

Answer 2

I think the auto generated SqlCommands generated by SqlCommandBuilder are not really going to be suitable in your case (If I have understood the question correctly). In the WHERE clause of the SQL Update statements generated by SqlCommandBuilder,the values of ALL columns are compared with their original values (as determined by the original data values in the DataRow). If the original values are not matched in the destination database, no rows will get updated.

This link to the SqlCommandBuilder may help :

http://msdn.microsoft.com/en-us/library/ms971491.aspx

From that link, try to understand : "adCriteriaAllCols" as that is what SqlCommandBuilder uses. I suspect what you want is "AdCriteriaKey" behaviour.

One possible solution might be to NOT use SqlCommandBuilder, and simply write the INSERT/UPDATE/DELETE SqlCommands yourself, and attach them to SqlDataAdapter.InsertCommand, UpdateCommand,and DeleteCommand.
There's some example code at : http://support.microsoft.com/kb/308055

EDIT : the SqlCommandBuilder in .net versions 2.0 and later have a ConflictOption property. The default used is : CompareAllSearchableValues. Try using : OverwriteChanges, which will cause the WHERE clause generated in SQL statements to only compare the primary key values.

Answer 3

I tried commenting but was told to re-read the question. So I did, and it didn't help :) You have very little code in that example which ties dt (you say this is populated from Excel) to the database. You have the variables connectionToSQL, cmd, da and b. These are connected to a database. You then iterate through dt, which isn't. This is why in my comment I asked for the example sourcecode where you're modifying rows in dt - because I presumed you would have that somewhere, in order to expect that the changes would jump from Excel (which populated dt) to your database.

I see that you're calling da.Update(dt); Try opening a new dataset from the database, and going through the rows in dt, applying the changes to the rows in your new dataset. From what I can tell - and there's not much code there - no commands are issued because the dataadapter knows the internal rows of dt did not come from its datasource. That's my stab, anyway.