Convert datagridview data to DataTable

Mixing GUI code and database code violates the single responsibility principle. One class (here your form) should have at the best one responsibility. One method (here Suggestion()) should have at best one responsibility. Your method is retrieving items from the GUI and is storing them into the database.

---

Based on the naming guidelines

• method names should be made out of verbs or verb phrases.
• variables should be named (also it is not mentioned explicitly for variables which are local to a method) using camelCase casing. You shouldn't use any type of snake_Case casing.

At least if you have choosen a style, also this style isn't following the guidelines you should stick to the choosen style. Right now you are mixing the casing styles.

---

Calling Convert.ToDouble() on a double or calling ToString() on a String is redundant.

---

Your loops are starting at 1 and you are accessing the items by [iterator -1]. You should start at 0 as every array in NET.

The starting at 1 and the ending condition of < thegrid.RowCount will let you miss one row.

---

You should retrieve Cells["PUR LT"] only if partNumber == material.

---

Both OleDbConnection and OleDbCommand are implementing IDisposable so enclosing the usage in a using block will automatically call Dispose() on the objects and therefor also close the connection.

---

Instead of iterating over the rows of the datagridviews you should consider to use linq to do the job.

---

You should always assume that a cell of the datagridview can be DBNull.Value. So a checking for DBNull should be done.

---

Refactoring the former method with implementing the mentioned points will lead to a new class for which I have choosen the name RowContent

public class RowContent { public bool IsValid { get; private set; } public DateTime RequestedDate { get; private set; } public String Material { get; private set; } public String Quantity { get; private set; } public DateTime SuggestedDate { get; private set; } public RowContent(string requestedDate, string material, string quantity) { RequestedDate = Convert.ToDateTime(requestedDate); Material = material; Quantity = quantity; } public void SetPureLeadTime(String leadTime) { SuggestedDate = GetWorkDays.AddWorkdays(RequestedDate, Convert.ToDouble(leadTime)); IsValid = true; } } 

next we add a method for checking if the givven DataGridViewCell's value is DBNull.

private bool IsNotDBNull(DataGridViewCell cell) { return !cell.Value.Equals(DBNull.Value); } 

No we extract the retrieving of the datagridviews values to a separate method.

private IEnumerable<RowContent> GetSuggestedContents(DataGridView firstGrid, DataGridView secondGrid) { IEnumerable<RowContent> contents = firstGrid.Rows .Cast<DataGridViewRow>() .Where(r=> IsNotDBNull(r.Cells["Requested_Date"]) && IsNotDBNull(r.Cells["Material"]) && IsNotDBNull(r.Cells["Requierd_Qty"])) .Select(r => new RowContent(r.Cells["Requested_Date"].Value.ToString(), r.Cells["Material"].Value.ToString(), r.Cells["Requierd_Qty"].Value.ToString())); foreach (var rowContent in contents) { String leadTime = secondGrid.Rows .Cast<DataGridViewRow>() .Where(r => IsNotDBNull(r.Cells["PartNumber"]) && r.Cells["PartNumber"].Value.ToString() == rowContent.Material) .Select(r => r.Cells["PUR LT"].Value.ToString()).FirstOrDefault(); if (String.IsNullOrEmpty(leadTime)) { continue; } rowContent.SetPureLeadTime(leadTime); } return contents.Where(c => c.IsValid); } 

which can be called like

IEnumerable<RowContent> contents = GetSuggestedContents(dataGridView9, dataGridView10); 

And last but not least the database manipulating code which returns an IList<string> containing the Message property of any occured OleDbException

private IList<string> UpdateSuggestedContents(IEnumerable<RowContent> contents) { IList<string> errorMessages = new List<string>(); using (OleDbConnection connection = new OleDbConnection("your connection string here")) using (OleDbCommand oleDbCmd = new OleDbCommand("InsertSuggested", connection)) { oleDbCmd.CommandType = CommandType.StoredProcedure; OleDbParameter partNumberParameter = oleDbCmd.Parameters.Add("@PartNumber", OleDbType.VarChar, 30); OleDbParameter adjustedDateParameter = oleDbCmd.Parameters.Add("@AdjustedDate", OleDbType.Date, 50); OleDbParameter suggestedDateParameter = oleDbCmd.Parameters.Add("@SuggestedDate", OleDbType.Date, 50); OleDbParameter quantityParameter = oleDbCmd.Parameters.Add("@Qty", OleDbType.VarChar, 30); connection.Open(); foreach (RowContent content in contents) { partNumberParameter.Value = content.Material; adjustedDateParameter.Value = content.RequestedDate; suggestedDateParameter.Value = content.SuggestedDate; quantityParameter.Value = content.Quantity; try { oleDbCmd.ExecuteNonQuery(); } catch (OleDbException ex) { errorMessages.Add(ex.Message); } } } return errorMessages; }