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.
LVL 1
MisledmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
You might try using delimiters around the field name in the SelectCommand, such as `password` or [password].  The SelectCommand is used to derive the UpdateCommand.

Bob
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MisledmanAuthor Commented:
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...
0
MisledmanAuthor Commented:
This is the update command CommandText:
"UPDATE WebInfo SET Password = ? WHERE ( (ID = ?) AND ((? = 1 AND Password IS NULL) OR (Password = ?)) )"
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

MisledmanAuthor Commented:
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....
0
Bob LearnedCommented:
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
0
Bob LearnedCommented:
The OleDbCommandBuilder is the same.

Bob
0
MisledmanAuthor Commented:
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.....
0
MisledmanAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.