Results 1 to 4 of 4

Thread: Storing Comma Delimited Strings to DataTable/dataadapter/AccessDatabase

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2019
    Posts
    108

    Storing Comma Delimited Strings to DataTable/dataadapter/AccessDatabase

    I have inherited an existing Access Database that has a table with a field defined as a "short string"

    This database gets loaded into my projects DataTable via OleDbDataAdapter and OleDbCommandBuilder

    The table in question "rs.Rows(irs).Item("ACT_FLOW") " currently has the string "25.28,0.34,23.04" that is in place at the instance when it is about to be changed

    In execution of the program I update that value


    Code:
    sGal = "25.28,0.34,23.04"
    rs.Rows(irs).Item("ACT_FLOW") = sGal
    But this fails in the DataAdapter Update Command with the error "Query is too Complex"

    Any suggestions?
    Thanks

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Storing Comma Delimited Strings to DataTable/dataadapter/AccessDatabase

    Yeah, the commas are causing you trouble because the common is meaningful in SQL, as well. The best solution in this case depends on your situation. The easiest is to not use a comma as the delimiter. For example, you could do a replace with "|", since the | character isn't meaningful. There are also ways to get the comma in there if you really want to keep the commas. The final solution is probably not workable, but is otherwise the best, would be to not store that type of data. A comma delimited string is essentially "packaged data". There are times when you need to store packaged data as a package, but such data isn't readily searchable, so it somewhat defeats the purpose of a database. Still, you know best whether the data should remain packaged, or would be more useful if it were not packaged, and if you feel it needs to remain packaged, consider not using a symbol other than a comma or other meaningful symbol.
    My usual boring signature: Nothing

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Storing Comma Delimited Strings to DataTable/dataadapter/AccessDatabase

    But... it's a string... so the commas shouldn't matter... otherwise string "like, this one, for instance" would also fail... would it not? And I'm guessing it doesn't... so..... shrug. dunno.

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

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Storing Comma Delimited Strings to DataTable/dataadapter/AccessDatabase

    I'm probably thinking about this wrong. A quick search on "Query Is Too Complex" suggests that this can have some strange causes. It sounds more like an Access bug than anything in particular to do with the query. I was thinking of some of the oddities that could arise with a naively created SQL string, but I wouldn't expect a dataadapter to create a naive query....except that this does appear to be Access, so you never know...
    My usual boring signature: Nothing

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