?
Solved

Using the command builder

Posted on 2004-12-01
8
Medium Priority
?
514 Views
Last Modified: 2010-04-24
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.
0
Comment
Question by:Misledman
  • 5
  • 3
8 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 750 total points
ID: 12720741
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
 
LVL 1

Author Comment

by:Misledman
ID: 12720849
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
 
LVL 1

Author Comment

by:Misledman
ID: 12720891
This is the update command CommandText:
"UPDATE WebInfo SET Password = ? WHERE ( (ID = ?) AND ((? = 1 AND Password IS NULL) OR (Password = ?)) )"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Misledman
ID: 12721482
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12721527
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12721536
The OleDbCommandBuilder is the same.

Bob
0
 
LVL 1

Author Comment

by:Misledman
ID: 12721557
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
 
LVL 1

Author Comment

by:Misledman
ID: 12722365
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question