syntax problem

  • Thread starter Thread starter bill
  • Start date Start date
B

bill

All,

I have a C# app that is accessing an Excel spreadsheet using the
following:

OleDbDataAdapter metricDataAdapter = new OleDbDataAdapter("SELECT
* FROM [ReqReviewsData]", Connection);

This works, and I can read the ReqReviewsData range into my dataset.
Now I am trying to write the dataset (or anything for that matter)
back to the Excel spreadsheet. I do the following (taken from an
example supplied by David Hayden):


dbCmd = Connection.CreateCommand();
dbCmd.CommandText = "Update [ReqReviewsData] Set Space = \"xx\"
WHERE MetricName = \"Verified\"";
dbCmd.ExecuteNonQuery();

This generates the following:

Syntax error in UPDATE statement.

The connection string looks like:

string ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=D:\\LETB\\Documentation\\Metrics\
\LETBMetrics.xlsm;" +
"Extended Properties=" + (char)34 + "Excel
12.0;HDR=YES;" + (char)34;

And yes indeed the Excel range that I am selecting has a column with
"Space" and "MetricName" which really should not have anything to do
with syntax anyway. I have diddled with dbCmd.CommandText in several
ways, one of which is the following:

dbCmd.CommandText = "Update [ReqReviewsData] Set Space = " +
(char)34 + "xx" + (char)34 + " WHERE MetricName = " + (char)34
+ "Verified" + (char)34;

The produces the same error. What oh what is wrong with my UPDATE
statement?

TIA,

Bill
 
All,

I have a C# app that is accessing an Excel spreadsheet using the
following:

    OleDbDataAdapter metricDataAdapter = new OleDbDataAdapter("SELECT
* FROM [ReqReviewsData]",    Connection);

This works, and I can read the ReqReviewsData range into my dataset.
Now I am trying to write the dataset (or anything for that matter)
back to the Excel spreadsheet. I do the following (taken from an
example supplied by David Hayden):

      dbCmd = Connection.CreateCommand();
      dbCmd.CommandText = "Update [ReqReviewsData] Set Space = \"xx\"
WHERE MetricName = \"Verified\"";
      dbCmd.ExecuteNonQuery();

This generates the following:

      Syntax error in UPDATE statement.

The connection string looks like:

      string ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
              "Data Source=D:\\LETB\\Documentation\\Metrics\
\LETBMetrics.xlsm;" +
              "Extended Properties=" + (char)34 + "Excel
12.0;HDR=YES;" + (char)34;

And yes indeed the Excel range that I am selecting has a column with
"Space" and "MetricName" which really should not have anything to do
with syntax anyway. I have diddled with dbCmd.CommandText in several
ways, one of which is the following:

      dbCmd.CommandText = "Update [ReqReviewsData] Set Space = "+
        (char)34 + "xx" + (char)34 + " WHERE MetricName = " + (char)34
+ "Verified" + (char)34;

The produces the same error. What oh what is wrong with my UPDATE
statement?

TIA,

Bill
Click to expand...

Oh never mind... After checking some other posts I discovered that if
I substituted [Space] and [MetricName] it worked just fine (see below)

dbCmd.CommandText = "Update [ReqReviewsData] Set [Space] = " +
(char)34 + "xx" + (char)34 + " WHERE [MetricName] = " +
(char)34 + "Verified" + (char)34;
 
try
dbCmd.CommandText = "Update [ReqReviewsData] Set Space = '" + "xx" + "'
WHERE MetricName = '" + "Verified" + "'";
 
Back
Top