Solved

Multiple instances of executereader and an open connection

Posted on 2006-06-11
2
622 Views
Last Modified: 2013-12-25
Greetings Experts,

My question involves using an executeReader in a for/next loop.  My code: \

Dim SConn As SqlConnection = New SqlClient.SqlConnection(sConnectionString)
 SConn.Open()

 Dim cmd2 As New SqlCommand
 cmd2.Connection = SConn

  For Incr = 0 To Counter
        Str = "Select Officer from RescueCheckout Where RDate = '" & RDate & "' and RescueNumber = '" & Units(Incr) & "'"
        cmd2.CommandText = Str
        cmd2.Connection = SConn
         sqlreader = cmd2.ExecuteReader
         If sqlreader.HasRows  Then
                info(Jncr) = Units(Incr)
                Jncr = Jncr + 1
            End If
            SConn.Close()
 Next
 
It appears that I must close and then reopen a connection for each iteration of the for/next.  Is this correct?  I would think that I could use the same connection for each ExecuteReader.

Thansk

V Whitehead


   
   
0
Comment
Question by:VSWhite
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 3

Accepted Solution

by:
fullcontact earned 500 total points
ID: 16885340
You do not need to close your connection within to loop, but you need to close the reader. You can only have one reader open at a time, ie :-

Replace

SConn.Close

With

sqlreader.close
0
 
LVL 3

Expert Comment

by:fullcontact
ID: 16885397
Could you try and use a single SQL to do this ie not using a loop 0 to counter

Select RescueNumber from RescueCheckout Where RDate = '" & RDate & "' and RescueNumber between 0 AND " & Counter

 This would return all the rescuenumbers which exists between 0 and counter.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question