OdbcDataAdapter Update not working!

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Can anyone tell me why this code won't update the datasource? It
always comes back with this DBConcurrency Exception:

"Concurrency violation: the UpdateCommand affected 0 of the expected 1
records."

The database is Oracle. Here's the table creation SQL for the table
I'm querying:

CREATE TABLE TEST
(
ID INT,
email CHAR(1),
NAME CHAR(1),
CONSTRAINT PK_test PRIMARY KEY (ID )
)

Here's the code in its entirety:

using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data;
using System.Data.Odbc;

namespace DatabaseTester {
static class Program {
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main() {

DataSet data = new DataSet();

OdbcConnection conn = new OdbcConnection(
"DSN=ORACLE-DATABASE;UID=user;PWD=password!;" );

OdbcDataAdapter da = new OdbcDataAdapter( "Select id, email, name
from test", conn );
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//DataTable[] t = da.FillSchema( data, SchemaType.Source );

da.Fill( data );
data.AcceptChanges();

OdbcCommandBuilder cb = new OdbcCommandBuilder( da );
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();

MessageBox.Show( "Before: " + data.Tables[0].Rows[0][1].ToString()
);

data.Tables[0].Rows[0][1] = "a";

MessageBox.Show( "After: " + data.Tables[0].Rows[0][1].ToString() );

if( data.HasChanges() ) {
MessageBox.Show( "It has changes." );

try {
MessageBox.Show( "Returns: " + da.Update( data ).ToString() );
data.AcceptChanges();
}
catch( DBConcurrencyException e ) {
MessageBox.Show( e.ToString() );
}

}

conn.Close();
}
}
}
 
Ah, I'm no Oracle expert, but if you tell ADO.NET to "accept the changes"
you're not going to post any changes to the database.
(And there is no need to assign the Da.UpdateCommand etc. from the
Commandbuilder--it's done automatically.)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
My apologies because I won't be in front of my code until Monday, but I
have to set the commands because it -isn't- done automatically. If I
try to call .Update without setting these commands, it throws an error
(I'll triple check on Monday, but I'm 99% sure). As far as accepting
changes, I only do this after initially loading the data - you'll
notice I don't do this after changing the data. I can take the command
out and it doesn't make a difference.

Thanks for the suggestions, any other ideas? I've been struggling with
this for 2 days!
 
Alright, I'm now sitting in front of my code and indeed, if I take the
command builder portion out, I get an exception:

Update requires a valid UpdateCommand when passed DataRow collection
with modified rows.

Anyone have any ideas? I'm really struggling as to why this is not
working.
 
More info; I entered an update command manually, and the update worked
fine, so it would seem what's at issue here is the command builder's
update command generation. It generates a command fine, but I don't
believe it's filling in the parameters correctly; ie, this is what it
generates for this table:

UPDATE test SET ID = ?, EMAIL = ?, NAME = ? WHERE ((ID = ?) AND ((? = 1
AND EMAIL IS NULL) OR (EMAIL = ?)) AND ((? = 1 AND NAME IS NULL) OR
(NAME = ?)))

When I enter this manually and fill in the ?'s with the correct
information, it works. When I leave it in its generated form, it
doesn't. So my new question is - why is the command builder not
filling in the parameters correcty?

Any other ideas/ways to do this also welcome!
 
Woohoo! It has been solved!

What was happening was I was using the oracle ODBC driver and
apparently it was really outdated. Updating the driver solved all
issues! Hope this helps someone :)
 
Back
Top