We had looked at GataGridView combobox column earleir.We saw how to fill it with data from a simple array.Today we see how to populate our combobox column in datagridview with data from the database.We are using MySQL database in this case.
using MySql.Data.MySqlClient;
using System;
using System.Collections;
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;
namespace ComboColumn_DataBase
{
public partial class Form1 : Form
{
//INITIALIZATIONS
static string conString = "Server=localhost;Database=playersdb;Uid=root;Pwd=;";
MySqlConnection con = new MySqlConnection(conString);
DataTable dt = new DataTable();
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.DataSource = loadData().Tables[0];
addCombo();
}
private DataSet loadData()
{
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand cmd;
DataSet ds = new DataSet();
//SELECT STMTS
string playerssql = "SELECT * FROM playerstb";
string coachessql = "SELECT * FROM coachestb";
//PLAYER TABLE
cmd = new MySqlCommand(playerssql,con);
adapter.SelectCommand = cmd;
adapter.Fill(ds, "Players");
//COACHES TABLE
adapter.SelectCommand.CommandText = coachessql;
adapter.Fill(ds, "Coaches");
dt = ds.Tables[1];
return ds;
}
//COMBO COLUMN
public void addCombo()
{
//ADD COLUMNS
DataGridViewComboBoxColumn combo = new DataGridViewComboBoxColumn();
combo.HeaderText = "Head Coach";
combo.Name = "combo";
ArrayList row = new ArrayList();
//FILL ARRAYLIST FROM DATATABLE
foreach(DataRow dr in dt.Rows)
{
row.Add(dr["Name"].ToString());
}
//ADD TO COMBO
combo.Items.AddRange(row.ToArray());
//ADD THE COMBO TO DATAGRIDVIEW
dataGridView1.Columns.Add(combo);
}
}
}
Cheers.
C# “How to get a list of match letters words through suggest & append while entering some letters in the datagridview cell?
My code is this,
private void dataGridView1_EditingControlShowing_1(object sender, DataGridViewEditingControlShowingEventArgs e)
{
if (dataGridView1.CurrentCell.ColumnIndex == 0)
{
int currentRow = dataGridView1.CurrentRow.Index;
TextBox tb1 = e.Control as TextBox;
OleDbCommand cmd1 = new OleDbCommand(“SELECT [AccName] FROM AccLevel3 WHERE AccName LIKE ‘” + tb1.Text + “%'”, conn);
conn.Open();
OleDbDataReader reader = cmd1.ExecuteReader();
AutoCompleteStringCollection AccountCol = new AutoCompleteStringCollection();
if (reader.HasRows == true)
{
while (reader.Read())
{
AccountCol.Add(reader[“AccName”].ToString());
}
}
else
{
MessageBox.Show(“Data not Found”);
}
conn.Close();
reader.Close();
if (tb1 != null)
{
tb1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
tb1.AutoCompleteCustomSource = AccountCol;
tb1.AutoCompleteSource = AutoCompleteSource.CustomSource;
}
e.Control.KeyPress -= new KeyPressEventHandler(Column1_KeyPress);
if (dataGridView1.CurrentCell.ColumnIndex == 2) //Desired Column
{
TextBox tb = e.Control as TextBox;
if (tb != null)
{
tb.KeyPress += new KeyPressEventHandler(Column1_KeyPress);
}
}
}
}