Error in DataReader/Command

  • 12 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
  • 12 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.


















  • 12 years ago
    Thanks! Your answer really help me!

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Never trust a programmer in a suit.” - Anonymous