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

Question

I select * from an Excel spreadsheet into 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();
   }
 }
}