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);
}
}
}
}