Column names and typed dataset

[Guest]

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 address 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.

 

[Bart Mermuys]

Hi,

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 address 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

 

[luxspes]

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 address 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.

 

[Guest]

Thanks Bart,

Your solution to the problem just works perfectly.

Br. AnHund

Hi,

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 address 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.