asked on

Using the command builder

When using the command builder, if you have a column in the database that is some kind of key word (I gues that is it), DataAdapter.Update(DS,"Table") fails. For example, I have a field called "password". The update fails with this column name. If I change it to "PasswordWeb", it works without error. I know people are having this issue using the command builder. Is there a way around it? It's a lot of trouble to go back and change field names.

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Misledman

ASKER

This is the error.
System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.

I tried the select statement change. Here it is:
sql = "Select WebInfo.ID, WebInfo.[Password] from WebInfo where Employee = " & EmployeeNumber

This has happened to me before. I just changed the column name. Too much work here...

Misledman

ASKER

This is the update command CommandText:
"UPDATE WebInfo SET Password = ? WHERE ( (ID = ?) AND ((? = 1 AND Password IS NULL) OR (Password = ?)) )"

Misledman

ASKER

You got me in the right direction. I'll award the points. I think you are wrong about getting the command text from the select statement. I bracketed the Password field and it didn't propogate to the command builder command text. However, I manually re-wrote the command builder's command text like this:
sql = "UPDATE WebInfo SET WebInfo.Password = ? WHERE ( (ID = ?) AND ((? = 1 AND WebInfo.Password IS NULL) OR (WebInfo.Password = ?)) )"
da.UpdateCommand.CommandText = sql

It works like that. The only issue is first getting the command text from a watch. Then you can copy and paste and edit. There are LOTS of words that one can use that the command builder doesn't like. Server is one.... I'm drawing blanks on others but I have run across it several times with words you would NOT think would be an issue..... Anyways, hopefully this will help someone in the future... BTW, I am using an Access database....

Bob Learned

SqlCommandBuilder Class
http://authors.aspalliance.com/aspxtreme/sys/data/sqlclient/SqlCommandBuilderClass.aspx

Excerpt: To generate INSERT, UPDATE, or DELETE statements, the SqlCommandBuilder uses the SelectCommand property to retrieve a required set of metadata automatically.

Bob

Bob Learned

The OleDbCommandBuilder is the same.

Bob

Misledman

ASKER

You know, that may be correct. I only tried bracketing the column name ("[Password]"). Had I put the table name in ("WebInfo.Password") it may have propogated to the command builder. I'll try that later.....

Misledman

ASKER

FYI...... Th insert command doesn't work either. No surprise. The surprise is that "Webinfo.Password" does NOT work like it did with the update command. "[Password]" DOES work. I'm going to Redmond and a "word" with Bill gates. GEZZZZZZZZZ