PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Displaying ACCESS/VB.Net Query results WITHOUT any data Binding.-VBForums
Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2019
    Posts
    23

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

    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).

    Code:
    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

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,796

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

    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:
    Code:
    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:
    Code:
    ds.tables("Db05060") ...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2019
    Posts
    23

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

    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?

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,719

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

    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:
    vb.net Code:
    1. 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.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,719

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

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width