I am passing parameters to a function to select data from an SQL database.

I am looping through three arraylists to get data. My SQL statement:
        Dim strSQL As String = "SELECT Stand.StandID, Stand.Block, Purchase.MgtArea, " _
        & "Stand.TractNum, Purchase.TractName, BType.Btype, Strata.Stratum, " _
        & "Stand.Origin, Stand.Acres, Regime.SingleChop, Regime.SpotRake, Regime.Bed " _
        & "FROM (((Stand INNER JOIN Purchase ON Stand.TractNum = Purchase.PurchaseID) " _
        & "INNER JOIN BType ON Stand.BType = BType.Code) " _
        & "INNER JOIN Strata ON Stand.Stratum = Strata.Code) " _
        & "INNER JOIN Regime ON Stand.Regimeid = Regime.RegimeID " _
        & "WHERE Stand.TractNum = """ & selPurchase.Item(index) & """ AND BType.Btype = """ _
        & selBType.Item(bindex) & """ And Strata.Stratum = """ & selStratum.Item(sindex) & """"

This statement doesnt return any data though it doesnt produce an error message either.
I've checked with a query in the database and data is there. Also I've checked that data
is being passed.

The loop that is passing the data:
           For i = 0 To MMain.selPurchase.Count
                For b = 0 To MMain.selBType.Count
                    For s = 0 To selStratum.Count
                        objStands = getStands(i, b, s)
                        s = s + 1
                    Next s
                    b = b + 1
                Next b
                i = i + 1
            Next i

Also, is there a way to pass all of the array values at once and not loop?

Thanks,
dc

Your query has double quotes instead of single quotes. SQL Server takes single quotes for strings.

I've tried several variations of single quotes but they turn the sql
statement into a comment.

& "WHERE Stand.TractNum = """ & selPurchase.Item(index) & """ AND BType.Btype = """ _
& selBType.Item(bindex) & """ And Strata.Stratum = """ & selStratum.Item(sindex) & """"

Which of the quotes need to be single?

Thanks
dc925

& "WHERE Stand.TractNum = '" & selPurchase.Item(index) & "' AND BType.Btype = '" _
& selBType.Item(bindex) & "' And Strata.Stratum = '" & selStratum.Item(sindex) & "'"


The other thing you can do to is write the query with parameters. This simplifies the text of the SQL statement, and also provides some additional functionality with regards to the actual values used (automatic replacement of a single quote in a value to a pair of single quotes among other things).

& "WHERE Stand.TractNum = @tractnum AND BType.Btype = @btype And Strata.Stratum = @stratum"

sqlCommand.Parameters.Add("@tractnum", <sql datatype>).Value = selPurchase.Item(index)
sqlCommand.Parameters.Add("@btype", <sql datatype>).Value = selBType.Item(bindex)sqlCommand.Parameters.Add("@s tratum", <sql datatype>).Value = selStratum.Item(sindex)

Peter
------------------------------------------------------
Work smarter, not harder.

Thanks. The quotes work fine. And I like the parameters.

dc:D