SQL Query Not Filling RecordSet Help

Hey Guys

I am using a sql query to fill a recordset but for its not filling the recordset for some reason and i cant see why.

here is my code

Dim sqlstring As String
Dim myConnection As ADODB.Connection
Dim myRS As New ADODB.Recordset

Set myConnection = New ADODB.Connection
myConnection.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=" & App.Path & "DatabaseDatabase.mdb" & ";"

myRS.CursorLocation = adUseClient
myRS.Open "SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2 FROM CLIENT, TREAT WHERE CLIENT.CCODE = TREAT.CCODE AND TREAT.DATE >= " & CDate(CustomStartDate) & " AND TREAT.DATE <= " & CDate(CustomEndDate) & " GROUP BY CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2", myConnection, adOpenKeyset, adLockOptimistic


If IsNull(myRS!Street2) = True Then
txtResults.SelText = myRS!CFIRST + " " + myRS!CSURNAME + " " + myRS!Street + vbCrLf
Else
txtResults.SelText = myRS!CFIRST + " " + myRS!CSURNAME + " " + myRS!Street + " " + myRS!Street2 + vbCrLf
End If
myRS.MoveNext
Loop

myRS.Close
Set myRS = Nothing

Please help

Skyaxe

Comments

  • [b][red]This message was edited by KhalilDutch at 2006-12-15 8:24:11[/red][/b][hr]
    Hello Skyaxe,

    You're using an ADODB, this is how I use it, hope it's something you can use:

    Declaring
    [code]
    Dim objMyDB As ADODB.Connection
    Dim objMyRecordset As New ADODB.Recordset
    [/code]

    Initialising
    [code]
    Set objMyDB = New ADODB.Connection
    Set objMyRecordset = New ADODB.Recordset
    [/code]

    Connecting
    [code]
    Call objMyDB.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=App.Path & "DatabaseDatabase.mdb";Persist Security Info=False")
    [/code]

    Querying
    [code]
    strSelectionstring1="SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET," &_
    "CLIENT.STREET2 FROM CLIENT, TREAT WHERE CLIENT.CCODE = TREAT.CCODE" &_
    " AND TREAT.DATE >= " & CDate(CustomStartDate) & " AND TREAT.DATE <= " & _
    CDate(CustomEndDate) & " GROUP BY CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2"
    Set objMyRecordset = objMyDB.Execute(strSelectionstring1)
    DoEvents
    [/code]

    I hope this is enlightening ;-)

    cheers!


    [b]--Khalil[italic][red]Dutch[/red]--[/italic][/b]
    ___________________________
    [code]If ... Then
    ...
    Else
    ...
    Whatever....[/code]



Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion