I have been working on an application to update data in a database from a datagridviewer. The issue I am running into here is that after I change a row, I try to refill the data adapter so I am able to see a refreshed view of the database. However, when I try that, I always get an error on the BindingSource_PositionChanged event. I'm assuming it's because I am flushing out what it was bound to and doesn't know what to do. Is there any way around this so I can refresh the data grid but not get the error?
I attached a screenshot of the error.
Code Below:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace SBTForceClose
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void LoadGrid()
{
dataGridView1.DataSource = dataSet1.LKP_SBT_FORCE_CLOSE;
dataGridView1.Refresh();
}
private void button1_Click(object sender, EventArgs e)
{
LoadGrid();
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'dataSet1.LKP_SBT_FORCE_CLOSE' table. You can move, or remove it, as needed.
this.lKP_SBT_FORCE_CLOSETableAdapter.Fill(this.dataSet1.LKP_SBT_FORCE_CLOSE);
}
//Initialize variables for all columns.
Int32 VAL1;
Int32 VAL2;
string VAL3 = "";
Int32 VAL4;
DateTime VAL5;
DateTime VAL6;
Int32 VAL7;
Int32 VAL8;
string VAL9 = "";
string VAL10 = "";
string VAL11 = "";
DateTime VAL12;
string VAL13 ="";
DateTime VAL14;
DateTime VAL15;
string VAL16 ="";
string VAL17 ="";
string VAL18 ="";
string VAL19 ="";
string VAL20 ="";
private DataRow LastDataRow = null;
private void UpdateRowToDatabase()
{
if (LastDataRow!=null)
{
if (LastDataRow.RowState == DataRowState.Modified)
{
if (MessageBox.Show("This row has been changed. Are you sure you would like to update this record?", "!Record Changed!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
//GET ALL COLUMN DATA FOR CURRENT ROW SELECTED. AT THIS POINT WE KNOW IT'S BEEN MODIFIED.
dataGridView1.RowsDefaultCellStyle.SelectionBackColor = Color.Red;
VAL1 = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString());
VAL2 = Convert.ToInt32(dataGridView1.CurrentRow.Cells[1].Value.ToString());
VAL3 = dataGridView1.CurrentRow.Cells[2].Value.ToString();
VAL4 = Convert.ToInt32(dataGridView1.CurrentRow.Cells[3].Value.ToString());
VAL5 = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[4].Value.ToString());
VAL6 = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[5].Value.ToString());
VAL7 = Convert.ToInt32(dataGridView1.CurrentRow.Cells[6].Value.ToString());
VAL8 = Convert.ToInt32(dataGridView1.CurrentRow.Cells[7].Value.ToString());
VAL9 = dataGridView1.CurrentRow.Cells[8].Value.ToString();
VAL10 = dataGridView1.CurrentRow.Cells[9].Value.ToString();
VAL11 = dataGridView1.CurrentRow.Cells[10].Value.ToString();
VAL12 = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[11].Value.ToString());
VAL13 = dataGridView1.CurrentRow.Cells[12].Value.ToString();
VAL14 = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[13].Value.ToString());
VAL15 = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[14].Value.ToString());
VAL16 = dataGridView1.CurrentRow.Cells[15].Value.ToString();
VAL17 = dataGridView1.CurrentRow.Cells[16].Value.ToString();
VAL18 = dataGridView1.CurrentRow.Cells[17].Value.ToString();
VAL19 = dataGridView1.CurrentRow.Cells[18].Value.ToString();
VAL20 = dataGridView1.CurrentRow.Cells[19].Value.ToString();
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "MyConnString";
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
conn.Open();
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE LKP_SBT_FORCE_CLOSE SET VENDOR_NUMBER = ?, VENDOR_DESC = ?, STORE_NUMBER = ?, EFFECTIVE_START_DATE = ?, FORCE_CLOSE_DATE = ?, CATEGORY_NUMBER = ?, CATEGORY_ID = ?, IS_ALL_STORES = ?, IS_CATEGORY_CLOSE = ?, CURRENT_FLAG = ?, EXTRACT_DATE = ?, SOURCE_TABLE_NAME = ?, CREATE_DATE = ?, MAINT_DATE = ?, CREATE_UID = ?, MAINT_UID = ?, DELETE_FLAG = ?, DELETE_UID = ? WHERE FORCE_CLOSE_SCHED_ID = ?";
cmd.Parameters.AddWithValue("@VAL2", VAL2);
cmd.Parameters.AddWithValue("@VAL3", VAL3);
cmd.Parameters.AddWithValue("@VAL4", VAL4);
cmd.Parameters.AddWithValue("@VAL5", VAL5);
cmd.Parameters.AddWithValue("@VAL6", VAL6);
cmd.Parameters.AddWithValue("@VAL7", VAL7);
cmd.Parameters.AddWithValue("@VAL8", VAL8);
cmd.Parameters.AddWithValue("@VAL9", VAL9);
cmd.Parameters.AddWithValue("@VAL10", VAL10);
cmd.Parameters.AddWithValue("@VAL11", VAL11);
cmd.Parameters.AddWithValue("@VAL12", VAL12);
cmd.Parameters.AddWithValue("@VAL13", VAL13);
cmd.Parameters.AddWithValue("@VAL14", VAL14);
cmd.Parameters.AddWithValue("@VAL15", VAL15);
cmd.Parameters.AddWithValue("@VAL16", VAL16);
cmd.Parameters.AddWithValue("@VAL17", VAL17);
cmd.Parameters.AddWithValue("@VAL18", VAL18);
cmd.Parameters.AddWithValue("@VAL19", VAL19);
// cmd.Parameters.AddWithValue("@VAL20", VAL20);
cmd.Parameters.AddWithValue("@VAL1", VAL1);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
conn.Close();
}
finally
{
conn.Close();
}
}
}
}
}
private void lKPSBTFORCECLOSEBindingSource_PositionChanged(object sender, EventArgs e)
{
//Once a row has been updated and I try to fill the table adapter again, I get an error here saying it's not set to an instance of an object.
BindingSource thisBindingSource = (BindingSource)sender;
DataRow thisDataRow = ((DataRowView)thisBindingSource.Current).Row;
if (thisDataRow==LastDataRow)
{
throw new ApplicationException("Something went wrong!");
}
UpdateRowToDatabase();
LastDataRow = thisDataRow;
}
private void RefreshButton_Click(object sender, EventArgs e)
{
// I try to get a refreshed list from the database for the grid once we update a row. I originally had this right after the query execution and it failed.
// I Decided to try it here but it still fails.
this.lKP_SBT_FORCE_CLOSETableAdapter.Fill(this.dataSet1.LKP_SBT_FORCE_CLOSE);
}
}
}
