Results 1 to 10 of 10

Thread: Problem with OleDbCommandBuilder SQL it creates for updates

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2007
    Posts
    84

    Problem with OleDbCommandBuilder SQL it creates for updates

    Database = MSAccess
    Field Name = 120 XXX

    Problem - The SQL statement generated by the builder does not enclose the field names in brackets. This then raises an exception with "error in update statement". BTW, same problem with the insert statement.

    So the question is: How can I force the builder to place brackets around the field names so that I have a statement like this:

    Code:
    UPDATE Sample SET 
    WHERE Sample.[120 XXX]="AAA"
    PS. The field names cannot be changed.

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Nov 2007
    Posts
    84

    Re: Problem with OleDbCommandBuilder SQL it creates for updates

    Bump

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Problem with OleDbCommandBuilder SQL it creates for updates

    Please don't bump your threads. Again the best reason to do things yourself (you have complete control). Another reason to follow the proper rules of databases and not use spaces in field names.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Problem with OleDbCommandBuilder SQL it creates for updates

    If you put brackets around the identifiers in the SELECT statement then the command builder will do the same. That means that you cannot use a wild card in your query. You have to specify each column individually so you can wrap brackets around each name that needs it.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Nov 2007
    Posts
    84

    Re: Problem with OleDbCommandBuilder SQL it creates for updates

    Quote Originally Posted by GaryMazzone
    Please don't bump your threads. Again the best reason to do things yourself (you have complete control). Another reason to follow the proper rules of databases and not use spaces in field names.
    Why not bump. This is common in all forums. Second I know all about proper field names but I did not create the database. I am left with it.

    If you put brackets around the identifiers in the SELECT statement then the command builder will do the same. That means that you cannot use a wild card in your query. You have to specify each column individually so you can wrap brackets around each name that needs it.
    I can't do that because each table is different.

    The lack of proper database connectivity within VB makes me crazy. I never had this many problems in Delphi. Heck. VB doesn't even have a Table component.

  6. #6
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Problem with OleDbCommandBuilder SQL it creates for updates

    The space in a field name will cause a problem with any database system. I'll leave it to you to work out then
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Problem with OleDbCommandBuilder SQL it creates for updates

    A Bump is a reply to a thread without any real content (such as just "bump", or "anyone?"), which you post in the hope of getting a reply sooner.
    Why not bump.
    It annoys people for various reasons, such as:
    • You are expecting people with the right knowledge for your problem to be online 24/7 (and have time to answer it as soon as they see it), which is not the case, especially as we are all based in different time zones. People will read & reply when they get the chance.
    • As you haven't posted any extra info at all (such as "I've tried the following code ... but it didn't help, it gave this error message instead: ..."), it heavily implies that you aren't trying to find a solution yourself - so it seems like you expect other people to do it all for you.
    • If somebody has already replied to (or subscribed to) the thread, they will get an email notification - and find that there is nothing new to see since the previous post (assuming they visited the forums since the previous post - otherwise they will just see that you are impatient).
    • ...

    These are just some of the reasons why, like on many other sites, we do not allow bumping here. Please do not do it again.
    Last edited by si_the_geek; Jun 17th, 2008 at 03:32 AM. Reason: added definition of a bump, as several people link to this post

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Nov 2007
    Posts
    84

    Re: Problem with OleDbCommandBuilder SQL it creates for updates

    Putting brackets in the SELECT statement did not have any effect on the statemetn built by the CommandBuilder?????

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Problem with OleDbCommandBuilder SQL it creates for updates

    I suggest you go back to Delphi.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Nov 2007
    Posts
    84

    Re: Problem with OleDbCommandBuilder SQL it creates for updates

    I'd love to but my employers are too stupid to see that VB sucks and it's not a real programming language.

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