I came across one thing/problem regarding typed datasets in the case where
the column name has two words or has a dash in between the words in the
column name like:

"E-mail" or "Expiry date"

In the select statment I use [E-mail] and [Expiry date] which works fine,
but the problem is that the

OleDbCommandBuilder(adapter)

does not build correct update statements and I get an error ("Syntax error
in UPDATE statement") when trying to update a row with "E-mail" or "Expiry
date"

Other colums with a single word column name works just fine.

Any hints would be very much appreciated.

Thanks..

Please see code sample below (not complete with all details, just to get an
idea):


string SqlString = "SELECT CustomerID, Firstname, [E-mail], [Expiry date]
FROM Customers";

// Create new dataset
CustomerDataset customerDataset = new CustomerDataset();

// Get customers
db.Execute(customerDataset, "Customers");

// Select row 1 (could be any other row)
CustomerDataset.CustomerRow row = (CustomerDataset.CustomerRow)

customerDataset.Customer.Rows[1];
// Change some fields
row.Firstname = "ZZ Top";
row._E_mail = (E-Mail Removed);

// Then update data base with the changes
connection.ConnectionString = ConnectionString;
command.CommandText = SqlString;

// Auto generate update statements
OleDbCommandBuilder up = new OleDbCommandBuilder(adapter);

// UPDATE FAILS WITH "Syntax error in UPDATE statement"
adapter.Update(ds, tableName);

NOTE: everything works just fine if the column names are one word like
"Email", "Expirydate" etc.

Hi,

"AnHund" <(E-Mail Removed)> wrote in message
news:02541C39-C3EC-4982-99FC-(E-Mail Removed)...
>I came across one thing/problem regarding typed datasets in the case where
> the column name has two words or has a dash in between the words in the
> column name like:
>
> "E-mail" or "Expiry date"
>
> In the select statment I use [E-mail] and [Expiry date] which works fine,
> but the problem is that the
>
> OleDbCommandBuilder(adapter)
>
> does not build correct update statements and I get an error ("Syntax error
> in UPDATE statement") when trying to update a row with "E-mail" or "Expiry
> date"
>
> Other colums with a single word column name works just fine.
>
> Any hints would be very much appreciated.
>
> Thanks..
>
> Please see code sample below (not complete with all details, just to get
> an
> idea):
>
>
> string SqlString = "SELECT CustomerID, Firstname, [E-mail], [Expiry date]
> FROM Customers";
>
> // Create new dataset
> CustomerDataset customerDataset = new CustomerDataset();
>
> // Get customers
> db.Execute(customerDataset, "Customers");
>
> // Select row 1 (could be any other row)
> CustomerDataset.CustomerRow row = (CustomerDataset.CustomerRow)
>
> customerDataset.Customer.Rows[1];
> // Change some fields
> row.Firstname = "ZZ Top";
> row._E_mail = (E-Mail Removed);
>
> // Then update data base with the changes
> connection.ConnectionString = ConnectionString;
> command.CommandText = SqlString;
>
> // Auto generate update statements
> OleDbCommandBuilder up = new OleDbCommandBuilder(adapter);

Try to set:
up.QuotePrefix = "[";
up.QuoteSuffix = "]";

before updating.

HTH,
Greetings


>
> // UPDATE FAILS WITH "Syntax error in UPDATE statement"
> adapter.Update(ds, tableName);
>
> NOTE: everything works just fine if the column names are one word like
> "Email", "Expirydate" etc.

AnHund wrote:
> I came across one thing/problem regarding typed datasets in the case where
> the column name has two words or has a dash in between the words in the
> column name like:
>
> "E-mail" or "Expiry date"
>
> In the select statment I use [E-mail] and [Expiry date] which works fine,
> but the problem is that the
>
> OleDbCommandBuilder(adapter)
>
> does not build correct update statements and I get an error ("Syntax error
> in UPDATE statement") when trying to update a row with "E-mail" or "Expiry
> date"
If you like the idea of having the SQL automatically generated for you
maybe you should take a look at http://www.nhibernate.org



>
> Other colums with a single word column name works just fine.
>
> Any hints would be very much appreciated.
>
> Thanks..
>
> Please see code sample below (not complete with all details, just to get an
> idea):
>
>
> string SqlString = "SELECT CustomerID, Firstname, [E-mail], [Expiry date]
> FROM Customers";
>
> // Create new dataset
> CustomerDataset customerDataset = new CustomerDataset();
>
> // Get customers
> db.Execute(customerDataset, "Customers");
>
> // Select row 1 (could be any other row)
> CustomerDataset.CustomerRow row = (CustomerDataset.CustomerRow)
>
> customerDataset.Customer.Rows[1];
> // Change some fields
> row.Firstname = "ZZ Top";
> row._E_mail = (E-Mail Removed);
>
> // Then update data base with the changes
> connection.ConnectionString = ConnectionString;
> command.CommandText = SqlString;
>
> // Auto generate update statements
> OleDbCommandBuilder up = new OleDbCommandBuilder(adapter);
>
> // UPDATE FAILS WITH "Syntax error in UPDATE statement"
> adapter.Update(ds, tableName);
>
> NOTE: everything works just fine if the column names are one word like
> "Email", "Expirydate" etc.

Thanks Bart,

Your solution to the problem just works perfectly.

Br. AnHund

"Bart Mermuys" wrote:

> Hi,
>
> "AnHund" <(E-Mail Removed)> wrote in message
> news:02541C39-C3EC-4982-99FC-(E-Mail Removed)...
> >I came across one thing/problem regarding typed datasets in the case where
> > the column name has two words or has a dash in between the words in the
> > column name like:
> >
> > "E-mail" or "Expiry date"
> >
> > In the select statment I use [E-mail] and [Expiry date] which works fine,
> > but the problem is that the
> >
> > OleDbCommandBuilder(adapter)
> >
> > does not build correct update statements and I get an error ("Syntax error
> > in UPDATE statement") when trying to update a row with "E-mail" or "Expiry
> > date"
> >
> > Other colums with a single word column name works just fine.
> >
> > Any hints would be very much appreciated.
> >
> > Thanks..
> >
> > Please see code sample below (not complete with all details, just to get
> > an
> > idea):
> >
> >
> > string SqlString = "SELECT CustomerID, Firstname, [E-mail], [Expiry date]
> > FROM Customers";
> >
> > // Create new dataset
> > CustomerDataset customerDataset = new CustomerDataset();
> >
> > // Get customers
> > db.Execute(customerDataset, "Customers");
> >
> > // Select row 1 (could be any other row)
> > CustomerDataset.CustomerRow row = (CustomerDataset.CustomerRow)
> >
> > customerDataset.Customer.Rows[1];
> > // Change some fields
> > row.Firstname = "ZZ Top";
> > row._E_mail = (E-Mail Removed);
> >
> > // Then update data base with the changes
> > connection.ConnectionString = ConnectionString;
> > command.CommandText = SqlString;
> >
> > // Auto generate update statements
> > OleDbCommandBuilder up = new OleDbCommandBuilder(adapter);
>
> Try to set:
> up.QuotePrefix = "[";
> up.QuoteSuffix = "]";
>
> before updating.
>
> HTH,
> Greetings
>
>
> >
> > // UPDATE FAILS WITH "Syntax error in UPDATE statement"
> > adapter.Update(ds, tableName);
> >
> > NOTE: everything works just fine if the column names are one word like
> > "Email", "Expirydate" etc.
>
>
>