Click here to Skip to main content
15,071,561 members
Please Sign up or sign in to vote.
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
Updated 2-Nov-14 1:03am
v5

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
   
Comments
[no name] 2-Nov-14 8:56am
   
Good to know, my 5.
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...
   
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?
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.
   
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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900