none
Visual Basic: Is it a good idea to use TableAdapters for searching in a database in VB? RRS feed

  • Question

  • I used the wizard that lets me fill a data grid using fill by.
    The database is made in Microsoft access. 
    For example: In this case, the user enters ID in a textbox txtInvSearch and gets a result.

    Note: The search feature in my program works but I feel like I took a short-cut and almost feel guilty for some weird reason...





    #Region "Button  Search "
    
    #Region "ID Search "
        Private Sub btnInvSearch_Click(sender As Object, e As EventArgs) Handles btnInvSearch.Click
            Try
                Me.MasterTableAdapter.Fill_Grid_By_Name(Me.TS_History_Docket_FiletestDataSet.Master, txtInvSearch.Text)
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
        End Sub
    #End Region

    Please do let me know if there's an alternate/better way? 
    I kind of found the easiest way out :P 



    Wednesday, June 28, 2017 8:44 PM

Answers

  • I personally do not case for working with table adapters. Table Adapters are good for a small database be it a single table or less than a handful of related tables. Even so there can be issues when you make changes to the underlying table structures to properly regenerate the classes in your project the adapters utilizes.

    Doing a simple like condition with multiple values can be a real pain. I recently wrote a MSDN code sample (sorry it's C#) that shows the pains one must go through to do this.

    Suppose you are adding new rows (using ms-access) in the DataGridView via a Table adapter, you must reload the DataGridView to get the new primary keys back or jump through hoops while with SQL-Server and Table Adapters the new primary key comes back on a successful add.

    Using a connection and command with parameters will require more code yet provides flexibility all around.

    In regards to searching data, it can be simple or complex. Here is a simple example I did for Microsoft that shows the basics for searching w/o a TableAdapter.

    We can cheat using the following component for filtering (yeah not the same as searching) which requires two or three lines of code to implement. I've used it with customization (not easy at first).

    Any ways that's it in a nutshell.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, June 28, 2017 11:36 PM
    Moderator
  • Here is adding in ms-access

    https://code.msdn.microsoft.com/Adding-new-records-into-53ce3eb1?redir=0

    These are samples I've done in the past, whatever you see imagine you can learn from.

    https://1drv.ms/u/s!AtGAgKKpqdWjhCrCm4j4JttdEpvG

    https://1drv.ms/u/s!AtGAgKKpqdWjiBPLVuluvyPhaXgk

    https://1drv.ms/u/s!AtGAgKKpqdWjhmjRCOS-CYKOBZoD


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by creepyspirals Thursday, June 29, 2017 5:33 PM
    Thursday, June 29, 2017 1:31 PM
    Moderator

All replies

  • I personally do not case for working with table adapters. Table Adapters are good for a small database be it a single table or less than a handful of related tables. Even so there can be issues when you make changes to the underlying table structures to properly regenerate the classes in your project the adapters utilizes.

    Doing a simple like condition with multiple values can be a real pain. I recently wrote a MSDN code sample (sorry it's C#) that shows the pains one must go through to do this.

    Suppose you are adding new rows (using ms-access) in the DataGridView via a Table adapter, you must reload the DataGridView to get the new primary keys back or jump through hoops while with SQL-Server and Table Adapters the new primary key comes back on a successful add.

    Using a connection and command with parameters will require more code yet provides flexibility all around.

    In regards to searching data, it can be simple or complex. Here is a simple example I did for Microsoft that shows the basics for searching w/o a TableAdapter.

    We can cheat using the following component for filtering (yeah not the same as searching) which requires two or three lines of code to implement. I've used it with customization (not easy at first).

    Any ways that's it in a nutshell.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, June 28, 2017 11:36 PM
    Moderator
  • Firstly thank you for your reply you've helped me a lot in this forum :)

    Unfortunately my filter search "function" is just one part of the program :( ...
    I will be required to permit users to delete and add in new data in the database via my program.
    Are there any sample codes (VB) I should look at?

    all I know is that we use a connection string and use something called OLEdb

    -cp

    Thursday, June 29, 2017 12:57 PM
  • Despite marketing guys from all companies who sell databases tell that searching in a database is easy(They call it often so nice business solutions and never talk about databases). 

    They probably never have been asking to their development department how. 

    I always have found it a horror task to find something in a database as it was not simply getting an identifier. 



    Success
    Cor

    Thursday, June 29, 2017 1:16 PM
  • Here is adding in ms-access

    https://code.msdn.microsoft.com/Adding-new-records-into-53ce3eb1?redir=0

    These are samples I've done in the past, whatever you see imagine you can learn from.

    https://1drv.ms/u/s!AtGAgKKpqdWjhCrCm4j4JttdEpvG

    https://1drv.ms/u/s!AtGAgKKpqdWjiBPLVuluvyPhaXgk

    https://1drv.ms/u/s!AtGAgKKpqdWjhmjRCOS-CYKOBZoD


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by creepyspirals Thursday, June 29, 2017 5:33 PM
    Thursday, June 29, 2017 1:31 PM
    Moderator
  • Bless your soul !! :))
    Thursday, June 29, 2017 5:33 PM
  • Dear Ms. Payne, 

    I thought Fairy Godmothers were fictional...
    But you're high key amazing! 

    I hope to one-day be like you... helping others :)

    Thank you so much for your resources. The world needs more people like you.

    I cannot thank you enough!
    -Cp :))
    Tuesday, July 4, 2017 6:01 PM