How to retrieve value in datagridview by autocomplete text

Question

1.00/5 (1 vote)

See more:

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,im using ms access db i dont know where is im wrong here im pasting my code

table structure is :
Med_id AutoNumber
Medicine_Name Memo
Number_of_tab Memo
Drawer_No Memo
Dealer_Name Text
Availability Number
Expiry_Date Date/Time (format : shortDatetime)

datagridview structure
Quantity Medicine_Name(Its a autocomplete text base on this i want to show Expiry_Date),Medicine_Cost, Manufacturer_Name,Batch_No,Expiry_Date , Rupees

What I have tried:

C#

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


        }

Posted 13-Oct-16 6:19am

Atul Rokade

Updated 14-Oct-16 9:04am

Add a Solution

Comments

[no name] 13-Oct-16 12:30pm

Simple. You are getting the data for one column and trying to access the data in your data reader that doesn't exist (you only have 1 column of information not 5).

Atul Rokade 13-Oct-16 13:01pm

i made changes in this line Expiry_Date = Convert.ToDateTime(dr["Expiry_Date"]); but still value is not retrieving in database :( , pls tell me on which event should i put a code so i can retrieve a value

[no name] 13-Oct-16 13:19pm

The event you are using should work. Did you try steeping through your code and finding out what the values of the variables are? Start there.

Atul Rokade 13-Oct-16 13:53pm

i tried everything but failed

[no name] 13-Oct-16 15:01pm

Since none of know what that means, what do you expect us to tell you? We can't run or debug your code for you. It's up to you to learn how to run your code in the debugger and find the error.

Richard Deeming 13-Oct-16 12:46pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Atul Rokade 13-Oct-16 13:54pm

@Richard sir : can you give me solution on this pls

Richard Deeming 13-Oct-16 13:58pm

Simple - don't use string concatenation. Use a parameter instead:

cmd = new OleDbCommand("select Expiry_Date from Medicine_Available_Detail where Medicine_Name = @Medicine_Name", con);
cmd.Parameters.AddWithValue("@Medicine_Name", medicinename);

Atul Rokade 13-Oct-16 14:15pm

Sir i wrote code on datagridview cellEndEdit but still im not get a output, my query is medicine_name is autocomplete text in datagridview base on this Expiry_Date should be populate here is my modify code

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

Richard Deeming 13-Oct-16 14:19pm

If you're not getting any results returned, that means there are no matching records in your database.

Atul Rokade 13-Oct-16 14:24pm

Sir data is there i checked when i choose Medicine_Name : Xedo which having Expiry_Date 10/04/2018(dd/mm/yyyy)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Atul Rokade · Answer 1 · 2016-10-14T09:04:00

here is i got solution

C#

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

            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; 
            con.Close();