Displaying ACCESS/VB.Net Query results WITHOUT any data Binding.

[pj.field]

Hi, guys. Hope you are all keeping safe and well in this awful time.

I am having time to follow earlier lines of thought, mainly inspired by JMC (thanks)
For various reasons, I am constrained to using VB.NET to 'manipulate' a ".accdb" type Access database.
I have a 90% working database with which I am quite pleased - again, in a large part to you guys offering advice.
Could you help once again with my final (Ihope!) problem, please?

As you will see from the code below, I call sql for various items, and they all work very well : except the "Search".
It may well be working, but I cannot interpret the results, because I don't know how to access them. (No pun intended).

Public Class Form1

    Dim ds As New DataSet                                     'HOLDS A DATASET OBJECT
    Dim da As OleDb.OleDbDataAdapter                'HOLDS A DATA_ADAPTER OBJECT
    Dim sql As String                                       	'HOLDS A SQL STRING
    Dim con As New OleDb.OleDbConnection         'THE CONNECTION OBJECT
    Dim dbProvider As String                                	'HOLDS THE PROVIDER
    Dim dbSource As String                                  	'HOLDS THE DATA SOURCE
    Dim TheDatabase As String                               	'HOLDS THE DATABASE NAME
    Dim THEDATABASEFOLDER As String                 'HOLDS THE DATABASE NAME
    Dim FullDatabasePath As String                         'HOLDS THE DATABASE PATH

    Dim status As String                                    	'THIS SHOWS THE TYPE OF OPERATION, ADD NEW, DELETE, SEARCH & etc.
    Dim curRec, curCell As Integer                          	'POINTS TO CURRENT RECORD ROW, CURRENT COLUMN
    Dim maxRec As Integer                                   	'HOLDS THE CURRENT NUMBER OF RECORDS IN THE TABLE

'**********

    Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        'START THE INTERRUPT TIMER TO UPDATE THE TOOLSTRIP 
        Timer1.Enabled = True
        Timer1.Start()

        'SET UP THE PROVIDER
        dbProvider = "Provider=Microsoft.Ace.OLEDB.12.0;"

        'SET UP THE DATABASE AND ITS LOCATION "C:\Users\Public\Documents"
        TheDatabase = "/DB0506.accdb"
        THEDATABASEFOLDER = Environment.GetFolderPath(Environment.SpecialFolder.CommonDocuments)
        FullDatabasePath = THEDATABASEFOLDER & TheDatabase

        'SET THE DATA SOURCE
        dbSource = "Data Source = " & FullDatabasePath

        'SET THE CONNECTION STRING
        con.ConnectionString = dbProvider & dbSource

        'OPEN THE DATABASE
        con.Open()

        'STORE AN SQL STRING
        sql = "SELECT * FROM Data"

        'PASS THE SQL STRING AND CONNECTION OBJECT TO THE DATA_ADAPTER
        da = New OleDb.OleDbDataAdapter(sql, con)

        'FILL THE DATASET WITH RECORDS VIA THE DATA_ADAPTER FROM THE DATABASE (TABLE DEFINED IN SQL)
        da.Fill(ds, "DB0506")

        MessageBox.Show("Db Opened")

        con.Close()
        MessageBox.Show("Db Closed")

        'TOTAL NUMBER OF RECORD ENTRIES
        maxRec = ds.Tables("DB0506").Rows.Count

        'POINT TO ROWINDEX OF FIRST RECORD ENTRY
        curRec = 0

         'DISPLAY THE RECORD IN THE TEXT BOXES
        Call showRecord(curRec)

    End Sub

'**********

    ''' <summary>
    ''' THIS DISPLAYS THE CURRENT RECORD IN THE TEXT BOXES, USING <c> curRow </c> as the Rowindex 
    ``` ONLY USING THE FIRST 'ID'  AND THE NEXT TWO COLUMNS AT THIS TIME
    ''' </summary>
    Private Sub showRecord(curRec As Integer)
       txtID.Text=ds.Tables("DB0506").Rows(0).Item(0)
        txtFirstName.Text = ds.Tables("DB0506").Rows(curRec).Item(1)
        txtLastName.Text = ds.Tables("DB0506").Rows(curRec).Item(2)
    End Sub

'**********

   ''' <summary>
    ''' FIRSTNAME TEXT BOX KEYPRESS DETECTED
    ''' IF ADDING NEW RECORD, POINT TO NEXT TEXTBOX
    ''' IF SEARCHING ON COLUMN NAME "FIRSTNAME" THEN SQL THE SEARCH
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub txtFirstName_KeyUp(sender As Object, e As KeyEventArgs) Handles txtFirstName.KeyUp
        If e.KeyCode = Keys.Enter Then
            If status = "Add" Then
                Try
                    txtLastName.Focus()
                Catch
                    MessageBox.Show("Error : " - No status, Or LastName IsNot Focused")
                End Try

            ElseIf status = "Search" Then
                    Try
                        sql = "SELECT * FROM Data WHERE Data.FirstName Like '%" & Me.txtFirstName.Text & "%'"
                        'PASS THE SQL STRING AND CONNECTION OBJECT TO THE DATA_ADAPTER
                        da = New OleDb.OleDbDataAdapter(sql, con)
                        da.Fill(ds, "Db0506")
                    Catch
                         MessageBox.Show("Error with sql / da ??")
                    End Try
'
'                                                                       
' WHAT DO I DO HERE TO DISPLAY THE FIRST 'FOUND' ROW DATA?????
'
'
             End If
        End If
 
   End Sub

The desired result from the query may well be in the dataset, but I cannot get to it. The main reason is that, due to other considerations stipulated by my colleagues, the text boxes
showing the data cannot be Bound, nor is there room on screen for a DataGridView. Opening another window for a DGV is also being frowned upon.

Could some kind person please let me know how to find the Row Number (in the main database) where the returned search query lies? Then I can access the row,
and get and display the record contents.

Thanking you in advance,

Best regards,

Pete

[techgnome]

It's in your dataset, in a datatable, named "Db0506" ... I'm not sure what you mean by "how to find the Row Number (in the main database) where the returned search query lies"...
But this:

da.Fill(ds, "Db0506")

Runs the query and puts the results in a datatable in the dataset and calls its "Db0506" ... you get to the datatable like this:

ds.tables("Db05060") ...

-tg

[pj.field]

Hi, techgnome. Thanks for your input. I must be missing the point here, as I can't display the searched-for string.
The FirstName fields in the first (and only) records at the moment are "Peter", "Alfie", "Billy" & "Eddie".
If, say, my Current Record ID=2, ie 'looking' at the record holding "Billy", and I enter the search string parameter "Alfie", no errors show.
BUT when I ask to see ds.Tables("DB0506").Row(0). Item(0) it returns the first record in the database, one containing "Peter" in the FirstName field.
Where am I going wrong?

[jmcilhinney]

You are retrieving all the data from your Data table when the form is first displayed and then later you query the same table for only some of the data and put the results in the same DataTable as before. There's two issues there.

If you populate the same DataTable then you are adding the new results to the end of the old results, so the first row is still going to be the same row it was before the second query. If you want the new results then you have to get the LAST row, not the first.

That said, what's the point of the second query at all? You already got all the data to begin with so what's the point in going back to the database to get a subset of the data that you already have? If you want a specific row, get it from the already-populated DataTable. No new query required.

There's a lot that could be tidied up about your code but let's limit ourselves a bit here. Firstly, get rid of the DataSet unless you specifically want multiple DataTables. If all you want is one DataTable then just create one DataTable. Once you have populated that with all the data at startup, you can search it as required. There are various ways to do that. One would be to call the Select method of the DataTable. Another would be to use the DefaultView to access the data instead of the Rows. That is a DataView and thus allows you to filter and sort. For example, if you do this:

myDataTable.DefaultView.RowFilter = $"FirstName Like '%{Me.txtFirstName.Text}%'"

then only matching rows will be exposed via the DefaultView. If the RowFilter is blank then all rows are exposed. Just note that each item in the DefaultView is a DataRowView rather than a DataRow. They can be treated the same way for basic data access but there are some differences.

[jmcilhinney]

A better way to approach this would be to use a BindingSource. You can bind your DataTable to a BindingSource without binding that to your UI. The BindingSource uses the DefaultView under the hood, so it items are DataRowView objects. You access the current record via the Current property. You can navigate by calling the MoveFirst, MovePrevious, MoveNext and MoveLast methods or setting the Position property and that will update the Current property. You can set the Filter property the same way as the RowFilter in my previous post.