how to retrieve value in datagridview by autocomplete text

Atul Rokade

Iam creating one application where medicine_name is autocomplete text, base on that selection i want to populate Expiry_Date but whenever i run the code its given me Index was outside the bounds of the array exception, retrieve code i written on dataGridView1_CellEndEdit event i dont know where is im wrong here im pasting my code and screen shots of application and table structure

private void dataGridView1_CellEndEdit_1(object sender, DataGridViewCellEventArgs e)

{

string connectionString = null;

connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

con.ConnectionString = connectionString;

string medicinename = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["Medicine_name"].Value);

DateTime Expiry_Date = Convert.ToDateTime (dataGridView1.Rows[e.RowIndex].Cells["Expiry_Date"].Value);

con.Open();

cmd = new OleDbCommand("select Expiry_Date from Medicine_Available_Detail where Medicine_Name='" + medicinename + "'", con);

OleDbDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

Expiry_Date = Convert.ToDateTime (dr.GetValue(5));// exeception coming on this line

}

con.Close();

}

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Madhanmohan Devarajan10y ago
Accepted answer
You need to assign the value to the current row in the datagrid view in the cell for expiry date. In your code you are missing that one. Just you are storing the value to the variable Expiry_Date.
while (dr.Read())
{
Expiry_Date = Convert.ToDateTime(dr["Expiry_Date"]);
}
dataGridView1.Rows[e.RowIndex].Cells["Expiry_Date"].Value =Expiry_Date; //Include this line, here is the place where you are going to assign the value to the cell "Expiry_Date" in the DataGridView1
con.Close();
Atul Rokade10y ago
@Madhanmohan sir : thank you very very much sir its working as expected
string medicinename = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["Medicine_name"].Value);
DateTime Expiry_Date=new DateTime ();
con.Open();
cmd = new OleDbCommand("select Expiry_Date as Expiry_Date from Medicine_Available_Detail where Medicine_Name=@Medicine_Name",con);
cmd.Parameters.AddWithValue("@Medicine_Name",medicinename);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Expiry_Date = Convert.ToDateTime(dr["Expiry_Date"]);

}
dataGridView1.Rows[e.RowIndex].Cells["Expiry_Date"].Value = Expiry_Date;
this line make difference thanx a lot sir :)
con.Close();
Regards
Atul :)
Madhanmohan Devarajan10y ago
Please check dr.GetValue(5) whether it is showing string value while debugging. If it shows as object then use dr.GetValue(5).ToString(). Because the syntax is DateTime.TryParse(string, out DateTime);
DateTime.TryParse(dr.GetValue(5), out Expiry_Date);// error coming on this line
Reason for the error may be dr.GetValue(5) is object not a value.
Atul Rokade10y ago
Hi @madhanmohan its give me error
Error 13 The best overloaded method match for 'System.DateTime.TryParse(string, out System.DateTime)' has some invalid arguments
Error 14 Argument '1': cannot convert from 'object' to 'string'
string medicinename = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["Medicine_name"].Value);
DateTime Expiry_Date=new DateTime ();
con.Open();
cmd = new OleDbCommand("select Expiry_Date as Expiry_Date from Medicine_Available_Detail where Medicine_Name=@Medicine_Name",con);
cmd.Parameters.AddWithValue("@Medicine_Name",medicinename);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DateTime.TryParse(dr.GetValue(5), out Expiry_Date);// error coming on this line
}
con.Close();
Madhanmohan Devarajan10y ago
One thing i have missed in the earlier post is to comment theConvert.ToDateTime (dataGridView1.Rows[e.RowIndex].Cells["Expiry_Date"].Value); but still you need the declaration forExpiry_Date as shown below
Solution:
private void dataGridView1_CellEndEdit_1(object sender, DataGridViewCellEventArgs e)
{
string connectionString = null;
connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
con.ConnectionString = connectionString;
string medicinename = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["Medicine_name"].Value);
DateTime Expiry_Date = new DateTime();
// Convert.ToDateTime (dataGridView1.Rows[e.RowIndex].Cells["Expiry_Date"].Value); hereCells["Expiry_Date"].Value will be null
con.Open();
cmd = new OleDbCommand("select Expiry_Date from Medicine_Available_Detail where Medicine_Name='" + medicinename + "'", con);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
// Below line is the actual place where you are trying to get the Expiry_Date from DB thru SQL Query
//Expiry_Date = Convert.ToDateTime (dr.GetValue(5));// exeception coming on this line
DateTime.TryParse(dr.GetValue(5), out Expiry_Date); // try this
con.Close();
}
Atul Rokade10y ago
i writing code on cellEndEdit event that's why proper output is not coming? and i modified a code but still output is not coming
string medicinename = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["Medicine_name"].Value);
DateTime Expiry_Date = Convert.ToDateTime(dataGridView1.Rows[e.RowIndex].Cells["Expiry_Date"].Value);
con.Open();
cmd = new OleDbCommand("select Expiry_Date as Expiry_Date from Medicine_Available_Detail where Medicine_Name=@Medicine_Name",con);
cmd.Parameters.AddWithValue("@Medicine_Name",medicinename);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Expiry_Date = Convert.ToDateTime(dr["Expiry_Date"]);
}
con.Close();
Atul Rokade10y ago
thnx @MadhanMohan for reply but then how could i save retrieve date value? in datagridview if i comment that line
Madhanmohan Devarajan10y ago
Atul,
In your code Medicine_Name is auto complete and you are storing the value in "String medicinename", but in the next line you are trying to convert the "dataGridView1.Rows[e.RowIndex].Cells["Expiry_Date"].Value" to DateTime and that field is not auto complete.
string medicinename = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["Medicine_name"].Value);
DateTime Expiry_Date = Convert.ToDateTime (dataGridView1.Rows[e.RowIndex].Cells["Expiry_Date"].Value);
The actual value for the selected medicine is got after executing the SQL query.
cmd = new OleDbCommand("select Expiry_Date from Medicine_Available_Detail where Medicine_Name='" + medicinename + "'", con);
OleDbDataReader dr = cmd.ExecuteReader();
Solution:
comment the line
//DateTime Expiry_Date = Convert.ToDateTime (dataGridView1.Rows[e.RowIndex].Cells["Expiry_Date"].Value);