How to bind values from DatagridView to Item list in C#?

[TatsuSheva]

I am doing a Knapsack application in C#.

I have an excel file where I have a list of products, price, quantity and from which I populate my datagridview1. When it is populated, I have a TextBox where I choose any amount, and then according to this amount the datagridview2 is populated with products where the total is equal to the amount that I have entered.

The problem is that I'm getting this error when I want my datagridview2 to be populated:

Unable to cast object of type 'System.DBNull' to other types....
​

Here is my code:

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
configureDataGridViewColumns();
}

public bool IsReusable
{
get { return true; }
}

public void ProcessRequest(DataGridView context)
{
List<Item> items = new List<Item>();
foreach (DataGridViewRow dr in dataGridView2.Rows)
{
//I've assumed imaginary properties ColName and ColValue in MyItem class
items.Add(new Item(Convert.ToString(dr.Cells[1].Value),
Convert.ToInt32(dr.Cells[3].Value), Convert.ToInt32(dr.Cells[2].Value),
Convert.ToDouble(dr.Cells[0].Value)));
}

int capacity = Convert.ToInt32(montant.Text);

ItemCollection[] ic = new ItemCollection[capacity + 1];

for (int i = 0; i <= capacity; i++) ic = new ItemCollection();

for (int i = 0; i < items.Count; i++)
for (int j = capacity; j >= 0; j--)
if (j >= items.Weight)
{
double quantity = Math.Min(items.Quantity, j / items.Weight);
for (int k = 1; k <= quantity; k++)
{
ItemCollection lighterCollection = ic[j - k * items.Weight];
int testValue = lighterCollection.TotalValue +
k * items.Value;

if (testValue > ic[j].TotalValue)
(ic[j] = lighterCollection.Copy()).AddItem(items, k);
}
}

foreach (KeyValuePair<string, int> kvp in ic[capacity].Contents)
{
string[] row = new string[] {kvp.Key, kvp.Value.ToString()};
context.Rows.Add(row);
}
}

private void configureDataGridViewColumns()
{
DataGridViewColumnCollection columns = dataGridView1.Columns;
DataGridViewTextBoxColumn textColumn = new DataGridViewTextBoxColumn();

textColumn.DataPropertyName = "Designation";
textColumn.Name = "Designation";
textColumn.HeaderText = "Designation";
textColumn.ValueType = typeof(char);
textColumn.Width = 180;
columns.Add(textColumn);

textColumn = new DataGridViewTextBoxColumn();
textColumn.DataPropertyName = "Quantite";
textColumn.Name = "Quantite";
textColumn.HeaderText = "Quantite";
textColumn.ValueType = typeof(decimal);
textColumn.ReadOnly = true;
textColumn.DefaultCellStyle.Format = "N2";
textColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
columns.Add(textColumn);

textColumn = new DataGridViewTextBoxColumn();
textColumn.DataPropertyName = "Prix Unitaire";
textColumn.Name = "Prix Unitaire";
textColumn.HeaderText = "Prix Unitaire (CFA.)";
textColumn.ValueType = typeof(decimal);
textColumn.ReadOnly = true;
textColumn.DefaultCellStyle.Format = "N2";
textColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
columns.Add(textColumn);

textColumn = new DataGridViewTextBoxColumn();
textColumn.DataPropertyName = "Montant";
textColumn.Name = "Montant";
textColumn.HeaderText = "Montant (CFA.)";
textColumn.ValueType = typeof(decimal);
textColumn.ReadOnly = true;
textColumn.DefaultCellStyle.Format = "N2";
textColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
columns.Add(textColumn);

Padding padding = dataGridView1.DefaultCellStyle.Padding;
padding.Right = 10;
dataGridView1.DefaultCellStyle.Padding = padding;
}

private class Item
{
public string Description;
public int Weight;
public int Value;
public double Quantity;

public Item(string description, int weight, int value, double quantity)
{
Description = description;
Weight = weight;
Value = value;
Quantity = quantity;
}
}

private class ItemCollection
{
public Dictionary<string, int> Contents = new Dictionary<string, int>();
public int TotalValue;
public int TotalWeight;

public void AddItem(Item item, int quantity)
{
var a = ";";

if (Contents.ContainsKey(item.Description))
{
Contents[item.Description] += quantity;
Contents[item.Description] += item.Weight;
}
else
{
Contents[item.Description] = item.Weight;
}

Contents[item.Description] = Convert.ToInt32(string.Format("{02}{1}",
item.Weight, quantity));

TotalValue += quantity * item.Value;
TotalWeight += quantity * item.Weight;
}

public ItemCollection Copy()
{
var ic = new ItemCollection();
ic.Contents = new Dictionary<string, int>(this.Contents);
ic.TotalValue = this.TotalValue;
ic.TotalWeight = this.TotalWeight;
return ic;
}
}

private void button1_Click(object sender, EventArgs e)
{
ProcessRequest(dataGridView1);
}

private void button2_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
string nomFichier = openFileDialog1.FileName;

//string nomFichier = My.Computer.FileSystem.CurrentDirectory + "\\ex47.xlsx";
DataSet dat = new DataSet();

//déclaration et utilisation d'un OLeDBConnection
using (OleDbConnection Conn =
new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;"
+ " Data Source='" + nomFichier + "';"
+ " Extended Properties=\"Excel 8.0;;HDR=YES\""))
{
Conn.Open();

//déclaration du DataAdapter
//notre requête sélectionne toute les cellule de la Feuil1
using (OleDbDataAdapter Adap = new OleDbDataAdapter(
"select * from [Feuil1$]", Conn))
{
Adap.TableMappings.Add("Table", "TestTable");
//Chargement du Dataset
Adap.Fill(dat);
//On charge les données sur le DGV
dataGridView2.DataSource = dat.Tables[0];
}

//libèrer les ressources
Conn.Close();
}
}
}



What do you think ? Here is also a link for my excel file ibb.co/jsXaEo

Continue reading...