Re: ODBCCommandBuilder produces different UpdateCommand with Oracle using field name aliases

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" <jeemail.no.spam@xxxxxxxxx> wrote in message news:0f80b150-67e9-486c-8f67-ed1fbe288621@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.

.

Relevant Pages