SQL Table not updating...

[Neil Toulouse]

Hi guys

C# noob here. I have been following tutorials and googling this issue to death, and looking at similar issues on this forum, but I just can't get it to work!

Basically I have two forms. The first form has a datagrid which displays the results of a query from a single table SQL server database, and an edit button that passes the key value of the selected record to the second form for editing. This works fine.

The second form has two text boxes and a save button. The textboxes are bound and filled by the query on this form (code shown below), allowing edits to be made and saved. The problem is, the 'save' method does not update the SQL table. When investigating this further the _da.Update(_dt); reports that 0 rows have been updated.

I know this should be very straightforward, but I cannot see where I am going wrong

Any advice is much appreciated!

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace DataAdapter
{
    public partial class FrmEdit : Form
    {
        private readonly int _usersId;
        private SqlConnection _gnConn;
        private SqlDataAdapter _da;
        private DataTable _dt;
        private SqlCommandBuilder _cb;

        public FrmEdit(int usersId)
        {
            // Assign argument to form property.
            _usersId = usersId;

            InitializeComponent();
            QueryDatabase();
        }

        private void QueryDatabase()
        {
            // Create connection string (masked out, but working).
            _gnConn = new SqlConnection("Server=***");

            // Create a data adapter.
            _da = new SqlDataAdapter("SELECT ID, Firstname, Surname FROM Person WHERE ID = " + _usersId.ToString(), _gnConn);

            // Create a SQLCommandBuilder object
            _cb = new SqlCommandBuilder(_da);

            // Create a data table.
            _dt = new DataTable();

            // Fill the data table with the result from the dataAdapter query.
            _da.Fill(_dt);

            // Bind the fields of the DataTable to the 'Text' property of the text boxes on the form.
            txtFirstname.DataBindings.Add("Text", _dt, "Firstname");
            txtSurname.DataBindings.Add("Text", _dt, "Surname");
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            // Create an UPDATE command for the adpater.
            var daUpdateCmd =
                 new SqlCommand("UPDATE Person SET Firstname = @pFirstname, Surname = @pSurname WHERE ID = @pUserID",
                     _gnConn);
            daUpdateCmd.Parameters.AddWithValue("@pFirstName", _dt.Rows[0].ItemArray[0]);
            daUpdateCmd.Parameters.AddWithValue("@pSurname", _dt.Rows[0].ItemArray[1]);
            daUpdateCmd.Parameters.AddWithValue("@puserID", _usersId);

            // Update SQL database.
            _da.UpdateCommand = daUpdateCmd;
            _da.Update(_dt);
            _dt.AcceptChanges();

            // Release the connection and close the form.
            _gnConn.Dispose();
            Close();
        }
    }
}

I like work. It fascinates me. I can sit and look at it for hours...

 

[ralphtrent]

I would start out by modifying the SQL query to hard code values to see if you have an issue with your sql and parameters.

Hard code some values in your code and see if the record saves.

 

[Neil Toulouse]

Hi Ralph

I tried everything but no avail. I gave up in the end and went down the 'SQL Passthrough' route, rather than trying to get the update method working on the adapter:

private void btnSave_Click(object sender, EventArgs e)
        {
            // Create an UPDATE command for the adpater.
            var daUpdateCmd =
                 new SqlCommand("UPDATE Person SET Firstname = @pFirstname, Surname = @pSurname WHERE ID = @pUserID",
                     _gnConn);
            daUpdateCmd.Parameters.Add(new SqlParameter("@pFirstName", _dt.Rows[0].ItemArray[1]));
            daUpdateCmd.Parameters.Add(new SqlParameter("@pSurname",   _dt.Rows[0].ItemArray[2]));
            daUpdateCmd.Parameters.Add(new SqlParameter("@pUserID",    _usersId));

            // Update SQL database.
            _gnConn.Open();
            [b]daUpdateCmd.ExecuteNonQuery();[/b]
            _gnConn.Close();

            // Dispose of the connection.
            _gnConn.Dispose();

            // Close form.
            this.Close();
        }

I think my question really is "am I doing anything wrong or should the original code work?"

Thank you for your time.

I like work. It fascinates me. I can sit and look at it for hours...