Results 1 to 2 of 2

Thread: Getting to grips with ADO & VB.Net

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2020

    Getting to grips with ADO & VB.Net

    Hi there. I'm totally new to VB programming for ADO, but am already quite a way on from what I was when I started looking into it yesterday.

    I am NOT using msaccess, other than to create the accdb tables in the first place. All user interaction is via this VB.Net application.

    So I have this accdb database which I've read a dataset from, and have also bound a datagrid to that dataset. Obviously I had to build a dataadapter to accomodate the connection. I did this from tutorials I found on the net.
    The application I'm building is in VB.Net and will be a standalone application which can be used by mulitple users simultaneously to query the single database on a network shared area.
    So for the sake of argument, lets say TWO users have the app open. Both have read the data using the DA and both have their DS populated on their DataGrid - I guess each user therefore has captured a "snapshot" of the database to work on. The highest primary key for both users might be "10002345", for example, as they have both have their own snapshot (DS) of the same database.
    > User 1 adds a row to the dataset using a the "next" primary key "10002346" and "updates" the DB via the DA.
    > User 2 adds a row to the their earlier snapshot of the dataset using the same primary key - generating an error - duplicate key.

    So, The solution is for each user to double-check for the maximum primary key immediately before "updating" the DB. They must NOT do this from their own earlier snapshot (DS), but from the DB itself, as the DB might not be as they "photographed" it fort their DS some time earlier.

    My Newbie question is:
    > How do I do a quick standalone query from the database to verify the Max primary key before saving da.update(ds), without reloading the full dataset? In other words, How can I run a query SELECT MAX (PKEY) FROM table and place the result into a single integer variable (and thus into the newly created DS record) without having to repopulate my whole dataset? The PKEY is an integer, NOT an autonumber.

    Do I need a second adapter - one for each SQL query? Surely not. The DA is the "channel", right, not the command itself... (I think)
    Assuming I use the same adapter with a different Query, do I have to pass that to a new dataset or can I pass the result directly to an integer variable? Will any of this affect my existing dataset (earlier snapshot)?

    Bottom line is "How do I arbitrarily send SQL queries to the SOURCE DATABASE after populating the dataset from it some time earlier?" Surely I don't have to entirely refresh the DS / re-read the whole database.

    Sorry if this is very basic question! I am completely new to this.


  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Sydney, Australia

    Re: Getting to grips with ADO & VB.Net

    Firstly, it's ADO.NET. ADO is something specific and different. If you're using data adapters and DataSets then you're using ADO.NET.

    As for your issue, it may be a non-issue. Is there a specific reason that you have the application setting IDs explicitly? Usually, the database will generate the ID when you save the data, so it doesn't matter how many users are saving data at the same time. In an Access database, that would mean using an AutoNumber column. The DataTable generates temporary IDs when you add rows and they are replaced with values generated by the database when you save. By default, the temporary values will be negative, to easily distinguish them from final values and avoid any clashes.

    That said, if you ever do need to query the database for a single value at any point, be it an existing value, a count, a sum or something else, you create a command and call ExecuteNonQuery. I suggest that you follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data for code examples of common ADO.NET scenarios, including that one. There's also another thread on retrieving auto-generated IDs from Access back into your DataTable. Sometimes that is not required, i.e. when you are done with the data after saving and there are no foreign keys to save, but sometimes it is.

Posting Permissions

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

Click Here to Expand Forum to Full Width