SqlDataAdapter.Update or SqlCommandBuilder not working

Question

I have a form with a datagrid, which is populated wih data from a sqlserver database. The datagrid populates fine but I am having trouble posting back the changes made by the user, to the table in the sql database. My forms code is as follows:

public partial class frmTimesheet : Form
{
    private DataTable tableTS = new DataTable();
    private SqlDataAdapter adapter = new SqlDataAdapter();
    private int currentTSID = 0;

    public frmTimesheet()
    {
        InitializeComponent();
    }

    private void frmTimesheet_Load(object sender, EventArgs e)
    {
        string strUser = cUser.currentUser;            
        cMyDate cD = new cMyDate(DateTime.Now.ToString());
        DateTime date = cD.GetDate();
        txtDate.Text = date.ToString();
        cboTSUser.DataSource = cUser.GetListOfUsers("active");
        cboTSUser.DisplayMember = "UserID";
        cboTSUser.Text = strUser;
        CheckForTimeSheet();
        PopulateTimeSheet();
    }

    private void CheckForTimeSheet()
    {
        string strUser = cboTSUser.Text;
        cMyDate cD = new cMyDate(txtDate.Text);
        DateTime date = cD.GetDate();
        int newTSID = cTimesheet.TimeSheetExists(strUser, date);
        if (newTSID != this.currentTSID)
        {
            tableTS.Clear();
            if (newTSID == 0)
            {
                MessageBox.Show("Create TimeSheet");
            }
            else
            {
                this.currentTSID = newTSID;
            }
        }
    }

    private void PopulateTimeSheet()
    {
        try
        {
            string sqlText = "SELECT EntryID, CaseNo, ChargeCode, StartTime, FinishTime, Units " +
                             "FROM tblTimesheetEntries " +
                             "WHERE TSID = " + this.currentTSID + ";";
            SqlConnection linkToDB = new SqlConnection(cConnectionString.BuildConnectionString());
            SqlCommand sqlCom = new SqlCommand(sqlText, linkToDB);
            SqlDataAdapter adapter = new SqlDataAdapter(sqlCom);
            adapter.SelectCommand = sqlCom;
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            adapter.Fill(tableTS);
            dataTimesheet.DataSource = tableTS;                
        }
        catch (Exception eX)
        {
            string eM = "Error Populating Timesheet";
            cError err = new cError(eX, eM);
            MessageBox.Show(eM + Environment.NewLine + eX.Message);
        }         
    }

    private void txtDate_Leave(object sender, EventArgs e)
    {
        CheckForTimeSheet();
        PopulateTimeSheet();            
    }

    private void cboTSUser_DropDownClosed(object sender, EventArgs e)
    {
        CheckForTimeSheet(); 
        PopulateTimeSheet();
    }

    private void dataTimesheet_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {
        try
        {   
            adapter.Update(tableTS);
        }
        catch (Exception eX)
        {
            string eM = "Error on frmTimesheet, dataTimesheet_CellValueChanged";
            cError err = new cError(eX, eM);
            MessageBox.Show(eM + Environment.NewLine + eX.Message);
        }
    }
}

No exception occurs, and when I step through the issue seems to be with the SqlCommandBuilder which does NOT build the INSERT / UPDATE / DELETE commands based on my gien SELECT command.

Can anyone see what I am doing wrong?

What am I missing?

Kindly, can you review my answer. – M.Hassan Mar 19 '18 at 19:34 — M.Hassan, Mar 19 '18 at 19:34

score 3 · Answer 1 · answered Apr 14 '12 at 02:23

3

You need to set the UpdateCommand instead of SelectCommand on update:

 SqlDataAdapter adapter = new SqlDataAdapter();
 SqlCommandBuilder sqlBld = new SqlCommandBuilder(adapter)
 adapter.UpdateCommand = sqlBld.GetUpdateCommand() ;

answered Apr 14 '12 at 02:23

Raj Ranjhan

3,869
2
19
29

Kindly, can you review my answer? you can remove the 3rd line`adapter.UpdateCommand = sqlBld.GetUpdateCommand() ;` – M.Hassan Mar 19 '18 at 19:34

score 1 · Answer 2 · answered Mar 19 '18 at 19:33

1

The question is old, but the provided answer by@Anurag Ranjhan need to be corrected.

You need not to write the line:

   adapter.UpdateCommand = sqlBld.GetUpdateCommand() ;

and enough to write:

   SqlDataAdapter adapter = new SqlDataAdapter();
   SqlCommandBuilder sqlBld = new SqlCommandBuilder(adapter)
   //remove the next line
   //adapter.UpdateCommand = sqlBld.GetUpdateCommand() ;

The Sql update/insert/delete commands are auto generated based on this line

 SqlCommandBuilder sqlBld = new SqlCommandBuilder(adapter)

You can find the generated update sql by executing the line:

 sqlBld.GetUpdateCommand().CommandText;

See the example How To Update a SQL Server Database by Using the SqlDataAdapter Object in Visual C# .NET

The problem said by OP can be checked by reviewing the Sql Statement sent by the client in SQl Server Profiler.

answered Mar 19 '18 at 19:33

M.Hassan

10,282
5
65
84

Stumbled on this problem. I am working on a large project with thousands of lines of code and needed to fix a bug. In my code, `sqlBld` was created but never used (as suggested in your answer). I fixed a bug and then I refactored the code. I use ReSharper. As of now, it doesn't realize that `sqlBld` is necessary and suggest to delete the whole line! But this line is necessary, it has side effects on `SqlDataAdapter`. I originally deleted the line and then spent a few hours looking for the reason why I suddenly get an exception when the method is executed. – deralbert Jun 26 '23 at 12:24
See [How does SqlCommandBuilder do its stuff and how can I stop ReSharper's suggestion to delete it?](https://stackoverflow.com/questions/11180397/how-does-sqlcommandbuilder-do-its-stuff-and-how-can-i-stop-resharpers-suggestio) for more information. – deralbert Jun 26 '23 at 13:29

SqlDataAdapter.Update or SqlCommandBuilder not working

2 Answers2