ODBCCommandBuilder produces different UpdateCommand with Oracle usingfield name aliases

  • Thread starter Thread starter Eric
  • Start date Start date
E

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.
 
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)
____________________________________________________________________________________________
 
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.
Click to expand...
Here is the code in question:
Click to expand...
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)
{
}
Click to expand...
When I run this for a SQL Server connection, the _da.Update(_dt);
command executes without an exception and
_cmdBldr.GetUpdateCommand().CommandText returns:
Click to expand...
"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 = ?)))"
Click to expand...
When I run this for an Oracle connection the _da.Update(_dt); command
generates an exception:
Click to expand...
"ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: "PG": invalid identifier"
Click to expand...
and _cmdBldr.GetUpdateCommand().CommandText returns:
Click to expand...
"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 = ?)))"
Click to expand...
Note that in SQL Server, the original field name "PLANGROUP" is used
in the UpdateCommand.  In Oracle, the field name alias "PG" is used.
Click to expand...
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?
Click to expand...
Thanks.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
Back
Top