Switch between two DataReaders

Question

I have the following code:

private void supplBox_SelectedIndexChanged(object sender, EventArgs e)
{
   if (sqlcon.State == ConnectionState.Closed)
   {
      sqlcon.Open();
   }
   cmd = new SqlCommand("Select * from Supplier_info where Supplier_name = '" + supplBox.Text + "'", sqlcon);
   cmd.ExecuteNonQuery();
   SqlDataReader reader = cmd.ExecuteReader();
   while (reader.Read())
   {
      suppNum.Text = Convert.ToString(reader.GetInt32(0));
   }
   reader.Close();
   sqlcon.Close();
   barCode1.Focus();
}
private void suppNum_KeyUp(object sender, KeyEventArgs e)
{
   if (sqlcon.State == ConnectionState.Closed)
   {
      sqlcon.Open();
   }
   cmd = new SqlCommand("Select * from Supplier_info where Supplier_id = '" + suppNum.Text + "'", sqlcon);
   cmd.ExecuteNonQuery();
   SqlDataReader reader = cmd.ExecuteReader();
   while (reader.Read())
   {
      supplBox.Text = reader.GetString(1);
   }
   reader.Close();
   sqlcon.Close();
   barCode1.Focus();
}

When I switch between the supplBox and suppNum I get this error:

There is already an open DataReader associated with this Command which must be closed first.

If I add MultipleActiveResultSets=true to my connection I get this error:

Invalid attempt to call Read when reader is closed.

Help please

Connections are cheap. Create them when you need them and close/dispose once you have what you need. Do not share connections across multiple methods/calls, it is not worth the hassle. — rene, May 01 '18 at 10:19
using (SqlConnection sqlcon= new SqlConnection(connectionString)) { // your code} — Albert Einstein, May 01 '18 at 10:20
Your code is vulnerable to SQL injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using ADO.NET. **Never** insert unsanitised data directly into your SQL. — ADyson, May 01 '18 at 10:24
thank you all but i need to correct my code so can anyone write the correct code so i can easily catch my fault? — Point_Systems, May 01 '18 at 10:31
YES, i have tried the @5740382 code and it worked perfect, thank you so much — Point_Systems, May 01 '18 at 11:21

Switch between two DataReaders

0 Answers0