How to use RecordSet in separate class object

[kutlesh]

I have a class where I keep all of my data collected from the form.

I am having trouble to use RecordSet inside the class object.

Here is a sample of my application state class:

public class MyAppDataClass
     public db2Conn as ADODB.Connection
     public RecordSet1 as ADODB.RecordSet
     public field as String

     public sub New()
            db2Conn = New ADODB.Connection()
            db2Conn.Open("access 2000 connection string")
            db2Conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient

     end sub 

     public function checkData() as boolean
          dim result as boolean = true

          try
               sql = "some select query"
               RecordSet1.Open(sql, db2Conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText)
          
               if not RecordSet1.EOF then
                    field  = cstr(RecordSet1.Fields("field1").Value) ' it breaks with RecordSet is not an instance of an object when called from event handles
                     ' or it breaks with Database Object is closed
               else
                    result = false
               end if

            catch ex As Exception
               result = false
            end try

          return result
     end function
end class

Private Sub myField_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles myField.KeyUp
      If e.KeyCode = Keys.Left Or e.KeyCode = Keys.Up Or e.KeyCode = Keys.Enter Then
            e.Handled = False
        ElseIf Len(myField.Text) = 6 Then
 
            ' it breaks with RecordSet is not an instance of an object when called from event handles
            ' or it breaks with Database Object is closed
            if instanceOfMyAppDataClass.checkData() then 
                  ' TODO
            else 
                  ' TODO
            end if
       end if
end sub

If I call all of those RecordSet commands inside the event handler it works:

Private Sub myField_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles myField.KeyUp
      If e.KeyCode = Keys.Left Or e.KeyCode = Keys.Up Or e.KeyCode = Keys.Enter Then
            e.Handled = False
        ElseIf Len(myField.Text) = 6 Then
 
            try
               sql = "some select query"
               RecordSet1.Open(sql, db2Conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText)
          
               if not RecordSet1.EOF then
                    field  = cstr(RecordSet1.Fields("field1").Value) 
               end if

            catch ex As Exception
              ' print exception to some output
            end try
       end if
end sub

What should I do or what do I do wrong when using RecordSet from inside other class?

[Pradeep1210]

if instanceOfMyAppDataClass.checkData() then

What is instanceOfMyAppDataClass?

Try

If Me.checkData() Then

[jmcilhinney]

Why are you using a Recordset at all? If you're going to use .NET then you really ought to use .NET, which means ADO.NET for data access.

[kutlesh]

Dim instanceOfMyAppDataClass as MyAppDataClass

Private Sub frmSV50_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
      instanceOfMyAppDataClass = new MyAppDataClass()
end sub

[kutlesh]

Because it all went in that direction... and nobody can guide me trough the learning process of ADO.NET, neither anybody has bothered to learn it in this company...

[kutlesh]

From these: https://docs.microsoft.com/en-us/dot...-code-examples

which one should I use to connect to IBM DB2 32bit database?

ODBC, or again some Access 2000 combo with OleDb?

But IBM DB2 version here is old, like 20 years, and it might not work if i don't use Access 2000 bridge.

[jmcilhinney]

If you are not allowed use ADO.NET then that's one thing but, if you are, do you really need to be guided? There's plenty of tutorials around. It might just be the most widely discussed area of .NET programming. There's even a Database FAQ link in my signature below that leads to a number of useful links on this very site. If you work your way through literature that covers the basics, then you can ask specific questions if and when you encounter specific issues.

[Shaggy Hiker]

One of the issues with using a RecordSet is that nobody uses them, so finding somebody who can give you any meaningful suggestions is relatively difficult. You may actually get better suggestions in the ClassicVB forum, though if you mention that you are using .NET, they'll immediately want the thread moved.

[kutlesh]

Hmm, I will be forward thinking and eventually replace all RecordSet statements with ADO.NET replacement.
The time does not allow to go that far.

Is ADO.NET DataSet appropriate way to go?

[Shaggy Hiker]

A dataset is just a collection of datatables.

The datatable offers somewhat more than the recordset did, if I remember right. The idea behind a datatable was that you fill it, then detach from the database, and only interact with the database again when you are ready to push the data back. I believe the recordset maintained the connection while the recordset was in use, though I could easily be wrong about that.

The other option is the DataReader, which may act more like a recordset, but it is forward-only and read-only, so you can't use it for updating data (though you CAN use it to fill a datatable), and you can't use it for going both backwards and forwards through the data.

[jmcilhinney]

ADO.NET vs ADO follows a similar pattern to many areas when coming to VB.NET from VB6 in that VB6 used to use one type/object to do everything while VB.NET makes use of several smaller types/objects that each specialise in one part of the process. In ADO.NET, a DataSet is basically an in-memory representation of a database. It contains DataTables, which correspond to database tables, and DataRelations, which correspond to database relations. A DataTable contains DataColumns that describe the data and DataRows that contains the data, just as a database table contains columns and rows. You will generally use a data adapter to move data back and forth between a database and a DataTable. The data adapter contains up to four command object that correspond to the SQL SELECT, INSERT, UPDATE and DELETE statements. The command objects each refer to a connection object that represents the connection to the database. In VB6, all that is rolled up into the Recordset. The monolithic approach may seem easier and it is for those whose understanding of the details is relatively minimal. Once you're used to it, using the multiple types is pretty simple and it tends to be a bit more flexible as you only use the specific parts you need for any particular job.