OleDbCommandBuilder creats bad Update Command
I am creating a database system using .NET and an access database. I am using OleDbCommandBuilder to create my SQL commands. However, i get thrown an exception when I try updating a certain field. It says that there is an error in SQL syntax. I extracted and command, and sure enough it is generated wrong. Here's what i got
UPDATE Tests SET TestName = ?, Procedure = ?, Developer = ?, System = ?, Type = ? WHERE ((TestID = ?) AND ((? = 1 AND TestName IS NULL) OR (TestName = ?)) AND ((? = 1 AND Developer IS NULL) OR (Developer = ?)) AND ((? = 1 AND System IS NULL) OR (System = ?)) AND ((? = 1 AND Type IS NULL) OR (Type = ?)))
As you can see, there is nothing for Procedure in the WHERE portion of the command. I checked the DB setup, and it seems that the database is configured just fine. The select command I feed it is "SELECT * FROM Tests", which should be perfect. What could cause OleDbCommandBuilder to create errorenous commands?
As Paul said, creating a reproducible sample and submitting it via the Product Feedback tool is the way to go.
I created a small sample table in a Jet database with a column named "Procedure" and then used an OleDbCommandBuilder to generate updating logic. The code appears at the end of this post. The logic was sound and included a concurrency check for the "Procedure" column. Based on that information, it looks like there may be somethind specific to your table.
If you could provide a CREATE TABLE query that would allow others to reproduce the problem, you'll increase the chances of someone providing an answer and/or solution.
David Sceppa
Microsoft
| |
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Data\NWind.mdb;";
OleDbConnection cn = new OleDbConnection(strConn);
cn.Open(); OleDbCommand cmd = cn.CreateCommand();
cmd.CommandText = "DROP TABLE BadCommandBuilderLogic";
try { cmd.ExecuteNonQuery(); } catch {} cmd.CommandText = "CREATE TABLE BadCommandBuilderLogic (ID int PRIMARY KEY, [Procedure] varchar(255), OtherCol varchar(255))";
cmd.ExecuteNonQuery(); OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM BadCommandBuilderLogic", cn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
Console.WriteLine(cb.GetUpdateCommand().CommandText); cn.Close();
|
