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

Answer this question

OleDbCommandBuilder creats bad Update Command

smith_a

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();

Zoran Stipanicev
Hi,

Could you reproduce this If so, report it in Product Feedback.

cheers,

Paul June A. Domag
OleDbCommandBuilder creats bad Update Command

Answer this question