Update MDB where there's space in header

[Eran.Yasso]

Hi,

My app updates mdb file with no probelm. the problem starts if there's
a space on a column name(header consists from two words). I tried all
sorts of things. here is my code:


private void button1_Click(object sender, System.EventArgs e)
{
string ColumnHeader = "DTV_DOWN MAC",updatestr="myupdate";
System.Data.OleDb.OleDbConnection con = null;
System.Data.OleDb.OleDbCommand command = null;
string filename = @"C:\test.mdb";
DataSet ds = new DataSet();
try
{
con = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=" + filename);// + ";Extended
Properties=\"text;HDR=Yes;IMEX=1;FMT=Delimited\"");
string sql = "UPDATE DataTable SET '" + ColumnHeader + "' =
'"+updatestr+"' WHERE TBL_ID = 1980";
con.Open();
command = new OleDbCommand(sql,con);
int t = command.ExecuteNonQuery();


}


catch(Exception err)
{
MessageBox.Show(err.Message.ToString());


}
con.Close();
}


the exception occurs in line int "t = command.ExecuteNonQuery();".
i get error message "No value given for one or more requiered
parameter".
If i remove the space and use "_" in the mdb header and in
ColumnHeader,
it works. Can't I update MDB with space in header?

Thanks for the help.

 

[John Vinson]

My app updates mdb file with no probelm. the problem starts if there's
a space on a column name(header consists from two words). I tried all
sorts of things. here is my code:

Did you try enclosing the (unwisely containing blanks) fieldname in
[square brackets], e.g.

string sql = "UPDATE DataTable SET [" + ColumnHeader + "] =

rather than singlequotes? I believe singlequotes work as fieldname
delimiters in the ORACLE dialect of SQL, but the Access dialect uses
brackets.

John W. Vinson[MVP]

 

[Larry Linson]

Names with spaces, in Access, must be enclosed in square brackets, e.g.,
[The Name]. Many of us simplify our work with Access databases by not using
spaces, but using capitalization to make the names readable, e.g., TheName,
or by separating the parts with underscore rather than space, e.g. The_Name.

However, it appears you are coding not in Access, but in C, or some variant,
and I don't know what you should do in that language -- using a Jet database
engine table and the ActiveX Data Objects code to access it. Perhaps you'd
be better to determine the Microsoft-sponsored newsgroups appropriate to the
variant of C that you are using and databases, and ask the question there.

Larry Linson
Microsoft Access MVP