ODBCCommandBuilder produces different UpdateCommand with Oracle usingfield name aliases

[Eric]

If I use a command that contains field name aliases, I get a different
result for _cmdBldr.GetUpdateCommand().CommandText, depending on
whether I am connected to SQL Server 2005 or Oracle 10g.

Here is the code in question:

string _sqlString = "SELECT ID, PLANGROUP AS PG, DESC_R FROM TABLEA
WHERE ID = 100002";
OdbcConnection _dc = new OdbcConnection();
_dc.ConnectionString = "<connectionstring>";
_dc.Open();
OdbcDataAdapter _da = new OdbcDataAdapter(_sqlString, _dc);
DataTable _dt = new DataTable();
_da.Fill(_dt);
_dt.Rows[0].BeginEdit();
_dt.Rows[0]["DESC_R"] = "ABC";
_dt.Rows[0].EndEdit();
OdbcCommandBuilder _cmdBldr = new OdbcCommandBuilder(_da);
MessageBox.Show(_cmdBldr.GetUpdateCommand().CommandText);
try
{
_da.Update(_dt);
}
catch(Exception ex)
{
}



When I run this for a SQL Server connection, the _da.Update(_dt);
command executes without an exception and
_cmdBldr.GetUpdateCommand().CommandText returns:

"UPDATE TABLEA SET ID = ?, PLANGROUP = ?, DESC_R = ? WHERE ((ID = ?)
AND ((? = 1 AND PLANGROUP IS NULL) OR (PLANGROUP = ?)) AND ((? = 1 AND
DESC_R IS NULL) OR (DESC_R = ?)))"


When I run this for an Oracle connection the _da.Update(_dt); command
generates an exception:

"ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: "PG": invalid identifier"

and _cmdBldr.GetUpdateCommand().CommandText returns:

"UPDATE TABLEA SET ID = ?, PG = ?, DESC_R = ? WHERE ((ID = ?) AND ((?
= 1 AND PG IS NULL) OR (PG = ?)) AND ((? = 1 AND DESC_R IS NULL) OR
(DESC_R = ?)))"

Note that in SQL Server, the original field name "PLANGROUP" is used
in the UpdateCommand. In Oracle, the field name alias "PG" is used.

Is there a way to force the command builder to create an update
command with the orginal field name when using Oracle? Is there
another approach I should be using, other than always creating my own
update command from scratch for Oracle?

Thanks.

 

[William Vaughn]

Ah, and why would you expect it to create non-backend specific code?
Incidentally, have you read any of the (dozens of) threads on why NOT to use
the CommandBuilder? How about this article?
http://msdn2.microsoft.com/en-us/library/ms971491.aspx

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Eric]

Ah, and why would you expect it to create non-backend specific code?
Incidentally, have you read any of the (dozens of) threads on why NOT to use
the CommandBuilder? How about this article?http://msdn2.microsoft.com/en-us/library/ms971491.aspx

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)

If I use a command that contains field name aliases, I get a different
result for _cmdBldr.GetUpdateCommand().CommandText, depending on
whether I am connected to SQL Server 2005 or Oracle 10g.

Here is the code in question:

string _sqlString = "SELECT ID, PLANGROUP AS PG, DESC_R FROM TABLEA
WHERE ID = 100002";
OdbcConnection _dc = new OdbcConnection();
_dc.ConnectionString = "<connectionstring>";
_dc.Open();
OdbcDataAdapter _da = new OdbcDataAdapter(_sqlString, _dc);
DataTable _dt = new DataTable();
_da.Fill(_dt);
_dt.Rows[0].BeginEdit();
_dt.Rows[0]["DESC_R"] = "ABC";
_dt.Rows[0].EndEdit();
OdbcCommandBuilder _cmdBldr = new OdbcCommandBuilder(_da);
MessageBox.Show(_cmdBldr.GetUpdateCommand().CommandText);
try
{
     _da.Update(_dt);
}
catch(Exception ex)
{
}

When I run this for a SQL Server connection, the _da.Update(_dt);
command executes without an exception and
_cmdBldr.GetUpdateCommand().CommandText returns:

"UPDATE TABLEA SET ID = ?, PLANGROUP = ?, DESC_R = ? WHERE ((ID = ?)
AND ((? = 1 AND PLANGROUP IS NULL) OR (PLANGROUP = ?)) AND ((? = 1AND
DESC_R IS NULL) OR (DESC_R = ?)))"

When I run this for an Oracle connection the _da.Update(_dt); command
generates an exception:

"ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: "PG": invalid identifier"

and _cmdBldr.GetUpdateCommand().CommandText returns:

"UPDATE TABLEA SET ID = ?, PG = ?, DESC_R = ? WHERE ((ID = ?) AND ((?
= 1 AND PG IS NULL) OR (PG = ?)) AND ((? = 1 AND DESC_R IS NULL) OR
(DESC_R = ?)))"

Note that in SQL Server, the original field name "PLANGROUP" is used
in the UpdateCommand.  In Oracle, the field name alias "PG" is used.

Is there a way to force the command builder to create an update
command with the orginal field name when using Oracle?  Is there
another approach I should be using, other than always creating my own
update command from scratch for Oracle?

Thanks.- Hide quoted text -

- Show quoted text -

Hi William,

Not sure if you caught what I was referring to. Basically the update
command is incorrect in that it uses the alias names instead of the
original field names. Strangely this only happens when using an
ODBCCommandBuilder, not when using the OracleCommandBuilder. Thanks
for the suggestion about not using the command builder and the article
reference, I will check it out.

Eric