Cannot insert value via SqlDataAdapter

Question

I am learning how to work with SQL in C#, and I got in troubles with using SqlDataAdapter. I have tried to use direct queries via SqlCommand class and everything works fine, but when I rewrote my code to use SqlDataAdapter I have no changes in my table. There is my code:

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ADO"]
                                                                 .ConnectionString);

connection.Open();

SqlDataAdapter daUser = new SqlDataAdapter("SELECT * FROM Books", connection);

SqlCommand insert = new SqlCommand();
insert.Connection = connection;
insert.CommandText = "INSERT INTO Books (name, author) VALUES (@name, @author);";

SqlParameterCollection pc = insert.Parameters;
pc.Add("@name", SqlDbType.VarChar, 20, "test123");
pc.Add("@author", SqlDbType.VarChar, 20, "test322");
daUser.InsertCommand = insert;

DataSet ds = new DataSet();
daUser.Fill(ds, "Books");
daUser.Update(ds, "Books");

Table Books was created with this SQL query in SQL Server Management Studio:

CREATE TABLE Books 
(
    id int PRIMARY KEY IDENTITY(1,1),
    name varchar(MAX) NOT NULL,
    author varchar(MAX) NOT NULL
)

INSERT INTO Books(name, author) 
VALUES('1984', 'George Orwell'), ('Fathers and sons', 'Dostoevski')

Looks like I am missing something to do, that why my code have no effect on table.

If you want to use an SqlDataAdapter in this context (not the correct way to do it) then you need to add your data to the table returned by the Fill method. SqlDataAdapter.Update method looks at the data changed in that table to decide what to update/insert or delete. You don't have any change there so no INSERT is called (of course you don't need the parameters) — Steve
– Steve, Commented Jan 11, 2016 at 14:23

Andrey Korneyev · Accepted Answer · 2016-01-11 14:36:51Z

2

SqlDataAdapter.Update will call its InsertCommand only for the rows of datatable having RowState = DataRowState.Added.

This rowstate is automatically assigned to the datarows being added to rows collection using DataTable.Add method (until next call to AcceptChanges method). Also you can use DataRow.SetAdded method to force this state assignment.

Since you're not modifying/adding anything in you datatable after you've populated it with select command, it has nothing to insert.

Change your code to something like

daUser.Fill(ds, "Books");

var newBook = daUser.Tables[0].NewRow();
newBook["name"] = "New Book";
newBook["author"] = "Author Name";
daUser.Tables[0].Rows.Add(newBook);

daUser.Update(ds, "Books");

and in this case it should be new row added to the database table.

See MSDN for reference.

edited Jan 11, 2016 at 14:36

answered Jan 11, 2016 at 14:25

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joseph Morgan · Accepted Answer · 2016-01-11 14:25:34Z

-1

Just to clarify the previous answer, which is correct, you want to call ExecuteNonQuery() on the command not the dataAdapter.

SqlCommand insert = new SqlCommand(); 
insert.Connection = connection;
insert.CommandText = "INSERT INTO Books (name, author) VALUES (@name,
@author);";
SqlParameterCollection pc = insert.Parameters;
pc.Add("@name", SqlDbType.VarChar, 20, "test123"); 
pc.Add("@author",
SqlDbType.VarChar, 20, "test322"); 
// you do not need this line if you execute the insert on the command object.
// daUser.InsertCommand = insert;

//Add this line instead:
insert.ExecuteNonQuery();

Joey

answered Jan 11, 2016 at 14:25

Joseph Morgan

1711 silver badge10 bronze badges

Collectives™ on Stack Overflow

Cannot insert value via SqlDataAdapter

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related