Column names and typed dataset

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Hi,

AnHund said:
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);
Click to expand...

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

before updating.

HTH,
Greetings
 
AnHund said:
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"
Click to expand...
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.
Click to expand...
 
Thanks Bart,

Your solution to the problem just works perfectly.

Br. AnHund

Bart Mermuys said:
Hi,

AnHund said:
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);
Click to expand...

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.
Click to expand...
Click to expand...
 
Back
Top