INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Table not updating...

SQL Table not updating...

(OP)
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!

CODE

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...

RE: SQL Table not updating...

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.

RE: SQL Table not updating...

(OP)
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:

CODE

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();
            daUpdateCmd.ExecuteNonQuery();
            _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...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close