Results 1 to 3 of 3

Thread: OleDbCommandBuilder and columns w/ spaces

  1. #1
    JuggaloBrotha's Avatar
    JuggaloBrotha is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Jun 2004
    Location
    Lansing, MI; USA
    Posts
    4,364
    Reputation
    978

    OleDbCommandBuilder and columns w/ spaces

    My company has a sub for updating an access database table with a dataset. We pass it a SQL Select statement which then in the sub updates the DataAdapter using OleDb.OleDbCommandBuilder(TheDataAdapter)

    this has been working fine for 2 years now, until now, one of the tables that needs to be updated in the database has 2 columns that have a space in the name. Getting the data is fine, we simply put brackets "[]" around the fields (columns) that have a space and it pulls all the data just fine, but the OleDb.OleDbCommandBuilder removes the bracket's when it generates the Delete and Insert statements which causes and error when the dataadapter's Update() method gets called.

    Is there a way around this?

    here's the class info: http://msdn2.microsoft.com/en-us/lib...r_members.aspx
    Currently using: VS 2010 Ultimate on Win7 Ultimate x64.


  2. #2
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1723
    Configure the data adapter yourself? Create an overload for this method that accepts a dataadapter already.

    All the command builder does when you pass it a select statement is:

    Deduce the table name
    Get the schema for the table
    Get the PK for the table

    create statements of the form:

    INSERT INTO {tablename}({columnnames}) VALUES({parameternames})
    UPDATE {tablename} SET {columnname1} = {parametername1} .. WHERE {PK column1} = {originalparametername1} ...
    DELETE FROM {tablename}({columnnames}) WHERE {PK column1} = {originalparametername1} ...


    If you require assistance with the SQLs, let me know.

    Alternately, there is nothing stopping you upgrading the code to accept a list of replacements and performing them on the UpdateCommand.CommandText etc... e.g. get the CommandBuilder to do most of the work for you, but then just tweak what it has written.

    The other 2 options I would recommend as a better solution are:

    Write a proper version of theis Data Access Layer, imitating .NET 2.0 (or get someone with .net 2 to create the code using the visual designer ,then rip it out and put it into your .net 1.1 project)

    Do not use spaces in column names - it has always been a bad idea

  3. #3
    JuggaloBrotha's Avatar
    JuggaloBrotha is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Jun 2004
    Location
    Lansing, MI; USA
    Posts
    4,364
    Reputation
    978
    i'll have a talk with my co-workers about this, thanx for the advice
    Currently using: VS 2010 Ultimate on Win7 Ultimate x64.


Bookmarks

Posting Permissions

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