DataAdapter.Update() doesn't work

  • Thread starter Thread starter zRaze
  • Start date Start date
Z

zRaze

I have created a Dataset containing two tables "Table1" and "Table2"
If I make a change to a field in "Table1", I call the AcceptChanges()
on the DataTable. This changes my local copy of the table

However, when I run the Adapter's Update, it doesn't error but doesn't
make the changes in my database. Am I missing something, all the tutes
I've read on ADO.Net is all about downloading and playing with the
data, nothing about updating the server with the changes.

Cheers...
 
Hi,

You shouldn't call AcceptChanges() *before* update because it will mark rows
as unchanged.
And Update calls AcceptChanges by itself.
 
Calling AcceptChanges right before you call update is a Guarantee that you
will never have your updates submitted in the db unless something else does
it.

Remember that Update, provided there are changes to submit calls
AcceptChanges on a row by row basis as it iterates through the rows
collection and after the update is successfully happened (again, on a row
by row basis not on a datatable basis).

Here's an article I wrote that may clear things up for you
http://www.knowdotnet.com/articles/efficient_pt4.html

However, what does Table1 and table2 have to do here?

If I understand what you're doing, just cut out the AcceptChanges totally
and you should be good to go. If not, let me know and I'll see what I can
do.

Cheers,

Bill
www.devbuzz.com
 
Hello zRaze,

Calling UpdateChanges will move all row states to an unmodified state, hence, nothing will be flagged to update.

Remove the call to AcceptChanges, as it is done by the Update command.
 
Thanks for your help. I still can't get it to work. However, I did
find my original problem, my UpdateCommand didn't exist (I haven't
used any wizards). I've butchered the one off the doc you posted.

Basically this app brings back two tables, into a dataset, and
allowing to modify the data (this example uses a UI, but I intend for
it not to have one) and put it back on the server.

When I didn't have an UpdateCommand and removed the AcceptChanges
function, I errored with a "missing update command". However, that bit
is fixed and now I just get -
"An unhandled exception of type 'System.Data.OleDb.OleDbException'
occured in system.data.dll"

---Objects------------------------------------------------------
Dim dbo As New Data.OleDb.OleDbConnection()
Dim dsTables As New Data.DataSet()
Dim daAdaptorA As Data.OleDb.OleDbDataAdapter
Dim daAdaptorB As Data.OleDb.OleDbDataAdapter
Dim iRowIndex As Integer

---Init Code---------------------------------------------------
dbo.ConnectionString = "<Access Database>"
dbo.Open()

daAdaptorA = New OleDb.OleDbDataAdapter("SELECT * FROM
Table1;", dbo)
daAdaptorA.UpdateCommand = New Data.OleDb.OleDbCommand("UPDATE
Table1 SET Test1 = @Test1, Test2 = @Test2;", dbo)
daAdaptorA.Fill(dsTables, "Table1")

daAdaptorB = New OleDb.OleDbDataAdapter("SELECT * FROM
Table2;", dbo)
daAdaptorB.Fill(dsTables, "Table2")


---Update Code-------------------------------------------
dsTables.Tables("Table1").Rows(iRowIndex).Item("Test1") =
TextBox1.Text
daAdaptorA.Update(dsTables.Tables("Table1"))
 
Yes, the reason that is is that Update never really hit the db b/c of hte
acceptchanges.

There's a lot wrong with your update code. No parameters have been added to
the parameters colleciton. That's the first problem. Basically, you add
them like
command.Parameters.Add()//there are a few different overloads that you can
look at.

Next you'll need to specify the columnmapping in the parameter declaration.

I'm going to include a sample, but it's sqlClient (very similar) but your
best bet is drag a OleDbDDataAdapter on your form (or in another project),
set your connection, add the table you want to query and let it generate the
logic for you . that will give you a really good idea of what's going on
(for many reasons I recommend this) Here's a sample, but you definitely
want to run through the wizard at least once and see (perhaps even copy and
paste the code directly once you generate it)

this.sqlUpdateCommand1.CommandText = @"UPDATE Employees SET EmplNum =
@EmplNum, LastName = @LastName, FirstName = @FirstName, BirthDate =
@BirthDate WHERE (EmplNum = @Original_EmplNum) AND (BirthDate =
@Original_BirthDate OR @Original_BirthDate IS NULL AND BirthDate IS NULL)
AND (FirstName = @Original_FirstName OR @Original_FirstName IS NULL AND
FirstName IS NULL) AND (LastName = @Original_LastName OR @Original_LastName
IS NULL AND LastName IS NULL); SELECT EmplNum, LastName, FirstName,
BirthDate FROM Employees WHERE (EmplNum = @EmplNum)";

this.sqlUpdateCommand1.Connection = this.sqlConnection2;

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@EmplNum", System.Data.SqlDbType.Int, 4,
"EmplNum"));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@LastName",
System.Data.SqlDbType.NVarChar, 20, "LastName"));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@FirstName",
System.Data.SqlDbType.NVarChar, 20, "FirstName"));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@BirthDate",
System.Data.SqlDbType.DateTime, 4, "BirthDate"));
 
Thanks. I added parameters, but when I it did an Update, it overwrote
all my values, I realised I hadn't put a WHERE clause in. However, I
added one for the primary key. but it now errors if I have
"WHERE ID = @ID" or "WHERE ID = @Original_ID" in the Update query
It tells me Concurrency error and it updates 0 rows...

Ideas?
 
Positive, it's an auto-number, and I didn't have any reference to
change it. Also, in the Parameter I added, I set the SourceVersion to
"Original" (Like I saw when I made one through the wizards)
 
Can you show me the update command? It's critical b/c it's what determines
the concurrency exception.
 
Here you go

daAdaptorA = New OleDb.OleDbDataAdapter("SELECT * FROM Table1;", dbo
daAdaptorA.UpdateCommand = New Data.OleDb.OleDbCommand("UPDATE Table1 SET Test1 = @Test1, Test2 = @Test2 WHERE ID = @ID;", dbo
daAdaptorA.UpdateCommand.Parameters.Add("@ID", Data.OleDb.OleDbType.Integer, 4, "ID").SourceVersion = DataRowVersion.Origina
daAdaptorA.UpdateCommand.Parameters.Add("@Test1", Data.OleDb.OleDbType.VarChar, 50, "Test1"
daAdaptorA.UpdateCommand.Parameters.Add("@Test2", Data.OleDb.OleDbType.Integer, 4, "Test2"

----- William Ryan eMVP wrote: ----

Can you show me the update command? It's critical b/c it's what determine
the concurrency exception
 
I've got it working... I tried adding the parameters in the order they
appear in the CommandText String, and it now works.
I can't believe that was screwing it up. What's the point in giving
parameters names if they have to be entered in a specific order!?!?!?!

Anyway, thanks for your help...

zRaze said:
Here you go:

daAdaptorA = New OleDb.OleDbDataAdapter("SELECT * FROM Table1;", dbo)
daAdaptorA.UpdateCommand = New Data.OleDb.OleDbCommand("UPDATE
Click to expand...
Table1 SET Test1 = @test1, Test2 = @Test2 WHERE ID = @ID;", dbo)
daAdaptorA.UpdateCommand.Parameters.Add("@ID",
Click to expand...
Data.OleDb.OleDbType.Integer, 4, "ID").SourceVersion =
DataRowVersion.Original
daAdaptorA.UpdateCommand.Parameters.Add("@Test1",
Click to expand...
Data.OleDb.OleDbType.VarChar, 50, "Test1")
daAdaptorA.UpdateCommand.Parameters.Add("@Test2",
Click to expand...
Data.OleDb.OleDbType.Integer, 4, "Test2")
----- William Ryan eMVP wrote: -----

Can you show me the update command? It's critical b/c it's what determines
the concurrency exception.
Click to expand...
 
Back
Top