How to update table field with a keyword as name??

Question

Hi,

I have a table ("TableVisits") with some fields (columns), and the name of one of them is "Desc" (=Description).

When I try to update the table

oDataAdapter.Update(oDataSet, "TableVisits");

I get the following exception:

"Syntax error in UPDATE statement."

It seems the Adapter refuses to work with table fields which have a language keyword as name.

However, I cannot change the database tables' field names.

So I was wondering if there is a way to get around this annoying problem.

Any help would be very much appreciated.

Thanks in advance.

Victor

Victor · Answer

Hi Vijaya, If a change the name of the column then the problem is solved, but in the to-be-converted database there are more than 200 tables and that would make the conversion process a lot more complex (forms, reports, etc). Besides, I thought I'd rather tackle the possible obstacles directly in the Business Object Mgr in one time. I'm still curious how your SQL-Statement string function (or whatever you use yourself). I mean how you get what you need (properties, etc) from the DataTable and do some kind of looping through the columns (I suppose). Would you mind sharing a bit to have an idea? Thanks.

Vijaya Kadiyala · Answer

can you chnage the column name from Desc to Description?? Thanks -- Vijaya Kadiyala www.DotNetVJ.com

Victor · Answer

Yes, I get an error when executing the oDataAdapter.Update(). "Syntax error in UPDATE statement." I know the problem lies at the naming of the fields ("Desc" in this case), because when I exclude the field from the selection/update everything goes fine. I read somewhere that enclosing the field name with [ ] in the statement should solve this, however, when I try to do that in the debugger (thus, from "set Desc = 'My description' " to "set [Desc] = 'My description' "), but the expression returns back to the original as soon as the Adapter.Update executes. I agree with you in that constructing the Sql statement is the best way when it comes to solving possible issues as this one, but since I am trying to convert more than 300 forms (from FoxPro) I didn't consider coding all expression one by one manually as an option. I suppose you also meant something like that...creating a standard/central function to put the SQL-statement string together based on a (parameter) DataTable. I was only curious as to how exactly you would layout the function to accomplish it all in the most efficient way. Thus, if the parameter is a DataTable, how to properly extract the info I need (looping through fields, getting field-types and field-values and putting the string together, as related to value-types). My preference goes to accessing and changing the statement on the CommandBuilder object, but if that can't be done, then I would appreciate to hear from you (or anyone) a 'proper' way to code such a function to build the SQL-statement string based on a DataTable. I probably ask stupid questions or seem to get the wrong approach on getting things done, but I am a beginner on C# (and the whole .NET), so I am just trying to get my feet on the 'right' track, with some assistance of the most experinece programmers, as yourself. Thanks again. Victor

Vijaya Kadiyala · Answer

What i mean when say construct UPDATE/DELETE statements: Construct the below statement: update books set title = 'Update Records' where author = 'xyz'; This way if you run into any issues you can easily debug. From my point of view i would use built-in methods when there is complex logic involved otherwise i always the code. Every one has their own opinion on this. Check out the below link http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=update Now if you use built-in code its hard figure out where the problem is. Are you getting any error with oDataAdapter.Update?? Thanks -- Vijaya Kadiyala www.DotNetVJ.com

Victor · Answer

Hi Vijaya, The table (TableVisits) has the following fields: - TableVisitID (autoincremental Key) - PersonID (numeric 5) - Type (numeric 5) - Desc (character 50) You say it's better NOT to use the built-in methods of .NET to update? I thought it was rather nice to do it with the existing methods, like oDataAdapter.Update(oDataSet, "TableVisits") and let the CommandBuilder take care of putting together the necessary statement for either Delete, Add or Update. The only problem I have is that the Update Command does not work when the name of the field is a keyword (like Desc). I read elsewhere that in such cases we need to put the field like this [Desc], however I don't know how I can change the statement built by the CommandBuilder. I tried it in the Debugger right before the oDataAdapter.Update (for example changing Desc = 'Test' to [Desc] = 'Test') but I find that the statement changes back to its original format when the Update is fired. So you say it's best practise to always construct UPDATE/DELETE statement myself? I suppose you mean not using the CommandBuilder and putting the statement-string together myself, right? But how exactly would I go by doing that? Do you mean I would need to cycle through the columns in the current row and check which need to be updated? But how about the values? And that probably means I would need to check the field type to format/convert the values, or not? And...could I still use the oDataAdapter.Update method? or would that require a completely different approach? I know there is an ocean of information out there, but that's actually how I got to the point where I am right now. Would you mind giving me a little sample of how you would go about with such matters, like adding/deleting/updating rows (with OleDb for Access database)? Thanks a lot in advance.

Vijaya Kadiyala · Answer

Hi Can you post the table structure. best practise, wlays construct UPDATE/DELETE statement when you are doing any operations on database instead of inbuilt methods of .Net to update. Thanks -- Vijaya Kadiyala www.DotNetVJ.com