Problem with arrays in a sql statement 1

[itmasterw]

Hi,
I tring to use an array the different catigories listed below. I got htis code work if I do it without loops, but that makes it really long, because I have a number of these. However, the problem that I am having is that it is not excepting the array in the sql statement. I know this because, in my test for the Rs state it comes back with a zero. If anyone know what I can do to make this work I would appreaciate it.
Thank you

My code:

Private Sub LoansGreaterThanZero2()
Dim Ary As Integer
Dim n As Integer
Dim L(9) As String

L(0) = "CURRENT"
L(1) = "30 DAYS"
L(2) = "60 DAYS"
L(3) = "90 DAYS"
L(4) = "120 DAYS"
L(5) = "BANKRUPTCY"
L(6) = "PRE FORECLOSURE"
L(7) = "POST FORECLOSURE"
L(8) = "Total Of FIRST PRINCIPAL BALANCE"
n = 0
For Ary = 0 To 8

On Error Resume Next 'Handles the case where the Field does not exist
Err.Number = 0
Rs.Open ("Select '" & [L] & "' FROM [tblPB_Greater_Than_0_Crosstab_Counts]"), _
Cn, adOpenKeyset, adLockOptimistic
Err.Number = 0

If Rs.State <> 1 Then GoTo NxtK

If Rs!L = 0 Then
GoTo NxtK
Else
x = 66
y = 9

Do While Not Rs.EOF
Set r1 = xl1.Range((Chr$(x) & Val))
r1 = Rs!L
y = y + 1
Rs.MoveNext
Loop
End If
Rs.Close
NxtK:
n = n + 1
Next Ary
End Sub

ITM

 

[DrJavaJoe]

Lots of problems here, first off do not use goto statements make it work without them. Second do not use single quotes around the select items. Third why not Open the recordset once? Select Current, [30 Days], [60 Days], etc...

see thread222-839882 for an extreme case on why goto statements can be bad.



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[Golom]

It looks like your code may be generating an invalid syntax error. The notation

... '" & [L] & "' ...
will probably fail because the square brackets are not contained as text nor are they a valid specification of the variable L. Also the single quotes around the name will cause SQL to create a field with the value '[Current]' rather than retrieving the field with the field name [Current].

Set a breakpoint after the RS.Open statement and check the error code (Err.Number). It's probable that the Open failed due to a syntax error so the recordset didn't get opened (i.e. Rs.State = 0)

 

[itmasterw]

To Golom:You are right, as you can see in my code, I have an Rs.State stsatement which is telling me tha tthe Rs is not opening. But what can Ido to make this work. I am trying to use an array to
Also, to DrJavaJoe, you are right this is not the best code but it works and I am trying to stream line this by not listing multiple blocks of code with [30 Days], [60 Days], etc. Intead I am trying to do this with an loop and arays; currently hte code is about 150 pages.
So if anyone has any ideas how I make this work I would appreaciate it.
Thank You





ITM

 

[fredericofonseca]

Use a stored procedure (or query if using Access) to hold the SQL statements.

Also your code is overlapping the information.

x = 66
y = 9

Do While Not Rs.EOF
Set r1 = xl1.Range((Chr$(x) & Val))
r1 = Rs!L
y = y + 1
Rs.MoveNext

for each SQL you do you are always using the same starting cell, with means that information from the previouls SQL can be totally or partially overriden.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[itmasterw]

TO fredericofonseca: Thankl you I ddid not see that, but the code worked if I did not use the Loop and the array There must be some way to get the array in there to work.
Isin there?

ITM

 

[Golom]

After a bit of cleanup ... try this

Private Sub LoansGreaterThanZero2()
    Dim Ary     As Integer
    Dim L(9)    As String
    Dim rs      As ADODB.Recordset

    L(0) = "CURRENT"
    L(1) = "30 DAYS"
    L(2) = "60 DAYS"
    L(3) = "90 DAYS"
    L(4) = "120 DAYS"
    L(5) = "BANKRUPTCY"
    L(6) = "PRE FORECLOSURE"
    L(7) = "POST FORECLOSURE"
    L(8) = "Total Of FIRST PRINCIPAL BALANCE"

    On Error Resume Next

    For Ary = 0 To UBound(L)

        Err.Clear
        Set rs = New ADODB.Recordset
        rs.CursorLocation = adUseClient
        rs.Open "Select [" & L(Ary) & "] FROM [tblPB_Greater_Than_0_Crosstab_Counts];", _
                cn, adOpenKeyset, adLockOptimistic


        If Err.Number = 0 Then
        
            If Not rs.EOF Then

                If rs.Fields(L(Ary)).Value <> 0 Then
                    x = 66
                    y = 9

                    Do Until rs.EOF
                        Set r1 = xl1.Range((Chr$(x) & Val(y)))
                        r1 = rs.Fields(L(Ary)).Value
                        y = y + 1
                        rs.MoveNext
                    Loop

                End If
                
            End If
            
        End If
        
        Set rs = Nothing

    Next Ary

End Sub

 

[itmasterw]

Thanks Golom, That worked. I was wondering though if you can tell me what the this pice of code you wrote does:
Rs.CursorLocation = adUseClient

ITM

 

[Golom]

From ADO Help

adUseClient
Uses client-side cursors supplied by a local cursor library. Local cursor services often will allow many features that driver-supplied cursors may not, so using this setting may provide an advantage with respect to features that will be enabled. For backward compatibility, the synonym adUseClientBatch is also supported.

adUseServer
Default. Uses data-provider or driver-supplied cursors. These cursors are sometimes very flexible and allow for additional sensitivity to changes others make to the data source. However, some features of the Microsoft Client Cursor Provider (such as disassociated recordsets) cannot be simulated with server-side cursors and these features will be unavailable with this setting.

In this specific case you may do just fine with the more limited server-side cursor but I just instinctively force a client side cursor because I've been bitten by missing functionality and general weirdness when I omit it.

 

[itmasterw]

Thanks again

ITM