SQL Data Adapter Insert Command

Question

There seems to be a problem with the following code I would like to insert a value in the database using the following code snippet.

DataTable dtUsers = new DataTable("tblUsers");
BindingSource bsUsers = new BindingSource();
SqlDataAdapter daUsers = new SqlDataAdapter("usp_GetUsers", Properties.Resources.ConnectionString);


     daUsers.InsertCommand = new SqlCommand("usp_InsertNewUser");
                daUsers.InsertCommand.Connection = new SqlConnection(Properties.Resources.ConnectionString);
                daUsers.InsertCommand.CommandType = CommandType.StoredProcedure;
                daUsers.InsertCommand.Parameters.Clear();
                daUsers.InsertCommand.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = txtUser.Text;
                daUsers.InsertCommand.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = txtPass.Text;
                daUsers.InsertCommand.Parameters.Add("@userType", SqlDbType.Int).Value = cbxUserType.SelectedValue;
                daUsers.Update(dtUsers);

And before you ask, the stored procedure is working fine. And also if I change the InsertCommand above to SelectCommand and call the Fill method, then everything works fine! This thing is frustrating as the Insert/update methods of the data adapter is not working as it should if you are using Select/Fill combo. No data rows are being inserted as I monitored that as well. Also no exceptions are generated. Al

score 2 · Answer 1 · answered Mar 30 '11 at 15:44

With that exact code snippet, nothing will be inserted - the way SqlDataAdapter.Update() works is for every DataRow in the supplied datatable (dtUsers) that has a RowState of Added, it will call the InsertCommand that has been defined on the data adapter. Likewise, for every DataRow with a RowState of Modified, it will call the UpdateCommand that has been defined (if there is one).

The purpose of SqlDataAdapter.Update(), is to be supplied with a DataTable (potentially containing new, updated and deleted rows) which it will call the appropriate defined sproc for.

So in your code, you have nothing in dtUsers with a RowState of Added and so there is no work to send to the DB....hence nothing gets inserted.

score -1 · Answer 2 · edited May 22 '16 at 17:57

You may use adapter.InsertCommand.ExecuteNonQuery() after setting the params to commit inserting in database.

For example:

DataTable dtUsers = new DataTable("tblUsers");
BindingSource bsUsers = new BindingSource();
SqlDataAdapter daUsers = new SqlDataAdapter("usp_GetUsers",Properties.Resources.ConnectionString);
daUsers.InsertCommand = new SqlCommand("usp_InsertNewUser");
daUsers.InsertCommand.Connection = new SqlConnection(Properties.Resources.ConnectionString);
daUsers.InsertCommand.CommandType = CommandType.StoredProcedure;
daUsers.InsertCommand.Parameters.Clear();
daUsers.InsertCommand.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = txtUser.Text;
daUsers.InsertCommand.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = txtPass.Text;
daUsers.InsertCommand.Parameters.Add("@userType", SqlDbType.Int).Value = cbxUserType.SelectedValue;
daUsers.InsertCommand.ExecuteNonQuery();
daUsers.Update(dtUsers);

SQL Data Adapter Insert Command

2 Answers2