syntax problem

TheMSsForum.com: The Microsoft Software Forum

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
Top

Re: syntax problem by bill

bill
Wed Feb 06 11:26:46 CST 2008

On Feb 6, 12:06=A0pm, bill <wgr...@draper.com> wrote:
> All,
>
> I have a C# app that is accessing an Excel spreadsheet using the
> following:
>
> =A0 =A0 OleDbDataAdapter metricDataAdapter =3D new OleDbDataAdapter("SELEC=
T
> * FROM [ReqReviewsData]", =A0 =A0Connection);
>
> 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):
>
> =A0 =A0 =A0 dbCmd =3D Connection.CreateCommand();
> =A0 =A0 =A0 dbCmd.CommandText =3D "Update [ReqReviewsData] Set Space =3D \=
"xx\"
> WHERE MetricName =3D \"Verified\"";
> =A0 =A0 =A0 dbCmd.ExecuteNonQuery();
>
> This generates the following:
>
> =A0 =A0 =A0 Syntax error in UPDATE statement.
>
> The connection string looks like:
>
> =A0 =A0 =A0 string ConnectString =3D "Provider=3DMicrosoft.ACE.OLEDB.12.0;=
" +
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 "Data Source=3DD:\\LETB\\Documentation\\Metric=
s\
> \LETBMetrics.xlsm;" +
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 "Extended Properties=3D" + (char)34 + "Excel
> 12.0;HDR=3DYES;" + (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:
>
> =A0 =A0 =A0 dbCmd.CommandText =3D "Update [ReqReviewsData] Set Space =3D "=
+
> =A0 =A0 =A0 =A0 (char)34 + "xx" + (char)34 + " WHERE MetricName =3D " + (c=
har)34
> + "Verified" + (char)34;
>
> The produces the same error. What oh what is wrong with my UPDATE
> statement?
>
> TIA,
>
> Bill

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 =3D "Update [ReqReviewsData] Set [Space] =3D " +
(char)34 + "xx" + (char)34 + " WHERE [MetricName] =3D " +
(char)34 + "Verified" + (char)34;




Top

Re: syntax problem by Misbah

Misbah
Wed Feb 06 11:35:20 CST 2008

try
dbCmd.CommandText = "Update [ReqReviewsData] Set Space = '" + "xx" + "'
WHERE MetricName = '" + "Verified" + "'";

--
Misbah Arefin


"bill" <wgrigg@draper.com> wrote in message
news:a6ec2773-5d62-44be-a70d-cd177c7d29eb@d4g2000prg.googlegroups.com...
> 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
>
>
Top