Error in DataReader/Command

• • Reply

  14 years ago

  by estrela666

  Liliana

  Lisbon, Portugal

  Joined 14 years ago
  Hi guys, I'm new here! My name is Liliana, 23 and from Portugal. I'm a Software developer beginning in Vb.Net!
  
  What it's wrong in this code?
  ****************************
  Dim conn As New SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=pontos;User Id=liliana;Password=1234;")
  conn.Open()
  Dim cmd_cli As New SqlClient.SqlCommand("Select * from tlb_clientes", conn)
  Dim dr_cli As SqlClient.SqlDataReader
  cmd_cli.CommandType = CommandType.Text
  dr_cli = cmd_cli.ExecuteReader
  Dim dr_dep As SqlClient.SqlDataReader
  Do While dr_cli.Read
  Dim cmd_dep As New SqlClient.SqlCommand("Select * from tlb_dependentes WHERE num_str_cli_dep=" & dr_cli("num_str_cli") & ";", conn)
  cmd_dep.CommandType = CommandType.Text
  dr_dep = cmd_dep.ExecuteReader '*** THE ERROR IT'S HERE!!
  Do While dr_dep.Read
  Debug.Print((dr_cli("nome_str_dep")))
  Loop
  Debug.Print(dr_cli("nome_str_cli"))
  Debug.Print(dr_cli("telefone_str_cli"))
  Loop
  *****************************************
  The error is: "There is already an open DataReader associated with this Command which must be closed first."
  But I think that the DataReader that is open is dr_cli which is associated with command cmd_cli.
  
  Thanks
  Liliana
  
  Report abuse
• • Reply

  14 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago
  Easy:
  
  One connection object can serve to more commands
  One command can do only one operation at one time
  
  so:
  if you need to do two data operations (eg. do: read: write: loop) u need
  to have two commands, and for one of them (acc2 prev eg.) one reader,
  second command will be changing cmdText and executingNonQuery.
  
  if you want share one command between two reads, u must close first reader
  and then already u can replace command select query and create new reader
  from executequery. when forgot close reader and trying change commandtext
  it will throw some exceptions like yours one
  
  also remember when inserting by command and reading from same connection in loop
  for ms access what i knw, is best to close, open (restart) connection before read, to
  flush waiting changes on db, to be possible to read immediatelly.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  14 years ago

  by estrela666

  Liliana

  Lisbon, Portugal

  Joined 14 years ago
  Thanks! Your answer really help me!
  Report abuse