I am performing the same recordset multiple times, just passing
different parameters each time.  Is there a way to do this more
efficiently without having to close and re-open the connection and
reader?  I thought there might be a cleaner way, this code seems to
take "forever" to run.

'-------BEGINNING OF CODE EXAMPLE-------
Dim SBInvNum As String
Dim strConInv As String = "Provider=Microsoft.JET.OLEDB.4.0;Data
Source =" & "\\myserver\invwhs.mdb"
Dim strRecInv As String = "SELECT * FROM
[invwhs_detail_local_with_desc] WHERE [whs] = ? and [ITEM_NBR]
LIKE ?;"
Dim conInv As New System.Data.OleDb.OleDbConnection(strConInv)
Dim comInv As New System.Data.OleDb.OleDbCommand(strRecInv, conInv)
Dim recInv As System.Data.OleDb.OleDbDataReader
For x = 0 To UBound(PartsArray, 2)
    SBInvNum = GetInvNum(PartsArray(0, x), PartsArray(1, x),
PartsArray(2, x)) 'generate part number
    conInv.Open()

    'see if the part is available at all plants, if it isn't clear the
plant from the array.
    For j = 0 To i
        comInv.Parameters.AddWithValue("P1", GoodPlants(0, j))
        comInv.Parameters.AddWithValue("P2", SBInvNum & "%")
        recInv = comInv.ExecuteReader
        If Not recInv.Read() Then
            GoodPlants(0, j) = ""
            GoodPlants(1, j) = ""
            GoodPlants(2, j) = ""
            GoodPlants(3, j) = ""
        End If
        recInv.Close() 'IS THERE A BETTER WAY? INSTEAD OF CLOSING AND
RE-OPENING?
        comInv.Parameters.Clear()
    Next j
    conInv.Close()
Next x
'-------END OF CODE EXAMPLE-------

Hello Mel,

You could just close the reader (you need to pass it a special parameter 
so it won't close the connection while it's at it) and open a new reader 
on the same connection. You can use the same command as before if you want 
to.

By the way. OleDBCommand also implements IDisposable and should be disposed 
when you're done with it.

I prefer using the using clause in C#, there's probably a similar thing in 
VB.NET. The syntax in C# is:

using(OleDBConnection conInv = new OleDBConnection(...))
{

}

this makes sure that when the end } is passed, close will be called automatically. 
Even if an exception occurs. Makes your code a lot easier to read if you 
use it consistently.

Jesse


> I am performing the same recordset multiple times, just passing
> different parameters each time.  Is there a way to do this more
> efficiently without having to close and re-open the connection and
> reader?  I thought there might be a cleaner way, this code seems to
> take "forever" to run.
> 
> '-------BEGINNING OF CODE EXAMPLE-------
> Dim SBInvNum As String
> Dim strConInv As String = "Provider=Microsoft.JET.OLEDB.4.0;Data
> Source =" & "\\myserver\invwhs.mdb"
> Dim strRecInv As String = "SELECT * FROM
> [invwhs_detail_local_with_desc] WHERE [whs] = ? and [ITEM_NBR]
> LIKE ?;"
> Dim conInv As New System.Data.OleDb.OleDbConnection(strConInv)
> Dim comInv As New System.Data.OleDb.OleDbCommand(strRecInv, conInv)
> Dim recInv As System.Data.OleDb.OleDbDataReader
> For x = 0 To UBound(PartsArray, 2)
> SBInvNum = GetInvNum(PartsArray(0, x), PartsArray(1, x),
> PartsArray(2, x)) 'generate part number
> conInv.Open()
> 'see if the part is available at all plants, if it isn't clear the
> plant from the array.
> For j = 0 To i
> comInv.Parameters.AddWithValue("P1", GoodPlants(0, j))
> comInv.Parameters.AddWithValue("P2", SBInvNum & "%")
> recInv = comInv.ExecuteReader
> If Not recInv.Read() Then
> GoodPlants(0, j) = ""
> GoodPlants(1, j) = ""
> GoodPlants(2, j) = ""
> GoodPlants(3, j) = ""
> End If
> recInv.Close() 'IS THERE A BETTER WAY? INSTEAD OF CLOSING AND
> RE-OPENING?
> comInv.Parameters.Clear()
> Next j
> conInv.Close()
> Next x
> '-------END OF CODE EXAMPLE-------
>