Why does DataAdapter.Update update nothing?

Question

0.00/5 (No votes)

See more:

Hi!

In the code below I add a row into my DataTable and try to update the database. In result, the element DataTable has the added row, but the table in the database doesn't change.

Would you be so kind to help me with fixing the problem.

C#

string connstr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|db.mdf;Integrated Security=True";
string command = "SELECT * FROM myTable";
var adapter = new SqlDataAdapter(command, connstr);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
var ds = new DataSet();
try
{
    adapter.Fill(ds);
    DataTable tab = ds.Tables[0];

    var r = tab.NewRow();
    r["name"] = "TestName";
    r["length"] = 1;
    r["Comment"] = "SomeComment";
    tab.Rows.Add(r);              

    adapter.Update(tab);
}
catch (SqlException ex)
{
    Console.WriteLine(ex.Message);
}

Console.Read();

Instead of adapter.Update(tab) I also tried adapter.Update(ds), but there was nothing new.

Here is my Table.
<img src="http://i.imgur.com/VZ7HYK6.png">

Posted 2-Nov-14 0:15am

brombenzol123

Updated 2-Nov-14 1:03am

v5

Add a Solution

3 solutions

Solution 1

Update command can update your data only if you
1. have an update command
2. have some primary key on your table - in which case update command generated automatically
Check these issues...

Posted 2-Nov-14 0:54am

Kornfeld Eliyahu Peter

Comments

Kornfeld Eliyahu Peter 2-Nov-14 7:26am

That wasn't clear from your post...
What about the id column? How it gets its values?

Solution 3

There is all right with it.
INSERT, UPDATE and DELETE commands are provided by SqlCommandBuilder in my code. To check ones' correctness, I called builder's properties:

C#

builder.GetInsertCommand().CommandText;
builder.GetUpdateCommand().CommandText;
builder.GetDeleteCommand().CommandText;

There were values

SQL

INSERT INTO [myTable] ([name], [length], [comment]) VALUES (@p1, @p2, @p3)

SQL

UPDATE [myTable] SET [name] = @p1, [length] = @p2, [comment] = @p3 WHERE (([Id] = @p4) AND ([name] = @p5) AND ([length] = @p6) AND ((@p7 = 1 AND [comment] IS NULL) OR ([comment] = @p8)))

SQL

DELETE FROM [myTable] WHERE (([Id] = @p1) AND ([name] = @p2) AND ([length] = @p3) AND ((@p4 = 1 AND [comment] IS NULL) OR ([comment] = @p5)))

As fas as I understand, in my case the query INSERT takes place. In my oppinion, there is all right with it.

As for primary key, there is one in my table.

Posted 2-Nov-14 1:21am

brombenzol123

Updated 2-Nov-14 1:24am

v2

Comments

Kornfeld Eliyahu Peter 2-Nov-14 7:27am

Please do not post comments as answers!

brombenzol123 2-Nov-14 8:49am

I just wanted my code to be lighted.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

brombenzol123 · Accepted Answer · 2014-11-02T02:49:00

The solution was (by Steve from StackOverflow forum):

This is a pretty common scenario. You have your database file listed between your project items and it has the property Copy To output Directory set to Copy Always.
This means that every time you start a debug session, the Visual Studio copy your MDF file from its project location to the BIN\DEBUG folder overwriting the file present there.
Of course this copy doesn't have the newest record added in the previous run.

So your code is working correctly but the configuration of your project wreak havoc with your tests

Simply change your property to Do Not Copy or Copy if Newer