[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DataGridView with SortKey to move up or down (again)

Posted on 2009-02-10
2
Medium Priority
?
371 Views
Last Modified: 2013-12-17
Hi,

I'm creating a DataGridView with move-record-up and move-record-down buttons.  I've seen a lot written on this, but none of the solutions include a bound DataGridView.  My problem is that I'm changing the DataGridView SortKey and only about 50% of the time does the data make it back to the database.  The code I'm using is listed below.  

If, in the same method, I write text to a textBox which is bound to the bindingSource, then the method works every time (indicating that the bound field is somehow forcing all the changed data back to the dataSource).  

If someone is going to suggest doing the entire method at the dataSource level instead of in the dataGridView, I would appreciate some code snippet as I can't seem to figure out if you make changes to the DataTable where to force the resort and how to manage the current position.

TIA
private void buttonPhotoMoveUp_Click( object sender, EventArgs e )
      {
         int currentRowIndex = _dataGridViewPhoto.CurrentCell.RowIndex;
 
         if ( currentRowIndex > 0 )
         {
            int currentRowSortKey = (int)( _dataGridViewPhoto.Rows[currentRowIndex].Cells["SortKey"].Value );
 
            int previousRowSortKey = (int)( _dataGridViewPhoto.Rows[currentRowIndex - 1].Cells["SortKey"].Value );
            // I swap the two keys here
            _dataGridViewPhoto.Rows[currentRowIndex].Cells["SortKey"].Value = previousRowSortKey;
            _dataGridViewPhoto.Rows[currentRowIndex - 1].Cells["SortKey"].Value = currentRowSortKey;
 
            // This "commit" works only about 50% of the time.  What am I missing to make
            // the dataGridView push its changed data back to the datasource.
            _dataGridViewPhoto.EndEdit();
            _bindingSourcePhoto.EndEdit();
            _sqlDataAdapterPhoto.Update( _dataSetMaster.Tables["Photo"] );
 
 
            _dataGridViewPhoto.Sort( _dataGridViewPhoto.Columns["SortKey"], ListSortDirection.Ascending );
 
            _dataGridViewPhoto.CurrentCell = _dataGridViewPhoto[0, currentRowIndex - 1];
 
            // If I uncomment this, it works every time.  The textBox is bound to the bindingSource.
            //textBoxPhotoDetail.Text = textBoxPhotoDetail.Text + " ";
 
         }
      }

Open in new window

0
Comment
Question by:bricedeganahl
  • 2
2 Comments
 

Author Comment

by:bricedeganahl
ID: 23602748
I just created an SQL stored procedure to swap the sortKey.  Then I do an sqlDataAdapter.Fill() and a dataGridView.Sort().  Somehow, this is faster and smoother than the working version posted above (with the textBox.Text bogus value change).  I'm stupified as to how a complete refill of the dataTable can be more efficient than just a post to a couple of fields, but its working so don't bother working on this one.
0
 

Accepted Solution

by:
bricedeganahl earned 0 total points
ID: 23602806
If anyone is interested in all the code, this is my whole solution.  Comments welcomed.


      private void buttonPhotoMoveUp_Click( object sender, EventArgs e )
      {
         int currentRowIndex = _dataGridViewPhoto.CurrentCell.RowIndex;
 
         if ( currentRowIndex > 0 )
         {
            int currentRowID = (int)( _dataGridViewPhoto.Rows[currentRowIndex].Cells["PhotoID"].Value );
 
            List<SqlParameter> listSqlParameters = new List<SqlParameter>();
            
            SqlParameter parameter = new SqlParameter();
            parameter.DbType = System.Data.DbType.Int32;
            parameter.Direction = System.Data.ParameterDirection.Input;
            parameter.ParameterName = "@PhotoIDOne";
            parameter.Value = (int)( _dataGridViewPhoto.Rows[currentRowIndex].Cells["PhotoID"].Value );
            listSqlParameters.Add(parameter);
 
            parameter = new SqlParameter();
            parameter.DbType = System.Data.DbType.Int32;
            parameter.Direction = System.Data.ParameterDirection.Input;
            parameter.ParameterName = "@PhotoIDTwo";
            parameter.Value = (int)( _dataGridViewPhoto.Rows[currentRowIndex - 1].Cells["PhotoID"].Value );
            listSqlParameters.Add( parameter );
 
            GenericExecuteStoredProcedure( "SwapPhotoSortKey", _sqlDataAdapterPhoto, "Photo", listSqlParameters );
 
            _dataGridViewPhoto.Sort( _dataGridViewPhoto.Columns["SortKey"], ListSortDirection.Ascending );
            _dataGridViewPhoto.CurrentCell = _dataGridViewPhoto[0, currentRowIndex - 1];
            
         }
      }
 
      private int GenericExecuteStoredProcedure( String storedProcedureName, SqlDataAdapter adapter, String tableName, List<SqlParameter> listSqlParameters )
      {
         SqlConnection connection = new SqlConnection( GetConnectionString() );
         connection.Open();
 
         SqlCommand command = new SqlCommand();
         SqlParameter parameter = new SqlParameter();
         parameter.DbType = System.Data.DbType.Int32;
         parameter.Direction = System.Data.ParameterDirection.Output;
         parameter.ParameterName = "@Identity";
         command.Parameters.Add(parameter);
         
         if ( listSqlParameters != null )
         {
            foreach ( SqlParameter sqlParameter in listSqlParameters )
            {
               command.Parameters.Add( sqlParameter );
            }
         }
         
         command.Connection = connection;
         command.CommandText = storedProcedureName;
         command.CommandType = System.Data.CommandType.StoredProcedure;
         command.ExecuteNonQuery();
         
         int identity = (int)(command.Parameters[0].Value);
         connection.Close();
 
         adapter.Fill( _dataSetMaster, tableName );
         
         return (identity);
 
      }
 
CREATE PROC SwapPhotoSortKey ( @Identity INTEGER OUTPUT, @PhotoIDOne INTEGER, @PhotoIDTwo INTEGER ) AS
   DECLARE @SortKeyOne AS INT
   DECLARE @SortKeyTwo AS INT
 
 
   SET @Identity = 0
   SET @SortKeyOne = (SELECT SortKey FROM Photo WHERE PhotoID = @PhotoIDOne)
   SET @SortKeyTwo = (SELECT SortKey FROM Photo WHERE PhotoID = @PhotoIDTwo)
 
   UPDATE Photo SET SortKey = @SortKeyTwo WHERE PhotoID = @PhotoIDOne
   UPDATE Photo SET SortKey = @SortKeyOne WHERE PhotoID = @PhotoIDTwo
 
GO

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question