F
FriQenstein
Guest
Greetings,
First off, I hope the title is detailed enough for this question.
What I am attempting to do is:
1. Generate a datagridview from an SQL query
2. User clicks on a cell in the datagridview to select the row
3. Certain values from that row get put into an array or list (i.e. Qty, SN, PN, etc.)
4. Reference those stored values for export to existing Excel sheet
So far, I have the query populating the datagridview with no issues. I have implemented a CellContentClick event that will pull the values from that specific row. I just need to find the best method of storing those certain values so that they can later be added to the Excel sheet.
As an example I first started off with the following to generate the datagridview:
private void button1_Click(object sender, EventArgs e) // filter button
{
string query1 = @"SELECT tblClientInventory.ClientItemID, tblClientsC.ClientID, tblClientsC.Company, tblClientInventory.Item, tblClientInventory.Qty, tblModelC.Model, tblClientInventory.SerialNumber, tblClientInventory.PartNumber, tblStatusC.Status, tblClientInventory.Notes
FROM tblClientsC INNER JOIN (tblStatusC INNER JOIN (tblModelC INNER JOIN tblClientInventory ON tblModelC.ModelID = tblClientInventory.Model) ON tblStatusC.StatusID = tblClientInventory.Status) ON tblClientsC.ClientID = tblClientInventory.ClientName
WHERE (((tblClientInventory.Qty) > 0))
AND (((tblClientsC.Company)= @clientName) OR (@clientName = '') OR (@clientName IS NULL))
AND (((tblModelC.Model) = @modelType) OR (@modelType = '') OR (@modelType IS NULL))
ORDER BY tblClientsC.Company, tblModelC.Model, tblClientInventory.SerialNumber, tblStatusC.Status;";
try // query the inventory table and dump it to the datagridview widget
{
OleDbCommand command = new OleDbCommand(query1, connection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
DataTable dtRecord = new DataTable();
command.Parameters.AddWithValue("@clientName", SqlDbType.VarChar);
command.Parameters["@clientName"].Value = cmb_CompanyName.Text;
command.Parameters.AddWithValue("@modelType", SqlDbType.VarChar);
command.Parameters["@modelType"].Value = cmb_ModelType.Text;
connection.Open();
dataAdapter.Fill(dtRecord);
dataGridView1.DataSource = dtRecord;
dataGridView1.RowHeadersVisible = false; // hide the very first left-hand blank column
// center the column headers for Qty, S/N, P/N, Model & Status & Notes
dataGridView1.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[0].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[1].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[2].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[3].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[4].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[5].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[5].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[6].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[6].SortMode = DataGridViewColumnSortMode.NotSortable;
// center the data contents of Qty, S/N, P/N, Model & Status
dataGridView1.Columns["Qty"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns["Model"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns["SerialNumber"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns["PartNumber"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns["Status"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("ERROR " + ex);
}
}
Then I added the event for clicking on the cell/row:
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
var1 = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ClientItemID"].Value.ToString();
Console.WriteLine("ItemID: " + var1);
var2 = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["Company"].Value.ToString();
Console.WriteLine("Client: " + var2);
var3 = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["Qty"].Value.ToString();
Console.WriteLine("Qty: " + var3);
PN = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["PartNumber"].Value.ToString();
Console.WriteLine("PN: " + PN);
var4 = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["Status"].Value.ToString();
Console.WriteLine("Status: " + var4);
notes = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["Notes"].Value.ToString();
SN = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["SerialNumber"].Value.ToString();
Console.WriteLine("SN: " + SN);
lbox_SerialNumbers.Items.Add(SN);
}
Originally, I just added the serial numbers to a listbox so the user could see that it was selected and added to the list.
I then take the serial number items from the listbox and populate a list:
foreach (var item in lbox_SerialNumbers.Items)
{
list.Add(item.ToString());
}
And then throw the SN values out to the existing excel sheet with the following, which populates a specific set of cells in the existing excel sheet:
string cellName;
int counter = 14;
foreach (var sn in list)
{
cellName = "D" + counter.ToString();
var range = sheet.get_Range(cellName, cellName);
range.Value2 = sn.ToString();
++counter;
}
And this is where I am currently at. When the user selects the row, I need to get the Qty, SN, & PN values from the datagridview so that they can be sent to the excel sheet like I did with the SNs already.
I am having a hard time figuring out the best method for this and I cannot utilize the same procedure I implemented for the SNs because it just does not seem to work.
Also, I have added a step where, say the user accidentally selects the wrong row and wants to remove an item from the list; I have made it possible for the user to select that item from the listbox which will remove it from the list with:
lbox_SerialNumbers.Items.Remove(lbox_SerialNumbers.SelectedItem);
The problem here, which ties into the first part of this post, is when a user deselects an item, I need it to also remove the corresponding values for Qty & PN.
I have attempted sending the data from the datagridview to an array and/or a list but doing it via the CellContentClick event does not seem to generate the results I am looking for.
Any help would be greatly appreciated.
Regards.
Continue reading...
First off, I hope the title is detailed enough for this question.
What I am attempting to do is:
1. Generate a datagridview from an SQL query
2. User clicks on a cell in the datagridview to select the row
3. Certain values from that row get put into an array or list (i.e. Qty, SN, PN, etc.)
4. Reference those stored values for export to existing Excel sheet
So far, I have the query populating the datagridview with no issues. I have implemented a CellContentClick event that will pull the values from that specific row. I just need to find the best method of storing those certain values so that they can later be added to the Excel sheet.
As an example I first started off with the following to generate the datagridview:
private void button1_Click(object sender, EventArgs e) // filter button
{
string query1 = @"SELECT tblClientInventory.ClientItemID, tblClientsC.ClientID, tblClientsC.Company, tblClientInventory.Item, tblClientInventory.Qty, tblModelC.Model, tblClientInventory.SerialNumber, tblClientInventory.PartNumber, tblStatusC.Status, tblClientInventory.Notes
FROM tblClientsC INNER JOIN (tblStatusC INNER JOIN (tblModelC INNER JOIN tblClientInventory ON tblModelC.ModelID = tblClientInventory.Model) ON tblStatusC.StatusID = tblClientInventory.Status) ON tblClientsC.ClientID = tblClientInventory.ClientName
WHERE (((tblClientInventory.Qty) > 0))
AND (((tblClientsC.Company)= @clientName) OR (@clientName = '') OR (@clientName IS NULL))
AND (((tblModelC.Model) = @modelType) OR (@modelType = '') OR (@modelType IS NULL))
ORDER BY tblClientsC.Company, tblModelC.Model, tblClientInventory.SerialNumber, tblStatusC.Status;";
try // query the inventory table and dump it to the datagridview widget
{
OleDbCommand command = new OleDbCommand(query1, connection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
DataTable dtRecord = new DataTable();
command.Parameters.AddWithValue("@clientName", SqlDbType.VarChar);
command.Parameters["@clientName"].Value = cmb_CompanyName.Text;
command.Parameters.AddWithValue("@modelType", SqlDbType.VarChar);
command.Parameters["@modelType"].Value = cmb_ModelType.Text;
connection.Open();
dataAdapter.Fill(dtRecord);
dataGridView1.DataSource = dtRecord;
dataGridView1.RowHeadersVisible = false; // hide the very first left-hand blank column
// center the column headers for Qty, S/N, P/N, Model & Status & Notes
dataGridView1.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[0].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[1].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[2].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[3].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[4].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[5].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[5].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[6].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns[6].SortMode = DataGridViewColumnSortMode.NotSortable;
// center the data contents of Qty, S/N, P/N, Model & Status
dataGridView1.Columns["Qty"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns["Model"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns["SerialNumber"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns["PartNumber"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.Columns["Status"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("ERROR " + ex);
}
}
Then I added the event for clicking on the cell/row:
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
var1 = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ClientItemID"].Value.ToString();
Console.WriteLine("ItemID: " + var1);
var2 = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["Company"].Value.ToString();
Console.WriteLine("Client: " + var2);
var3 = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["Qty"].Value.ToString();
Console.WriteLine("Qty: " + var3);
PN = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["PartNumber"].Value.ToString();
Console.WriteLine("PN: " + PN);
var4 = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["Status"].Value.ToString();
Console.WriteLine("Status: " + var4);
notes = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["Notes"].Value.ToString();
SN = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["SerialNumber"].Value.ToString();
Console.WriteLine("SN: " + SN);
lbox_SerialNumbers.Items.Add(SN);
}
Originally, I just added the serial numbers to a listbox so the user could see that it was selected and added to the list.
I then take the serial number items from the listbox and populate a list:
foreach (var item in lbox_SerialNumbers.Items)
{
list.Add(item.ToString());
}
And then throw the SN values out to the existing excel sheet with the following, which populates a specific set of cells in the existing excel sheet:
string cellName;
int counter = 14;
foreach (var sn in list)
{
cellName = "D" + counter.ToString();
var range = sheet.get_Range(cellName, cellName);
range.Value2 = sn.ToString();
++counter;
}
And this is where I am currently at. When the user selects the row, I need to get the Qty, SN, & PN values from the datagridview so that they can be sent to the excel sheet like I did with the SNs already.
I am having a hard time figuring out the best method for this and I cannot utilize the same procedure I implemented for the SNs because it just does not seem to work.
Also, I have added a step where, say the user accidentally selects the wrong row and wants to remove an item from the list; I have made it possible for the user to select that item from the listbox which will remove it from the list with:
lbox_SerialNumbers.Items.Remove(lbox_SerialNumbers.SelectedItem);
The problem here, which ties into the first part of this post, is when a user deselects an item, I need it to also remove the corresponding values for Qty & PN.
I have attempted sending the data from the datagridview to an array and/or a list but doing it via the CellContentClick event does not seem to generate the results I am looking for.
Any help would be greatly appreciated.
Regards.
Continue reading...