dcsimg
Results 1 to 5 of 5

Thread: Composite Keys

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    3

    Question Composite Keys

    I am developing an application that will have two databases at distant sites synchronised using a custom sync engine (this is necessary as it will be done over a very low-bandwidth VHF radio link, but that's another story!). To avoid the problem of key conflicts, each of the primary keys are composite keys, made up of two columns. The first column identifies the originating database (eg 0 for master, 1 for the remote database), the second column is an integer which is auto-incrementing.

    Therefore, it is possible to have for example:

    origin......pk.......name
    0.........1.........bloggs
    0.........2.........smith
    1.........1.........jones

    On the SQL side of things, this is all fine.

    However, when it comes to getting the application to work with this I am encountering some issues. Depsite having set up the relationships and keys in the dataset designer, I cannot work out a way of binding my controls to composite keys.

    For example, a combobox. If I set it to be bound to the 'pk' field, and I move to a record where '1-1-jones' is the value in the database, it actually shows 'bloggs'. I can see that this is because it is finding the first record with pk=1. But pk is only part of the key, I cannot see a way of telling the combobox object to bind to a composite key.

    Am I being too optimistic in thinking that this can be done without manually coding it?! Or have I missed something obvious? Many hours of searching the web has yielded no answers.

    Many thanks in advance.

    Ollie

  2. #2

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    3

    Re: Composite Keys

    Probably would have been useful to add(!), I am using VB .net 4.5 in VS2013

  3. #3
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,338

    Re: Composite Keys

    When you save... via vb, can you parse the 'id'.
    If so then add 's' + origin+'pk'+pk then when saving parse the string 'id' and add to the columns as required...?
    Same thing for the sql driving the grid/table view. Just need to process it before displaying in the form...

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  4. #4

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    3

    Re: Composite Keys

    Quote Originally Posted by Ecniv View Post
    When you save... via vb, can you parse the 'id'.
    If so then add 's' + origin+'pk'+pk then when saving parse the string 'id' and add to the columns as required...?
    Same thing for the sql driving the grid/table view. Just need to process it before displaying in the form...
    Thanks for your reply. I didn't get a notification and totally missed it, so apologies for delay in coming back to you!

    That is the kind of thing I figured I would have to do, just wondered if there was a cleaner way. I don't know how commonly composite keys are used, but I was surprised there was no way of doing this out of the box.

    Anyway, thanks again!

    Ollie

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,247

    Re: Composite Keys

    They're not uncommon... but they aren't used a lot. Usually though, when they are used, they are selected as a single concatenated field, not as their individual fields. Or, only select the ones you want based on the source Id in the first place.

    -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??? *

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