[RESOLVED] Ado/sql

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

VB Code:

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

VB Code:

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 :blush: )

Anyways, any tips would be appreciated

Re: Ado/sql

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

More like:

Code:

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

Re: Ado/sql

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?

Code:

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

?

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

Re: Ado/sql

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