Using DataReader in VB.NET

Question

I got the error message There is an open data reader associated with this command which needs to be closed first by using the following code:

myCommand = New SqlCommand("SELECT BookCode FROM tblBook",myConnection)
 myReader = myCommand.ExceuteReader
 While myReader.Read
   If myReader(0).ToString <> txtBookCode.Text Then
      myCommand = New SqlCommand("INSERT INTO tblBook VALUES(@BookCode, @BookTitle)",myConnection)
      myCommand.Parameters.AddWithValue("@BookCode", txtBookCode.Text)
      myCommand.Parameters.AddWithValue("@BookTitle", txtBookTitle.Text)
      myCommand.ExecuteNonQuery()
   Else
      MsgBox("There is already a book name '"& txtTitle.Text "'. Please try another code.",vbOkOnly,"BookCode Exists")
   End If
 End While

Pleas help.

Ales Ruzicka · Accepted Answer · 2011-07-06 07:36:58Z

2

Don't reuse myCommand variable. Create new one.

myCommand should be disposed in the end too (as well as reader).

Real reason of exception is more likely that you're trying to run two command on one connection at the same time. First read all data you need from reader and THEN do all inserts. Not both at once (i assume you don't want to create two connections. That would suck)

answered Jul 6, 2011 at 7:36

Ales Ruzicka

2,8101 gold badge19 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

heximal · Accepted Answer · 2011-07-06 07:37:35Z

1

looks like you're trying to use one variable myCommand more than once - in the first line of code and within a WHILE loop. it's better to declare one more AdoCommand variable to use it in LOOP

answered Jul 6, 2011 at 7:37

heximal

10.5k5 gold badges49 silver badges73 bronze badges

Collectives™ on Stack Overflow

Using DataReader in VB.NET

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related