[RESOLVED] Ado/sql

[kfcSmitty]

Not sure if this would be the right area...But I am wondering if it would be more efficient to use SQL instead of the ADO I am using below.

Every ADO search I do requires a for loop, and this seems to slow everything down (duh). I am under the impression if I use SQL I won't need one?

Here is my code..the bolded is the basics of the loop

Public Sub Supports(Hours As Integer, Payband As String, Account As String)
[b]RsPayband.Open "SupportPayband", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    RsPayband.MoveFirst
    Do While RsPayband.EOF = False[/b]
       'checks to see if Payband matches value in table
       
        If AnniversaryHappened Then
            If (Payband + 1) > MaxPayband Then
                newpayband = Payband
            Else
                newpayband = Payband + 1
            End If
           [b] If RsPayband.Fields("payband") = newpayband Then
                If strLevel <> "0" Then
                    newSupportDailyTotal = ((RsPayband.Fields(strLevel) * (Hours / 5)))
                End If
            End If[/b]
        End If
            [b]If RsPayband.Fields("Payband") = Val(Payband) Then
                If strLevel <> "0" Then
                    SupportDailyTotal = ((RsPayband.Fields(strLevel) * (Hours / 5)))
                End If
            End If
        RsPayband.MoveNext
    Loop
    RsPayband.Close[/b]
End Sub

I am not sure what the SQL would look like, but I am guessing something like this

RsPayband.Open "Select * from SupportPayband", cn, adOpenKeyset, adLockPessimistic
 
SupportDailyTotal = "Select " + txtlevel.text + " from SupportPayband where Payband = " + txtPayband.text

Or something along those lines (I've never used SQL )

Anyways, any tips would be appreciated

[szlamany]

Using SQL to get a single record is the better way to do it.

More like:

Dim strSQL as String

strSQL = "Select " + txtlevel.text + " from SupportPayband where Payband = " + txtPayband.text

Debug.Print strSQL ' Just so you can see the query you are about to execute!

RsPayband.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText

[kfcSmitty]

hrm, it keeps giving me a type mismatch error

debug is printing


"Select Level3 from SupportPayband where Payband = 2"

Which looks right to me

would that kind of select work for a table with the following layout?

Payband     Level1     Level2     Level3      Level4     Level5
   1         ##.##      ##.##      ##.##      ##.##     ##.##
   2         ##.##      ##.##      ##.##      ##.##     ##.##
   3         ##.##      ##.##      ##.##      ##.##     ##.##
   4         ##.##      ##.##      ##.##      ##.##     ##.##

?


also, how would I display the information I have pulled out?

[kfcSmitty]

Okay, figured out the type mismatch error. Now I need to know how to display the information I have pulled out

*edit* Figured the rest out, thanks