ADO data Control

[GrimmReaper]

Hello, I have an ADODC on my form. I want to populate the RecordSource etc. through coding
i also want to be able to Add, Update and Delete records to it in code
This is what I have so far

Private conn As adoDB.Connection
Private rs As adoDB.Recordset
 
Private Sub cmdBack_Click()
[b]adoDBHorse.Recordset.MovePrevious[/b]
End Sub
 
Private Sub cmdNext_Click()
[b]adoDBHorse.Recordset.MoveNext[/b]
End Sub
 
Private Sub Form_Load()
 
    Set conn = New adoDB.Connection
    ' Point this to NorthWind.mdb sample database.
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & CurDir & "\ST.mdb"
    Set rs = New adoDB.Recordset
    rs.CursorLocation = adUseClient
    Set rs.ActiveConnection = conn
    rs.Properties("IRowsetIdentity").Value = True
    rs.Open "HorseDet", , adOpenKeyset, adLockOptimistic, adCmdTableDirect
    Set adoDBHorse.Recordset = rs
 
 
[b]txtHorseName.DataField = adoDBHorse.Recordset("strHorseName")[/b]
End Sub
 
Private Sub txtHorseName_LostFocus()
adoDBHorse.Recordset.AddNew
 
End Sub

But I keep getting errors on the bolded lines saying that Either BOF and EOF are true.

How can I fix this ?

[Hack]

I can easily show you how to do what you need to do through VB and SQL code, but my memory of using data controls is, putting it politely, limited.

What is the purpose of adbDBHorse and why are you using it instead of rs which you have already declared as a recordset?

[Bombdrop]

GrimmReaper I would urge you to move away from using the ADODC , I consider it leads to bad/poor programms. Instead use ADO only and use a collection class structure to store and iterate through the retrieved data.

[GrimmReaper]

What is the purpose of adbDBHorse and why are you using it instead of rs which you have already declared as a recordset?

Hello Hack!, thanx.
Well I honestlty can't tell you anymore (i've confused myself soo much with this small app already!)
So, do you suggest I should use
rs.MoveNext
and
rs.MovePrevious
What about the textBox's datafield ?

[GrimmReaper]

Hello BombDrop, thanx for the links!

I already have a collection class, but I don't know how to incorporate the database.
I only have 1 field in 1 table - the reason for this, is to store the names (instead of typing in over again) - I was considering a textfile (but how can I read one line at a time, click next, read another etc.)
I'm attaching my project with - it's quite small
I just need a way to store and retrieve the HorseName.

[Hack]

Is there a reason that you want to display them one at a time as opposed to reading them all in and displaying them in a listbox or a listview?

[Bombdrop]

Below is an example of a collection class getting data in from a recordset via the Load method, notice i have changed the standard Add Method to accept only the object of the class.

Option Explicit
 
'local variable to hold collection
Private m_colClients As Collection
 
Private Sub Add(ByVal Client As clsClient)
 
    'Add the object to the collection
    m_colClients.Add Client, Client.clientshort
 
End Sub
 
Public Property Get Item(vntIndexKey As Variant) As clsClient
    'used when referencing an element in the collection
    'vntIndexKey contains either the Index or Key to the collection,
    'this is why it is declared as a Variant
    'Syntax: Set foo = x.Item(xyz) or Set foo = x.Item(5)
    Set Item = m_colClients(vntIndexKey)
End Property
 
 
 
Public Property Get Count() As Long
    'used when retrieving the number of elements in the
    'collection. Syntax: Debug.Print x.Count
    Count = m_colClients.Count
End Property
 
 
Public Sub Remove(vntIndexKey As Variant)
    'used when removing an element from the collection
    'vntIndexKey contains either the Index or Key, which is why
    'it is declared as a Variant
    'Syntax: x.Remove(xyz)
 
 
    m_colClients.Remove vntIndexKey
End Sub
 
 
Public Property Get NewEnum() As IUnknown
    'this property allows you to enumerate
    'this collection with the For...Each syntax
    Set NewEnum = m_colClients.[_NewEnum]
End Property
 
 
Private Sub Class_Initialize()
    'creates the collection when this class is created
    Set m_colClients = New Collection
End Sub
 
 
Private Sub Class_Terminate()
    'destroys collection when this class is terminated
    Set m_colClients = Nothing
End Sub
'======================================================================
'Procedure  :Load (Sub)
'Date       :28/09/2004
'InPut      :
'Returns    :
'Author     :© BombDrop
'Purpose    :Will load the collection with Client information
'======================================================================
Public Sub Load()
 
    On Error GoTo Load_Error
 
    Dim conClient As ADODB.Connection
    Dim cmdClient As ADODB.Command
    Dim recClient As ADODB.Recordset
    Dim objClient As clsClient
 
    Dim strConnectionString As String
 
    Set conClient = New ADODB.Connection
    Set cmdClient = New ADODB.Command
 
    
        
 
    'Set conClient properties and open
    conClient.ConnectionString = CONECTIONSTRING
    
    conClient.CursorLocation = adUseClient
    conClient.Open
 
 
    'set and Execute the command
    cmdClient.CommandText = "procDemoClient"
    Set cmdClient.ActiveConnection = conClient
    Set recClient = cmdClient.Execute
 
DoEvents
    Do While Not recClient.EOF
        Set objClient = New clsClient
 
        objClient.addressref = recClient!addressref
        objClient.analysisarea = recClient!analysisarea
        objClient.clientgroupref = recClient!clientgroupref
        objClient.clientname = recClient!clientname & ""
        objClient.ClientRef = recClient!ClientRef
        objClient.clientshort = recClient!clientshort & ""
        objClient.Comments = recClient!Comments & ""
        objClient.contact1 = recClient!contact1 & ""
        objClient.email1a = recClient!email1a & ""
        objClient.fax1 = recClient!fax1 & ""
        objClient.handler1 = recClient!handler1 & ""
        objClient.handler2 = recClient!handler2 & ""
        objClient.handler3 = recClient!handler3 & ""
        objClient.joindate = recClient!joindate & ""
        objClient.leavingdate = recClient!leavingdate & ""
        objClient.namesref = recClient!namesref
        objClient.salutation = recClient!salutation & ""
        objClient.telephone1 = recClient!telephone1 & ""
        objClient.usamemo1 = recClient!usamemo1 & ""
        objClient.usamemo2 = recClient!usamemo2 & ""
        objClient.Address1 = recClient!Address1 & ""
        objClient.Address2 = recClient!Address2 & ""
        objClient.Address3 = recClient!Address3 & ""
        objClient.Address4 = recClient!Address4 & ""
        objClient.Address5 = recClient!Address5 & ""
        objClient.Address6 = recClient!Address6 & ""
        objClient.Postcode = recClient!Postcode & ""
 
        Add objClient
        recClient.MoveNext
    Loop 'While Not recClient.EOF
 
 
 
    'DisconClientect recClientordset
    Set recClient.ActiveConnection = Nothing
 
    'Close conClientection
    conClient.Close
 
    'Destory objects
    Set conClient = Nothing
    Set cmdClient = Nothing
    Set recClient = Nothing
    Set objClient = Nothing
 
 
 
    GoTo CleanExit:
 
Load_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & vbCr & _
        "Found In Class Module: Clients " & vbCr & "Found In Procedure: Load" & _
        vbCr & IIf(Erl > 0, "Found In Line:" & Erl, ""), vbCritical, _
        "Error Occurred"
 
    Call LogError("Clients:Load", Err.Description, Err.Number, Erl)
 
CleanExit:
    On Error GoTo 0
 
End Sub

hope this helps!!

[GrimmReaper]

Is there a reason that you want to display them one at a time as opposed to reading them all in and displaying them in a listbox or a listview?

Wow, you guys are amazing!
yes, there is a reason - I edit one Horse at a time, I click next, then edit the next one

Thanx bombdrop, I'll quickly have a lokk

[GrimmReaper]

Hello again, I've tried inserting the code, but everywhere I put it, nothing happens
How can I incorporate it in my Collection or class ?
I'm still very new to this

[Hack]

Where did you put it? In a class module or a .BAS module?

[GrimmReaper]

I put it in my Class Module - is that wrong ?

[Hack]

I put it in my Class Module - is that wrong ?

No. Based on his code I believe that to be exactly right. However, you say nothing happens when you try to use it?

How are you calling it?

[Bombdrop]

Here is a very quick and dirty example using nwind DB


Hope this helps!!

[GrimmReaper]

Hello again guys!
Bombdrop:
Based on your example, I've tried to implement the same - everything looks ok, but, when the Form Loads I get this error:
"Object Does Not Support this property or method"
on this line:
txtHorseName.Text = colHorse(FrmIndex).HorseName

Waht am I doing wrong (again)