Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. in windows Forms

Question

Hi everyone

I have application which retrieves data and updates the table when user enter any data in datagridview.Everyhing is fine but the thing is when i tried to enter any value in cell and try to save for first time it will not allow changes but after refreshing when i try second time for same cell it will allow changes.below is the error i'm getting when i debug. can any help me this is last thing in my project

Error:Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

thankz

Answer 1

Read this link.  Concurrency Violation Explained.

---

Mark the best replies as answers. "Fooling computers since 1971."

http://thesharpercoder.blogspot.com/

Answer 2

hi thankz for replying

I have seen the link but still not able to figure out y the thing in first lace it will not let me save changes but when i refresh and edit same cell that i can save changes.I dont know what and where is wrong

Answer 3

I cannot tell you exactly what is wrong, either.  I have not seen any code.  But, the error is pretty clear.  Something is changing the table while you are using it.  Is there something obvious that you are overlooking? It's your application.  How many users/threads access the data at once?

Rudy   =8^D

---

Mark the best replies as answers. "Fooling computers since 1971."

http://thesharpercoder.blogspot.com/

Answer 4

i'm building this application for multiple user and below is my code and i when ever i click save button i'm getting this error.but when i refresh and edit the same cell i dont have problem.I stuck at this point and it is last point to complete my application.Thankz again for ur help

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Windows.Forms.VisualStyles;



namespace SerialNumberLog
{

    public partial class frmMain : Form
    {

        SqlConnection connection = new SqlConnection("Data Source=fcxp21c;Initial Catalog=FCX;Integrated Security=True;");
        SqlDataAdapter dataAdapter = null;
        DataTable dtLines = null;

        public frmMain()
        {
            InitializeComponent();
            btnSaveUpdates.Enabled = false;
            dgvLineDetails.CellClick += new DataGridViewCellEventHandler(dgvLineDetails_CellClick);
        }

        private void btnRetrieveOrder_Click(object sender, EventArgs e)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            try
            {

                //txtOrderNumber.Text="'txtOrderNumber.Text'";

                //if(txtOrderNumber.Text.Contains("-")) 

                //    txtOrderNumber.Text=txtOrderNumber.Text.Replace("-","");

                if (string.IsNullOrEmpty(txtLocationIdFilter.Text) && string.IsNullOrEmpty(cmbOrderNumber.Text) && string.IsNullOrEmpty(txtSerialNumber.Text) && string.IsNullOrEmpty(txtCustomerId.Text) && string.IsNullOrEmpty(txtCustNoPO.Text) && string.IsNullOrEmpty(dtpStart.Text) && string.IsNullOrEmpty(dtpEnd.Text))

                    MessageBox.Show("Please enter LocationId And/Or OrderNumber And/Or SerialNumber And/Or CustomerId And/Or Customer PO Number");



                else if ((!string.IsNullOrEmpty(dtpStart.Text) && !string.IsNullOrEmpty(dtpEnd.Text)) && (dtpStart.Visible) && (dtpEnd.Visible))
                {

                    string strSQL = "SELECT DISTINCT order_number FROM Serial_Number_Log snl WHERE date_assmbled BETWEEN '" + dtpStart.Value + "' AND '" + dtpEnd.Value + "' AND Order_Number IS NOT NULL AND LTRIM(RTRIM(Order_Number)) <> '' ORDER BY Order_Number;";

                    SqlDataAdapter ordersDA = new SqlDataAdapter(strSQL, connection);
                    DataTable ordersDT = new DataTable();
                    ordersDA.FillSchema(ordersDT,SchemaType.Mapped);
                    ordersDA.Fill(ordersDT);

                    if (ordersDT.Rows.Count > 0)
                    {
                        cmbOrderNumber.Items.Clear();

                        foreach (DataRow row in ordersDT.Rows)
                        {
                            cmbOrderNumber.Items.Add(row["order_number"]);

                        }
                        cmbOrderNumber.SelectedIndex = 0;
                        cmbOrderNumber.Focus();
                    }

                    else
                    {
                        string message = "Orders cannot be found for the specified ";
                        if (!string.IsNullOrEmpty(dtpStart.Text))
                            message += "start date: " + dtpStart.Text;
                        if (!string.IsNullOrEmpty(dtpEnd.Text))
                            message += " End date: " + dtpEnd.Text;

                        MessageBox.Show(message); //added
                    }

                }



                else if ((!string.IsNullOrEmpty(txtSerialNumber.Text)) && (!dtpStart.Visible) && (!dtpEnd.Visible))
                {
                    string strSQL = "Select Distinct Order_Number, Location_id FROM serial_number_log WHERE serial_Number='" + txtSerialNumber.Text + "'";
                    SqlCommand command1 = new SqlCommand(strSQL, connection);
                    SqlDataReader dataReader1 = command1.ExecuteReader();

                    if (dataReader1.HasRows)
                    {
                        string _orderNumber = string.Empty;
                        string _locationId = string.Empty;

                        while (dataReader1.Read())
                        {
                            _orderNumber = dataReader1["order_number"].ToString().Trim();
                            _locationId = dataReader1["location_id"].ToString().Trim();
                        }

                        if (!dataReader1.IsClosed)
                            dataReader1.Close();

                        GetOrderDetails(_orderNumber, _locationId);

                    }

                    else
                        MessageBox.Show("Invalid Serial Number");

                    if (!dataReader1.IsClosed)
                        dataReader1.Close();

                }


                else if ((!string.IsNullOrEmpty(txtLocationIdFilter.Text) || !string.IsNullOrEmpty(txtCustomerId.Text) || !string.IsNullOrEmpty(txtCustNoPO.Text)) && string.IsNullOrEmpty(cmbOrderNumber.Text.Trim()))
                {

                    string strSQL = "SELECT DISTINCT order_number FROM Serial_Number_Log snl WHERE ";
                    if (!string.IsNullOrEmpty(txtLocationIdFilter.Text))
                    {
                        strSQL += " location_id = " + txtLocationIdFilter.Text.Trim();

                        if (!string.IsNullOrEmpty(txtCustomerId.Text))

                            strSQL += " And customer_id=" + txtCustomerId.Text.Trim();

                        if (!string.IsNullOrEmpty(txtCustNoPO.Text))

                            strSQL += " And customer_PO_Number='" + txtCustNoPO.Text.Trim() + "' ";

                        strSQL += " Order By Order_Number;";
                    }

                    else
                    {
                        if (!string.IsNullOrEmpty(txtCustomerId.Text) && string.IsNullOrEmpty(txtCustNoPO.Text))

                            strSQL += " customer_id=" + txtCustomerId.Text.Trim() + " Order By Order_Number;";

                        if (string.IsNullOrEmpty(txtCustomerId.Text) && !string.IsNullOrEmpty(txtCustNoPO.Text))

                            strSQL += " customer_PO_Number='" + txtCustNoPO.Text.Trim() + "' Order By Order_Number;";

                        if (!string.IsNullOrEmpty(txtCustomerId.Text) && !string.IsNullOrEmpty(txtCustNoPO.Text))

                            strSQL += " customer_id=" + txtCustomerId.Text.Trim() + " AND customer_PO_Number='" + txtCustNoPO.Text.Trim() + "' Order By Order_Number;";

                    }

                    SqlDataAdapter ordersDA = new SqlDataAdapter(strSQL, connection);
                    DataTable ordersDT = new DataTable();
                    ordersDA.FillSchema(ordersDT,SchemaType.Mapped);
                    ordersDA.Fill(ordersDT);

                    if (ordersDT.Rows.Count > 0)
                    {
                        cmbOrderNumber.Items.Clear();
                        foreach (DataRow row in ordersDT.Rows)
                        {
                            cmbOrderNumber.Items.Add(row["order_number"]);

                        }
                        cmbOrderNumber.SelectedIndex = 0;
                        cmbOrderNumber.Focus();

                    }

                    else
                    {
                        string message = "Orders cannot be found for the specified ";
                        if (!string.IsNullOrEmpty(txtLocationIdFilter.Text))
                            message += "location Id: " + txtLocationIdFilter.Text;
                        if (!string.IsNullOrEmpty(txtCustomerId.Text))
                            message += " customer Id: " + txtCustomerId.Text;

                        MessageBox.Show(message); //added

                    }
                }

                else
                {
                    GetOrderDetails(cmbOrderNumber.Text, txtLocationIdFilter.Text);
                }


            }
            catch (Exception)
            {
                MessageBox.Show("Invalid Search.Please Check Again.");
            }

        }

        private void GetOrderDetails(string orderNumber, string locationId)
        {

            string strSQL = "Select TOP (1) Serial_number, Customer_Name, Order_Number, Customer_PO_Number, Location_id, customer_id FROM serial_number_log WHERE Order_Number='" + orderNumber + "'";

            if (!string.IsNullOrEmpty(txtLocationIdFilter.Text.Trim()))

                strSQL += " AND location_id = " + txtLocationIdFilter.Text;

            SqlCommand command = new SqlCommand(strSQL, connection);

            SqlDataReader dataReader = command.ExecuteReader();

                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        txtOrderNumberHeader.Text = dataReader["Order_Number"].ToString();
                        txtCustomerName.Text = dataReader["customer_name"].ToString();
                        txtCustomerPONumber.Text = dataReader["customer_po_number"].ToString();
                        txtLocationid.Text = dataReader["location_id"].ToString();
                        //txtLocationIdFilter.Text = dataReader["location_id"].ToString();
                        txtCustomerId.Text = dataReader["customer_id"].ToString();
                    }

                    if (!dataReader.IsClosed)
                        dataReader.Close();
                    dataAdapter = new SqlDataAdapter("SELECT ID,Serial_number 'SerialNumber', Line_Number 'LineNumber', Item_description 'ItemDescription', Fail_cond_pos_act_orient 'FailCondPosActOrient', Date_assmbled 'DateAssembled', Assembler_initials 'AssemblerInitials',  Labour_units 'LaborUnits', Comments, Expd_comments 'ExpdComments', Tech_comments 'TechComments', Hide_flag 'HideFlag', Invoice_flag 'InvoiceFlag', Issue_flag 'IssueFlag',Fail_Position 'FailPosition',Actuator_Orientation 'ActuatorOrientation',Valve_Serial_Number 'ValveSerialNumber',Position_Serial_Number 'PositionSerialNumber',Other_Serial_Number 'OtherSerialNumber',Limitorque_serial_number 'LimiTorqueSerialNumber',expd_note_id, record_in_use 'RecordInUse' FROM Serial_Number_Log snl WHERE Order_Number = '" + orderNumber + "'" + "Order By line_Number", connection);
                    dtLines = new DataTable();
                    dataAdapter.FillSchema(dtLines,SchemaType.Mapped);
                    dataAdapter.Fill(dtLines);

                    if (dtLines != null && dtLines.Rows.Count > 0)
                        btnSaveUpdates.Enabled = true;
                    dgvLineDetails.Columns.Clear();
                    dgvLineDetails.DataSource = dtLines;
                    dgvLineDetails.Columns["ItemDescription"].ReadOnly = true;
                    dgvLineDetails.Columns["LineNumber"].ReadOnly = true;
                    dgvLineDetails.Columns["SerialNumber"].ReadOnly = false;
                    dgvLineDetails.Columns["ID"].Visible = false;
                    dgvLineDetails.Columns["expd_note_id"].Visible = false;
                    dgvLineDetails.Columns["RecordInUse"].Visible = false;

                    //started here to disable the sorting by user
                    for (int i = 0; i < dgvLineDetails.ColumnCount; i++)
                    {
                        dgvLineDetails.Columns["SerialNumber"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["LineNumber"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["ItemDescription"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["FailCondPosActOrient"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["DateAssembled"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["AssemblerInitials"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["LaborUnits"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["Comments"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["ExpdComments"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["TechComments"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["HideFlag"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["InvoiceFlag"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["IssueFlag"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["FailPosition"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["ActuatorOrientation"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["ValveSerialNumber"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["PositionSerialNumber"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["OtherSerialNumber"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["expd_note_id"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        dgvLineDetails.Columns["LimiTorqueSerialNumber"].SortMode = DataGridViewColumnSortMode.NotSortable;
                        //dgvLineDetails.Columns["hide_flag"].ToString().ToUpper();

                    }

                    //ended here
                    DataGridViewButtonColumn noteCol = new DataGridViewButtonColumn();
                    noteCol.Text = "Notes";//changed from update Notes to Notes
                    noteCol.UseColumnTextForButtonValue = true;
                    noteCol.HeaderText = "EXPDNotes";//CHANGED FROM UPDATE TO EXPD
                    noteCol.Name = "Notes";
                    noteCol.Visible = true;
                    noteCol.DisplayIndex = 21;
                    dgvLineDetails.Columns.Add(noteCol);
                }

                else
                {
                    MessageBox.Show("Order cannot be found!");
                }

      
        }

        private void dgvLineDetails_CellClick(object sender, DataGridViewCellEventArgs e)
        {

            // Ignore clicks that are not on button cells. 

            if (e.RowIndex < 0 || e.ColumnIndex !=
                dgvLineDetails.Columns["Notes"].Index) return;
            string noteId = dtLines.Rows[e.RowIndex]["expd_note_id"].ToString();

            if (string.IsNullOrEmpty(noteId))

                MessageBox.Show("No EXPD Notes for this line.");

            else
            {
                frmNotes NotesFrm = new frmNotes();
                NotesFrm.NoteId = noteId;
                NotesFrm.OrderNumber = txtOrderNumberHeader.Text.Trim();
                NotesFrm.LineNumber = dtLines.Rows[e.RowIndex]["LineNumber"].ToString();
                NotesFrm.InitializeNotes();
                NotesFrm.ShowDialog();

                //connection.Open();
            }

        }
        private void btnSaveUpdates_Click(object sender, EventArgs e)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
           
            try
            {
             

                
                SqlCommandBuilder myBuilder = new SqlCommandBuilder(dataAdapter);
               
                dataAdapter.UpdateCommand = myBuilder.GetUpdateCommand();
                dataAdapter.FillSchema(dtLines, SchemaType.Mapped);
                dataAdapter.Update(dtLines);
                connection.Close();


                if (connection.State != ConnectionState.Open)
                    connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "Usp_SNL_UnLockItemForEdit";
                command.Parameters.Add(
                new SqlParameter("@Order_Number", txtOrderNumberHeader.Text.Trim()));
                //command.Parameters.Add(
                //new SqlParameter("@sysUserName", dgvLineDetails.Columns["SYs_UserName"].HeaderText));               

                SqlDataReader dataReader = command.ExecuteReader();

                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                        }

                        if (!dataReader.IsClosed)
                            dataReader.Close();                                      

                    }

                         MessageBox.Show("Order has been updated!");
                    }


            catch (Exception ex)
            {

                MessageBox.Show("Save failed. Please verify the data you have modified.");
            }

            if (connection.State == ConnectionState.Open)
                connection.Close();
        }

        //to get rows if they modified
       // private bool isUpdateNeeded()
        //{
        //    DataTable dt = dtLines.GetChanges(DataRowState.Modified);
        //    return (dt.Rows.Count > 0);
        //}

        //started here
        private void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)
        {

            if (args.RecordsAffected == 0)
            {
                args.Row.RowError = "Optimistic Concurrency Violation Encountered";
                args.Status = UpdateStatus.SkipCurrentRow;

            }
        }
        //ended here
        private void UnlockLineBeingEdited()
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();


                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "Usp_SNL_UnLockItemForEdit";
                command.Parameters.Add(
                new SqlParameter("@Order_Number", txtOrderNumberHeader.Text.Trim()));
                //command.Parameters.Add(
                //new SqlParameter("@sysUserName", dgvLineDetails.Columns["SYs_UserName"].HeaderText));

                SqlDataReader dataReader = command.ExecuteReader();

                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                        }

                        if (!dataReader.IsClosed)
                            dataReader.Close();
                        dataReader.Close();
                    }

                //foreach (DataRow dr in dtLines.Rows)
                //{
                //    if (Convert.ToBoolean(dr["RecordInUse"]) != true)
                //    {

                //        SqlCommand command = new SqlCommand();
                //        command.Connection = connection;
                //        //command.CommandText = "UPDATE serial_number_log SET Record_In_Use=0, Sys_UserName = System_user, serial_number='" + dr["SerialNumber"].ToString() + "', Fail_cond_pos_act_orient='" + dr["Failcondposactorient"].ToString() + "', DateAssembled='" + dr[" Date_assmbled"].ToString() + "',+'" + dr["Assembler_initials"].ToString() + "',+'" + dr["Labour_units"].ToString() + "',+'" + dr["Comments"].ToString() + "',+'" + dr["Expd_comments"].ToString() + "',+'" + dr["Tech_comments"].ToString() + "',+'" + dr["Hide_flag"].ToString() + "',+'" + dr["Invoice_flag"].ToString() + "',+'" + dr["Issue_flag"].ToString() + "','" + dr["Fail_Position"].ToString() + "',+'" + dr["Actuator_Orientation"].ToString() + "',+'" + dr["Valve_Serial_Number"].ToString() + "',+'" + dr["Position_Serial_Number"].ToString() + "',+'" + dr["Other_Serial_Number"].ToString() + "',+'" + dr["Limitorque_serial_number"].ToString() + "',+'" + dr["expd_note_id"].ToString() + "' WHERE Order_Number='" + txtOrderNumberHeader.Text + "'";

                //        command.CommandText = "UPDATE serial_number_log SET Record_In_Use=0, Sys_UserName = System_user WHERE ID=" + dr["ID"].ToString() + " AND Order_Number='" + txtOrderNumberHeader.Text + "'";

                //        command.ExecuteNonQuery();
                //    }
                //}

            }


            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {

            UnlockLineBeingEdited();

            if (connection.State == ConnectionState.Open)
                connection.Close();
            this.Close();
        }


        private void btnReset_Click(object sender, EventArgs e)
        {
            UnlockLineBeingEdited();

            dtpStart.Value = Convert.ToDateTime("01/23/2004");
            dtpEnd.Value = DateTime.Now;
            txtLocationIdFilter.Clear();
            cmbOrderNumber.Items.Clear();
            cmbOrderNumber.Text = string.Empty;
            txtCustNoPO.Clear();
            ClearSelections();
            btnSaveUpdates.Enabled = false;
            btnRetrieveOrder.Focus();

            if (connection.State == ConnectionState.Open)
                connection.Close();
            
        }


        private void ClearSelections()
        {

            txtCustomerName.Clear();
            txtCustomerPONumber.Clear();
            txtLocationid.Clear();
            txtOrderNumberHeader.Clear();
            txtSerialNumber.Clear();
            txtCustomerId.Clear();
            dgvLineDetails.Columns.Clear();
            dgvLineDetails.DataSource = null;

        }

        //private void frmMain_FormClosed(object sender, FormClosedEventArgs e)
        //{
        //    UnlockLineBeingEdited();
        //    if (connection.State == ConnectionState.Open)
        //        connection.Close();
        //}



        private void cmbOrderNumber_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(cmbOrderNumber.Text))
                GetOrderDetails(cmbOrderNumber.Text, txtLocationIdFilter.Text);
            else
                ClearSelections();


        }




        private void btnhide_Click(object sender, EventArgs e)
        {
            this.dtpStart.Enabled = false;
            btnhide.Visible = false;
            dtpStart.Enabled = false;
            dtpEnd.Enabled = false;
            dtpStart.Visible = false;
            dtpEnd.Visible = false;

            dtpStart.Visible = false;
            dtpEnd.Visible = false;
            lblEndDate.Visible = false;
            lblStartDate.Visible = false;

        }

        private void btnshow_Click(object sender, EventArgs e)
        {

            dtpStart.Visible = true;
            dtpEnd.Visible = true;
            lblStartDate.Visible = true;
            lblEndDate.Visible = true;
            btnhide.Visible = true;
            dtpStart.Enabled = true;
            dtpEnd.Enabled = true;
            dtpStart.Visible = true;
            dtpEnd.Visible = true;

        }


        //started here to clear on ESC button

        protected override void OnKeyDown(KeyEventArgs e)
        {
            
            base.OnKeyDown(e);
            if (e.KeyCode == Keys.Escape)
            {
                UnlockLineBeingEdited();
                txtLocationIdFilter.Clear();
                cmbOrderNumber.Items.Clear();
                cmbOrderNumber.Text = string.Empty;
                txtCustNoPO.Clear();
                EscClearSelections();
                btnSaveUpdates.Enabled = false;
                btnRetrieveOrder.Focus();
                dtpStart.Value = Convert.ToDateTime("01/23/2004");
                dtpEnd.Value = DateTime.Now;
                if (connection.State == ConnectionState.Open)
                    connection.Close();


            }
        }

        private void EscClearSelections()
        {

            txtCustomerName.Clear();
            txtCustomerPONumber.Clear();
            txtLocationid.Clear();
            txtOrderNumberHeader.Clear();
            txtSerialNumber.Clear();
            txtCustomerId.Clear();
            dgvLineDetails.Columns.Clear();
            dgvLineDetails.DataSource = null;
            
        }







        private void dgvLineDetails_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.Value != null && (e.ColumnIndex == 11 || e.ColumnIndex == 12 || e.ColumnIndex == 13))
            {
                e.Value = e.Value.ToString().ToUpper();
                e.FormattingApplied = true;
            }
        }

        private void dgvLineDetails_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            MessageBox.Show(e.Exception.Message);
        }

        private void dgvLineDetails_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {

            try
            {


                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "Usp_SNL_LockItemForEdit";
                command.Parameters.Add(
                new SqlParameter("@Order_Number", txtOrderNumberHeader.Text.Trim()));
                command.Parameters.Add(
                new SqlParameter("@ID", dtLines.Rows[e.RowIndex]["ID"].ToString()));


                //if (dataReader.HasRows)
                using(SqlDataReader dataReader = command.ExecuteReader())
                {
                    if (dataReader.HasRows)
                    {

                    }

                    if (!dataReader.IsClosed)
                        dataReader.Close();
                    dataReader.Close();
                }

            }
            catch (Exception ex)
            {
                //Convert.ChangeType(dgvLineDetails.Rows[e.RowIndex].Cells["RecordInUse"].Value, dgvLineDetails.Rows[e.RowIndex].Cells["RecordInUse"].ValueType);
                dgvLineDetails.Rows[e.RowIndex].Cells["RecordInUse"].Value = true;
                e.Cancel = true;
                MessageBox.Show(ex.Message);
            }

        }

        private void frmMain_FormClosed(object sender, FormClosedEventArgs e)
        {
            UnlockLineBeingEdited();
            if (connection.State == ConnectionState.Open)
                connection.Close();
            
        }   

  



    }



}

Answer 5

Hmm, a quick glance does reveal any threading.  That row locking stuff may be the culprit.  Why do you do it?

That's prohibitively long.  I would need to connect a lot dots.  Can you post a test version of your application that reproduces the problem to this web site that uses the same login as these forums.    skydrive.live.com  Save your project in a ZIP file.  Be sure to include a test database with it.

Rudy   =8^D

---

Mark the best replies as answers. "Fooling computers since 1971."

http://thesharpercoder.blogspot.com/

Answer 6

Hi Row locking is to stop multiple user trying to edit same row.when any other user acces it will lock the row and display to user.

I have Uploaded all the required doc for this project.Thankz for ur help

Answer 7

Hi I have an issue which is not resolved yet I'm unable to resolve please help me out

Answer 8

Hi Row locking is to stop multiple user trying to edit same row.when any other user acces it will lock the row and display to user.

I have Uploaded all the required doc for this project.Thankz for ur help

Can you post a link to the ZIP file so that other folks can download your project and take a look at it?

Rudy   =8^D

---

Mark the best replies as answers. "Fooling computers since 1971."

http://thesharpercoder.blogspot.com/

Answer 9

Hi thankz for ur help but i figured the solution for that but i have other issue.I have date time column in my data grid view when no value is entered its taking date time min value.How can i avoid and make blank if user does not enter any value.

thankz

Answer 10

Hi thankz for ur help but i figured the solution for that but i have other issue.I have date time column in my data grid view when no value is entered its taking date time min value.How can i avoid and make blank if user does not enter any value.

thankz

You can always define the field to not be null.

See the field, "Allow Nulls", is set to no.  This is in the table definition for a particular field, Modified Date.

---

Mark the best replies as answers. "Fooling computers since 1971."

http://thesharpercoder.blogspot.com/

Answer 11

Hi  i have set  date time value to null in table but when update any cell in my application then date time column is taking min value .How can i make it null

Answer 12

Hi mm6991,
According to your code, it seems that dgvLineDetails is the DataGridView and it is bound with the DataTable dtLines.
Mostly, you set a Default value for the column, please check the column DefaultValue of DataTable dtLines and the Table in the Database.
If they are not null, you need to change the designer of the column in the Table or you need to set default value to null by code.
Please check the sample below.

private void Form1_Load(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("x");
    DataColumn dc = new DataColumn("DataTime", typeof(DateTime));
    dt.Columns.Add(dc);
    dc.DefaultValue = DateTime.MinValue;
    // if the DefaultValue is not null, please set it to null
    //dc.DefaultValue = null;
    dt.Rows.Add("x0", DateTime.Now);
    this.dataGridView1.DataSource = dt;
}

In addition, as you have solved the former issue, could you please share the solution with us?
Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 13

Thankz for reply
It is the dateAssembled column which i want to set to null
I have checked in table design dateAssembled column does not have default value and it is set to accept null.
I have check my code i dont see anywhere like i have given any min value to dateAssembled column but when i debug it see it is haveing min datetime value.
Even i i tried to set to null but still its taking min value but for checking i changed from null to current date then it is taking current date
but im unable to set null/blank.I have attached my table design

Regarding my previous issue

In my code i have cell edit event which updates the bit type column to 1 so  that no other user tries to modify that particular line when user clicks save button that bit ytpe column value would be 0.So I just added code to get changes like below which solved my prblem.I think datagrid view was no getting changes once particular row was in edit mode.The same code i was using in my if satement  in save button event .

dgvLineDetails.Rows[e.RowIndex].Cells[

"RecordInUse"].Value = true;

                    dgvLineDetails.Rows[e.RowIndex].Cells[

"Sys_UserName"].Value = WindowsIdentity.GetCurrent().Name;

Answer 14

What's the retrun value of dgvLineDetails.Columns["dateAssembled "].DefaultValue?

If it is not null, please set it to null by code.

In addition, Where's the table design? I can't find it in your post.

Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 15

HI I'm sorry for late responce i just returned today to work.Return value for dateAssembled initially will be null. I'm unable to insert table design image so i loaded table script and also loaded code were i set my dateAssembled value to null but its not affecting anything

USE [FCX]
GO
/****** Object:  Table [dbo].[Serial_Number_Log]    Script Date: 05/21/2012 08:57:49 ******/
SET ANSI_NULLS ON
GO

        private void btnSaveUpdates_Click(object sender, EventArgs e)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();


            try
            {

                
                foreach (DataRow dr in dtLines.Rows)
                {
           
                    //if (Convert.ToBoolean(dr["RecordInUse"]) == true)
                     //if(Convert.ToString(dr["Sys_UserName"]) == WindowsIdentity.GetCurrent().Name)
                    if (Convert.ToBoolean(dr["RecordInUse"]) && Convert.ToString(dr["Sys_UserName"]) == WindowsIdentity.GetCurrent().Name)

                    {
                        SqlCommand command = new SqlCommand();
                        command.Connection = connection;
                        if (Convert.IsDBNull(dr["DateAssembled"]) == true)
                            if (dr["DateAssembled"].ToString() == string.Empty)
                                     dr["DateAssembled"] = DBNull.Value;                                       
                                                                                    
                                             
                        if (!string.IsNullOrEmpty(dr["LaborUnits"].ToString()))
                            command.CommandText = "UPDATE serial_number_log SET Record_In_Use=0, Sys_UserName = System_user, serial_number='" + dr["SerialNumber"].ToString() + "', Fail_cond_pos_act_orient='" + dr["Failcondposactorient"].ToString() + "', Date_assmbled='" + dr["DateAssembled"].ToString() + "', Assembler_initials='" + dr["AssemblerInitials"].ToString() + "', Labour_units=" + dr["LaborUnits"].ToString() + ", Comments='" + dr["Comments"].ToString() + "', Expd_comments='" + dr["ExpdComments"].ToString() + "', Tech_comments='" + dr["TechComments"].ToString() + "', Hide_flag='" + dr["HideFlag"].ToString() + "', Invoice_flag='" + dr["InvoiceFlag"].ToString() + "', Issue_flag='" + dr["IssueFlag"].ToString() + "', Fail_Position='" + dr["FailPosition"].ToString() + "', Actuator_Orientation='" + dr["ActuatorOrientation"].ToString() + "', Valve_Serial_Number='" + dr["ValveSerialNumber"].ToString() + "', Position_Serial_Number='" + dr["PositionSerialNumber"].ToString() + "', Other_Serial_Number='" + dr["OtherSerialNumber"].ToString() + "', Limitorque_serial_number='" + dr["LimiTorqueSerialNumber"].ToString() + "' WHERE ID=" + dr["ID"].ToString() + " AND Order_Number='" + txtOrderNumberHeader.Text + "'";
                        else
                            command.CommandText = "UPDATE serial_number_log SET Record_In_Use=0, Sys_UserName = System_user, serial_number='" + dr["SerialNumber"].ToString() + "', Fail_cond_pos_act_orient='" + dr["Failcondposactorient"].ToString() + "', Date_assmbled='" + dr["DateAssembled"].ToString() + "', Assembler_initials='" + dr["AssemblerInitials"].ToString() + "', Comments='" + dr["Comments"].ToString() + "', Expd_comments='" + dr["ExpdComments"].ToString() + "', Tech_comments='" + dr["TechComments"].ToString() + "', Hide_flag='" + dr["HideFlag"].ToString() + "', Invoice_flag='" + dr["InvoiceFlag"].ToString() + "', Issue_flag='" + dr["IssueFlag"].ToString() + "', Fail_Position='" + dr["FailPosition"].ToString() + "', Actuator_Orientation='" + dr["ActuatorOrientation"].ToString() + "', Valve_Serial_Number='" + dr["ValveSerialNumber"].ToString() + "', Position_Serial_Number='" + dr["PositionSerialNumber"].ToString() + "', Other_Serial_Number='" + dr["OtherSerialNumber"].ToString() + "', Limitorque_serial_number='" + dr["LimiTorqueSerialNumber"].ToString() + "' WHERE ID=" + dr["ID"].ToString() + " AND Order_Number='" + txtOrderNumberHeader.Text + "'";

                        command.ExecuteNonQuery();                 
                                                               
                        
                    }
                }

                GetOrderDetails(cmbOrderNumber.Text, txtLocationIdFilter.Text);
                MessageBox.Show("Order has been updated.");

            }
            catch (Exception ex)
            {

                MessageBox.Show("Save failed. Please verify the data you have modified." + ex.Message);
            }
        }

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Serial_Number_Log](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Serial_number] [varchar](255) NULL,
	[Customer_Name] [varchar](255) NULL,
	[Customer_id] [decimal](19, 0) NULL,
	[Order_Number] [varchar](255) NULL,
	[Item_description] [varchar](255) NULL,
	[Fail_cond_pos_act_orient] [varchar](255) NULL,
	[Date_assmbled] [datetime] NULL,
	[Assembler_initials] [varchar](30) NULL,
	[Customer_PO_Number] [varchar](255) NULL,
	[Line_Number] [decimal](19, 0) NULL,
	[Location_id] [decimal](19, 0) NULL,
	[Labour_units] [decimal](10, 2) NULL,
	[Comments] [text] NULL,
	[Expd_comments] [text] NULL,
	[Tech_comments] [text] NULL,
	[Hide_flag] [char](1) NULL,
	[Invoice_flag] [char](1) NULL,
	[Issue_flag] [char](1) NULL,
	[Expd_note_id] [decimal](19, 0) NULL,
	[Fail_position] [varchar](255) NULL,
	[Actuator_Orientation] [varchar](255) NULL,
	[Valve_Serial_Number] [varchar](255) NULL,
	[Position_Serial_Number] [varchar](255) NULL,
	[Other_Serial_Number] [varchar](255) NULL,
	[Limitorque_serial_number] [varchar](255) NULL,
	[Date_Created] [datetime] NULL,
	[Date_Modified] [datetime] NULL,
	[Sys_UserName] [varchar](255) NULL,
	[Record_In_Use] [bit] NULL,
 CONSTRAINT [PK_Serial_Number_Log] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UC_SerialNumber_OrderNumber_LineNumber_LocationID] UNIQUE NONCLUSTERED 
(
	[Serial_number] ASC,
	[Order_Number] ASC,
	[Line_Number] ASC,
	[Location_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF