locked
Field Names with spaces RRS feed

  • Question

  • I am binding to an old Access 95 database, some of the field names have spaces:

    Using this line causes an error:

     

    this.txtThickness.DataBindings.Add(new Binding("Text", bsInventory,"SIZE THICKNESS"));

     

    As I cannot change the field name - can anyone suggest how to get around the error.

     

    Thanks in advance.

     

     

    Wednesday, November 7, 2007 11:26 PM

Answers

  • Hi, Can-Ann,

     

    I have to say that it seems an issue with CommandBuilder.

    I tried to make a sample with OleDbCommandBuilder, and it gave me "Syntax error" message as in your project.

     

    Then I tried to change the UpdateCommand manually, and it works well again.

    For example, add a TextBox, a Button and a DataGridView onto your form.

    Code Block

            OleDbConnection conn;

            OleDbDataAdapter adapter;

            DataTable datatable;

            private void Form1_Load(object sender, EventArgs e)

            {

                conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\new.mdb;User Id=admin;Password=;");

                adapter = new OleDbDataAdapter("select * from table1",conn);

                adapter.UpdateCommand = new OleDbCommand();

                adapter.UpdateCommand.Connection = conn;

                adapter.UpdateCommand.CommandText = "UPDATE `Table1` SET `Name` = ?, `Money` = ?, `SIZE THICKNESS` = ? WHERE `ID` = ?";

                adapter.UpdateCommand.CommandType = CommandType.Text;

                adapter.UpdateCommand.Parameters.Add(new OleDbParameter("Name", OleDbType.VarWChar, 0, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Name", DataRowVersion.Current, false, null));

                adapter.UpdateCommand.Parameters.Add(new OleDbParameter("Money", OleDbType.Currency, 0, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Money", DataRowVersion.Current, false, null));

                adapter.UpdateCommand.Parameters.Add(new OleDbParameter("SIZE THICKNESS", OleDbType.VarWChar, 0, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "SIZE THICKNESS", DataRowVersion.Current, false, null));

                adapter.UpdateCommand.Parameters.Add(new OleDbParameter("Original_ID", OleDbType.Integer, 0, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ID", DataRowVersion.Original, false, null));

     

                datatable = new DataTable();

                adapter.Fill(datatable);

                textBox1.DataBindings.Add("Text", datatable, "SIZE THICKNESS",true, DataSourceUpdateMode.OnPropertyChanged);

                dataGridView1.DataSource = datatable;

     

               

            }

     

            private void button1_Click(object sender, EventArgs e)

            {

                try

                {            

                    dataGridView1.DataSource = null;

                    dataGridView1.DataSource = datatable;

                    adapter.Update(datatable);

                }

                catch (Exception ex)

                {

     

                    MessageBox.Show(ex.ToString());

                }

            }

     

     

    To feedback this CommandBuilder issue in a formal way, could you go to our Connect portal site and submit it?
    http://connect.microsoft.com/
    In your feedback, you can  quote the URL to this thread, so that the whole conversation can be available. Every feedback submitted will be evaluated carefully by our engineers and document writers. They will let you know their comments further through that portal. It would be great if you can also paste the link to the submitted feedback here, so that other community members can see it as well.

     

    Hope this helps,

    Regards

    Wednesday, November 14, 2007 1:58 AM

All replies

  • What error are you getting?

    Thursday, November 8, 2007 12:17 PM
  • The error occurs on this line:

    this.daInventory.Update(this.dtInventory);

     

    and is :

     

     

    Syntax error (missing operator) in query expression '(((? = 1 AND ActivityCode IS NULL) OR (ActivityCode = ?)) AND ((? = 1 AND Type IS NULL) OR (Type = ?)) AND (Number = ?) AND ((? = 1 AND Category IS NULL) OR (Category = ?)) AND ((? = 1 AND Description IS NULL) OR (Description = ?)) AND ((? = 1 AND Grade I'.

     

    When I remove the space it works perfect.

     

     

     

    Thursday, November 8, 2007 12:54 PM
  • Have you tried enclosing the fieldname in []

    i.e [field name]?

     

    Thursday, November 8, 2007 3:48 PM
  • Yes -

    "[SIZE THICKNESS]"

     doess not work  give the following

     

     

    Cannot bind to the property or column [SIZE THICKNESS] on the DataSource.
    Parameter name: dataMember

     

     

    also tried

     

    ["SIZE THICKNESS"]

     

     

    Thursday, November 8, 2007 7:24 PM
  • Hi, Can-Ann,

     

    Based on my understanding, you cannot update the data in "SIZE THICKNESS" column, can you?

     

    I think you should try to put the field name in single quotation marks.

    For example

    Code Block

    UPDATE `Table1` SET `Name` = ?, `Money` = ?, `SIZE THICKNESS` = ? ......

     

     

    It works fine in my computer.

     

    Hope this helps,

    Regards

    Tuesday, November 13, 2007 10:02 AM
  • Y Guo - thanks for your reply, my original problem is with the generated commmand causing the error, the field is databound:

     

    this.txtThickness.DataBindings.Add(new Binding("Text", bsInventory,"SIZE THICKNESS"));

     

    The error occurs on this line:

    this.daInventory.Update(this.dtInventory)

     

    I have not yet gotten around this yet.

     

     

     

    Tuesday, November 13, 2007 11:11 AM
  • Instead of using a query like Select * from Table1 for you data adapter's select command list the all the fields and surround the fields with [ ].  This usually fixes any problems with the automatically generated update, insert and delete commands.

    Tuesday, November 13, 2007 11:35 AM
  • Thanks for your reply Ken, however I am using the command builder:

     

    this.txtThickness.DataBindings.Add(new Binding("Text", bsInventory,"SIZE THICKNESS"));

     

    The error occurs on this line:

    this.daInventory.Update(this.dtInventory)

     

    and this is what causes the error:

     

     

     

    Tuesday, November 13, 2007 12:51 PM
  • Hi, Can-Ann,

     

    I have to say that it seems an issue with CommandBuilder.

    I tried to make a sample with OleDbCommandBuilder, and it gave me "Syntax error" message as in your project.

     

    Then I tried to change the UpdateCommand manually, and it works well again.

    For example, add a TextBox, a Button and a DataGridView onto your form.

    Code Block

            OleDbConnection conn;

            OleDbDataAdapter adapter;

            DataTable datatable;

            private void Form1_Load(object sender, EventArgs e)

            {

                conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\new.mdb;User Id=admin;Password=;");

                adapter = new OleDbDataAdapter("select * from table1",conn);

                adapter.UpdateCommand = new OleDbCommand();

                adapter.UpdateCommand.Connection = conn;

                adapter.UpdateCommand.CommandText = "UPDATE `Table1` SET `Name` = ?, `Money` = ?, `SIZE THICKNESS` = ? WHERE `ID` = ?";

                adapter.UpdateCommand.CommandType = CommandType.Text;

                adapter.UpdateCommand.Parameters.Add(new OleDbParameter("Name", OleDbType.VarWChar, 0, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Name", DataRowVersion.Current, false, null));

                adapter.UpdateCommand.Parameters.Add(new OleDbParameter("Money", OleDbType.Currency, 0, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Money", DataRowVersion.Current, false, null));

                adapter.UpdateCommand.Parameters.Add(new OleDbParameter("SIZE THICKNESS", OleDbType.VarWChar, 0, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "SIZE THICKNESS", DataRowVersion.Current, false, null));

                adapter.UpdateCommand.Parameters.Add(new OleDbParameter("Original_ID", OleDbType.Integer, 0, ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ID", DataRowVersion.Original, false, null));

     

                datatable = new DataTable();

                adapter.Fill(datatable);

                textBox1.DataBindings.Add("Text", datatable, "SIZE THICKNESS",true, DataSourceUpdateMode.OnPropertyChanged);

                dataGridView1.DataSource = datatable;

     

               

            }

     

            private void button1_Click(object sender, EventArgs e)

            {

                try

                {            

                    dataGridView1.DataSource = null;

                    dataGridView1.DataSource = datatable;

                    adapter.Update(datatable);

                }

                catch (Exception ex)

                {

     

                    MessageBox.Show(ex.ToString());

                }

            }

     

     

    To feedback this CommandBuilder issue in a formal way, could you go to our Connect portal site and submit it?
    http://connect.microsoft.com/
    In your feedback, you can  quote the URL to this thread, so that the whole conversation can be available. Every feedback submitted will be evaluated carefully by our engineers and document writers. They will let you know their comments further through that portal. It would be great if you can also paste the link to the submitted feedback here, so that other community members can see it as well.

     

    Hope this helps,

    Regards

    Wednesday, November 14, 2007 1:58 AM
  • Yu Guo - thanks I have submitted the (?) to MS and will post their feed back here .

     

     

     

     

    Wednesday, November 14, 2007 12:03 PM