-
Dec 10th, 2020, 12:36 PM
#1
Thread Starter
Lively Member
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
-
Dec 10th, 2020, 12:54 PM
#2
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
-
Dec 10th, 2020, 01:27 PM
#3
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
-
Dec 10th, 2020, 05:09 PM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|