title: SqlCommandBuilder With Convert Statement
body: <p>I have an application that's built in .NET language.</p>
  
  <p>In this application we mainly read/write to the database (SQL Server 2005).
  Sometimes (for a single input) I just use the SQL query, for example:</p>
  
  <pre><code>commandText = "INSERT INTO Test_Table (Number) VALUES ('10')";
  command = new System.Data.SqlClient.SqlCommand(commandText, connection);
  command.ExecuteNonQuery();
  </code></pre>
  
  <p>If I want to update a bunch of records in my database, I use the <code>SqlCommandBuilder</code> class, as in this example:</p>
  
  <pre><code>adapter = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM Test_Table WHERE Number = '9'",connection);
  
  commandbuilder = new System.Data.SqlClient.SqlCommandBuilder(adapter);
  
  dataset = new System.Data.DataSet();
  adapter.Fill(dataset,"Example");
  
  dataset.Tables["Example"].Rows[0].["Number"] = 10;
  adapter.update(dataset,"Example");
  </code></pre>
  
  <p>These work great. But now, for some reason I need to insert/update datetimes and use the <code>CONVERT</code> function on it.</p>
  
  <p>The single SQL query works great:</p>
  
  <pre><code>t = System.DateTime.Now;
  commandText = "INSERT INTO Test_Table (DateTime) VALUES (datetime, 't.toString()', 103)";
  command = new System.Data.SqlClient.SqlCommand(commandText, connection);
  command.ExecuteNonQuery();
  </code></pre>
  
  <p>This works without a problem, however I have no idea how to achieve the same thing using my <code>SqlCommandBuilder</code> scripts. I could change everything to single query, but this would take a week.</p>
  
  <p>I have already tried the following, without success:</p>
  
  <pre><code>t = System.DateTime.Now;
  adapter = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM Test_Table WHERE Number = '9'", connection);
  
  commandbuilder = new System.Data.SqlClient.SqlCommandBuilder(adapter);
  
  dataset = new System.Data.DataSet();
  adapter.Fill(dataset, "Example");
  
  dataset.Tables["Example"].Rows[0].["DateTime"] = "CONVERT(datetime,'" + t.toString() + "',103);
  adapter.update(dataset, "Example");
  </code></pre>