Auto Complete Datagridview using sql server.

Question

Hi ! 

 

Good day everyone,

 

It is possible to make my datagridview cell auto complete drop the data read coming from the database sql server? 

 

How to code this one?

 

This is my code I used to insert the data to sql server... I want every cell autodrop the data which save from database sql server every time I input something in a cell.

 

Hope anyone can help me.

 

Thank you! 

public void LoadDgMaintenance()
{
try
{
dataGridView1.ColumnCount = 14;
dataGridView1.Columns[0].Name = "Particular";
dataGridView1.Columns[0].Width = 180;
dataGridView1.Columns[1].Name = "QTY";
dataGridView1.Columns[1].Width = 180;
dataGridView1.Columns[2].Name = "Unit";
dataGridView1.Columns[2].Width = 180;
dataGridView1.Columns[3].Name = "Code";
dataGridView1.Columns[3].Width = 180;
dataGridView1.Columns[4].Name = "Type";
dataGridView1.Columns[4].Width = 180;
dataGridView1.Columns[5].Name = "Vendor";
dataGridView1.Columns[5].Width = 180;
dataGridView1.Columns[6].Name = "Vendor Add";
dataGridView1.Columns[6].Width = 180;
dataGridView1.Columns[7].Name = "Vendor Tel. No.";
dataGridView1.Columns[7].Width = 180;
dataGridView1.Columns[8].Name = "Serial No.";
dataGridView1.Columns[8].Width = 180;
dataGridView1.Columns[9].Name = "Unit Price";
dataGridView1.Columns[9].Width = 180;
dataGridView1.Columns[10].Name = "Amount";
dataGridView1.Columns[10].Width = 180;
dataGridView1.Columns[11].Name = "Remarks";
dataGridView1.Columns[11].Width = 180;
dataGridView1.Columns[12].Name = "Req. Branch";
dataGridView1.Columns[12].Width = 180;
////dataGridView1.Columns[13].Name = "Date Added";
////dataGridView1.Columns[13].Width = 180;
////dataGridView1.Columns[14].Name = "Date Purchase";
////dataGridView1.Columns[14].Width = 180;
dataGridView1.Columns[13].Name = "Other Notes";
dataGridView1.Columns[13].Width = 180;
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
using (connect = new SqlConnection(ItemformConn.connection))
{
connect.Close();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connect;
command.CommandText = @"INSERT INTO tblReceivingItem([ItemInvID],[ItemRefNo],[ItemPONo],[ItemParticulars],[ItemQTY],[ItemUnit],[ItemCode],[ItemType],[ItemVendor],[ItemVendorAdd],[ItemVendorTelNo],[ItemSerialNo],[ItemUnitPrice],[ItemAmount],[ItemRemarks],[ItemBranchReq],[ItemDateRecieved],[ItemDatePurchased],[ItemOthers]) VALUES(@ItemInvID,@ItemRefNo,@ItemPONo,@ItemParticulars,@ItemQTY,@ItemUnit,@ItemCode,@ItemType,@ItemVendor,@ItemVendorAdd,@ItemVendorTelNo,@ItemSerialNo,@ItemUnitPrice,@ItemAmount,@ItemRemarks,@ItemBranchReq,@ItemDateRecieved,@ItemDatePurchased,@ItemOthers)";
try
{
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (!row.IsNewRow)
{
//=====================================================Check duplication============================
SqlCommand cm;
SqlDataReader dr;
string sql = @"Select * from tblReceivingItem where ItemInvID like '" + IdHolder + "'";
cm = new SqlCommand(sql, connect);
connect.Open();
dr = cm.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
generateItemIDRevDG();
dr.Close();
connect.Close();
}
dr.Close();
connect.Close();
//===================================================End Checking duplicaiton=================
command.Parameters.Clear();
SqlParameter unitsParamID = command.Parameters.AddWithValue("@ItemInvID", IdHolder);
if(IdHolder==null){ unitsParamID.Value = DBNull.Value; }
SqlParameter unitsParamRef = command.Parameters.AddWithValue("@ItemRefNo", lblRefNo.Text);
if (lblRefNo.Text == null) { unitsParamRef.Value = DBNull.Value; }
SqlParameter unitsParamPO = command.Parameters.AddWithValue("@ItemPONo", txtPONo.Text);
if (txtPONo.Text == null) { unitsParamPO.Value = DBNull.Value; }
SqlParameter unitsParamRec = command.Parameters.AddWithValue("@ItemDateRecieved", txtDatePurch.Text);
if (txtDatePurch.Text== null) { unitsParamRec.Value = DBNull.Value; }
SqlParameter unitsParamPur = command.Parameters.AddWithValue("@ItemDatePurchased", lblDateOn.Text);
if (lblDateOn .Text== null) { unitsParamPur.Value = DBNull.Value; }
SqlParameter unitsParamPar = command.Parameters.AddWithValue("@ItemParticulars", row.Cells[0].Value);
if (row.Cells[0].Value == null) { unitsParamPar.Value = DBNull.Value; }
SqlParameter unitsParamQTY = command.Parameters.AddWithValue("@ItemQTY", row.Cells[1].Value);
if (row.Cells[1].Value == null) { unitsParamQTY.Value = DBNull.Value; }
SqlParameter unitsParamUnit = command.Parameters.AddWithValue("@ItemUnit", row.Cells[2].Value);
if (row.Cells[2].Value == null) { unitsParamUnit.Value = DBNull.Value; }
SqlParameter unitsParamCode = command.Parameters.AddWithValue("@ItemCode", row.Cells[3].Value);
if (row.Cells[3].Value == null) { unitsParamCode.Value = DBNull.Value; }
SqlParameter unitsParamtype = command.Parameters.AddWithValue("@ItemType", row.Cells[4].Value);
if (row.Cells[4].Value == null) { unitsParamtype.Value = DBNull.Value; }
SqlParameter unitsParamven = command.Parameters.AddWithValue("@ItemVendor", row.Cells[5].Value);
if (row.Cells[5].Value == null) { unitsParamven.Value = DBNull.Value; }
SqlParameter unitsParamvenadd = command.Parameters.AddWithValue("@ItemVendorAdd", row.Cells[6].Value);
if (row.Cells[6].Value == null) { unitsParamvenadd.Value = DBNull.Value; }
SqlParameter unitsParamtelno = command.Parameters.AddWithValue("@ItemVendorTelNo", row.Cells[7].Value);
if (row.Cells[7].Value == null) { unitsParamtelno.Value = DBNull.Value; }
SqlParameter unitsParamSer = command.Parameters.AddWithValue("@ItemSerialNo", row.Cells[8].Value);
if (row.Cells[8].Value == null) { unitsParamSer.Value = DBNull.Value; }
SqlParameter unitsParamPrice = command.Parameters.AddWithValue("@ItemUnitPrice", row.Cells[9].Value);
if (row.Cells[9].Value == null) { unitsParamPrice.Value = DBNull.Value; }
SqlParameter unitsParamamount = command.Parameters.AddWithValue("@ItemAmount", row.Cells[10].Value);
if (row.Cells[10].Value == null) { unitsParamamount.Value = DBNull.Value; }
SqlParameter unitsParamRem = command.Parameters.AddWithValue("@ItemRemarks", row.Cells[11].Value);
if (row.Cells[11].Value == null) { unitsParamRem.Value = DBNull.Value; }
SqlParameter unitsParamReq = command.Parameters.AddWithValue("@ItemBranchReq", row.Cells[12].Value);
if (row.Cells[12].Value == null) { unitsParamReq.Value = DBNull.Value; }
SqlParameter unitsParamOther = command.Parameters.AddWithValue("@ItemOthers", row.Cells[13].Value);
if (row.Cells[13].Value == null) { unitsParamOther.Value = DBNull.Value; }
connect.Open();
command.ExecuteNonQuery();
connect.Close();
}
}
btnAddNew.Enabled = true;
btnAdd.Enabled = false;
btnUpdate.Enabled = false;
btnDelete.Enabled = false;
}
catch (Exception ex) { MessageBox.Show(ex.Message); return; }finally { MessageBox.Show("Successfull Added!"); dataGridView1.Rows.Clear(); dataGridView1.Refresh(); }
}
}